Compute Daily Revenue by Creation Source
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Quick Answer: 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.