Compute Daily Revenue by Creation Source
Overview
This question evaluates proficiency in data manipulation and analytical SQL/Python skills, including aggregation, date truncation, joins, window functions, and period-over-period comparisons for revenue metrics.
active_ads
date | ad_id | advertiser_id | creation_source | revenue
2023-09-01 | 1001 | 17 | mobile_app | 150.00
2023-09-01 | 1002 | 18 | api | 75.00
2023-09-02 | 1003 | 17 | web | 200.00
advertiser_info
advertiser_id | advertiser_name | advertiser_country
17 | Alpha Corp | US
18 | Beta Ltd | UK
19 | Gamma GmbH | DE
Scenario
Advertising revenue reporting for a multi-source ads platform using relational data in tables active_ads and advertiser_info.
Question
Q1. Write a query to compute daily total revenue grouped by creation_source. Q2. Write a query to list the countries of the ten advertisers with the fewest active ads (break ties arbitrarily). Q3. For each creation_source, compute the proportion of advertisers whose spend in the current year exceeds their spend in the previous year by at least 1,000 USD.
Hints
Use date truncation, aggregation, LEFT JOINs, window functions or CTEs for yearly spend, then COUNTIF / COUNT ratio.