Compute active ad revenue by creation source
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Onsite
You work on an ads platform and need to report **active ad revenue** broken down by the ad’s **creation source**.
## Tables
### `ads`
- `ad_id` BIGINT **PK**
- `advertiser_id` BIGINT
- `creation_source` VARCHAR
*Examples:* `'SELF_SERVE'`, `'MANAGED'`, `'API'`
- `created_at` TIMESTAMP
### `ad_impressions`
- `impression_id` BIGINT **PK**
- `ad_id` BIGINT **FK → ads.ad_id**
- `impression_time` TIMESTAMP
### `ad_revenue_events`
- `event_id` BIGINT **PK**
- `ad_id` BIGINT **FK → ads.ad_id**
- `event_time` TIMESTAMP
- `revenue_usd` DECIMAL(18,6)
*(Assume revenue is recorded at the time it is earned, e.g., per impression/click.)*
## Definitions / assumptions
- Timezone: **UTC**.
- A paid ad is **active on a day** if it has **≥ 1 impression** that day.
- **Active ad revenue on a day** = sum of `revenue_usd` from `ad_revenue_events` that occurred that day **for ads that are active that same day**.
## Task
Write a SQL query to compute, for each calendar day in a given date range (e.g., `:start_date` to `:end_date`, inclusive):
- `report_date`
- `creation_source`
- `active_ads` (count of distinct active `ad_id`)
- `active_ad_revenue_usd` (sum of revenue for active ads)
Order results by `report_date`, then `creation_source`.
Overview: This question evaluates proficiency in data manipulation and analytics, testing skills such as joining event and impression tables, aggregating time-series revenue, and counting distinct active ads broken down by creation source.
Read the full Meta Data Scientist interview experience this question came from
You are given ad campaign metadata and daily performance stats.
Define **active ad revenue** as the total `spend` generated between **2025-05-01** and **2025-05-31** (inclusive) by campaigns whose current `status` is `'ACTIVE'`.
Write a SQL query to return, for May 2025:
- `creation_source`
- total active ad revenue (`active_ad_revenue`)
Group by `creation_source` and order results by `active_ad_revenue` descending.
Tables
ad_campaigns(campaign_id INT, advertiser_id INT, creation_source VARCHAR(20), created_at DATE, status VARCHAR(10))
ad_daily_stats(campaign_id INT, stat_date DATE, impressions INT, clicks INT, spend DECIMAL(10,2))
Hints
- Filter stats to the May 2025 date range using BETWEEN.
- Join daily stats to campaigns, then filter to campaigns with status = 'ACTIVE' and group by creation_source.