Sample dataset: https://gist.github.com/kotik/3baa5f53997cce85cc0336cb1256ba8b/
Expose the sample dataset through a single generic HTTP API endpoint, which is capable of filtering, grouping and sorting.
Dataset represents performance metrics (impressions, clicks, installs, spend, revenue) for a given date, advertising channel, country and operating system.
Dataset is expected to be stored and processed in a relational database.
Client of this API should be able to:
- filter by time range (date_from+date_to is enough), channels, countries, operating systems
- group by one or more columns: date, channel, country, operating system
- sort by any column in ascending or descending order
- see derived metric CPI (cost per install) which is calculated as cpi = spend / installs
Common API use-cases:
- Show the number of impressions and clicks that occurred before the 1st of June 2017, broken down by channel and country, sorted by clicks in descending order. Hint:
=> select channel, country, sum(impressions) as impressions, sum(clicks) as clicks from sampledataset where date < '2017-06-01' group by channel, country order by clicks desc;
channel | country | impressions | clicks
------------------+---------+-------------+--------
adcolony | US | 532608 | 13089
apple_search_ads | US | 369993 | 11457
vungle | GB | 266470 | 9430
vungle | US | 266976 | 7937
...
- Show the number of installs that occurred in May of 2017 on iOS, broken down by date, sorted by date in ascending order.
- Show revenue, earned on June 1, 2017 in US, broken down by operating system and sorted by revenue in descending order.
- Show CPI and spend for Canada (CA) broken down by channel ordered by CPI in descending order. Please think carefully which is an appropriate aggregate function for CPI.
API endpoint is supposed to serve a dynamic dataset that corresponds to any combination of filters, breakdowns and sorting. Four use-cases are provided to give the general idea of its usage and capabilities. Please don't expect use-case number as an API parameter. Please don't create four API endpoints.
Make sure you have a single API endpoint that is compliant with all use-cases described above and explicitly specify localhost urls for each of the 4 cases in your Readme.
We expect it to be written in a way that it could go into production and be maintained by your teammates. Clean code is your CV.
We'd like to give all candidates taking this test the same opportunity to solve the exercise in their own way, because of this we kindly ask you not to share your solution with anybody
From reviewer's perspective, the easiest tech stack to review is FastAPI (and SQLAlchemy) or Django (and Django ORM), SQLite, no Docker.
Feel free to use third-party libraries that make development easier and fun.
Spend as much time as you need to come up with the solution you like and when ready share your private repository with https://github.com/automate-reviewer
There is small correction required in sql query result as mentioned above:
Case 1 : API case states that date before the 1st of June 2017 but above sql results is similar when sql query will execute with date <= '2017-06-01'. Kindly make the changes. However during implementation I have considered it.
select channel, country, sum(impressions) as impressions, sum(clicks) as clicks from sampledataset where date < '2017-06-01' group by channel, country order by clicks desc;
adcolony|US|498861|12277
apple_search_ads|US|347554|10738
vungle|GB|249197|8831
vungle|US|249618|7440
unityads|US|201584|6888