Compute active ad revenue by creation source

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.

Compute active ad revenue by campaign creation source

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.

Table ad_campaigns

column	type	constraints
campaign_id	INT	PRIMARY KEY, NOT NULL
advertiser_id	INT	NOT NULL
creation_source	VARCHAR(20)	NOT NULL
created_at	DATE	NOT NULL
status	VARCHAR(10)	NOT NULL

Sample data:

advertiser_id	campaign_id	created_at	creation_source	status
1	101	2025-03-10	SELF_SERVE	ACTIVE
2	102	2025-04-02	MANAGED	ACTIVE
3	103	2025-02-15	API	PAUSED
4	104	2025-05-01	SELF_SERVE	ACTIVE
5	105	2025-01-05	MANAGED	INACTIVE

Table ad_daily_stats

column	type	constraints
campaign_id	INT	NOT NULL
stat_date	DATE	NOT NULL
impressions	INT	NOT NULL
clicks	INT	NOT NULL
spend	DECIMAL(10,2)	NOT NULL

Sample data:

campaign_id	clicks	impressions	spend	stat_date
101	120	10000	50	2025-04-28
101	300	25000	120.5	2025-05-03
101	210	18000	80	2025-05-20
102	500	40000	200	2025-05-10
102	410	35000	150	2025-05-29
103	600	50000	300	2025-05-12
104	130	12000	60	2025-05-01
104	160	14000	70	2025-06-01
105	900	80000	500	2025-05-15

Expected output

One row per creation_source, summing spend in May 2025 for campaigns whose status is ACTIVE. Columns: creation_source, active_ad_revenue.

active_ad_revenue	creation_source
350	MANAGED
260.5	SELF_SERVE

Hints

1. Filter stats to the May 2025 date range using BETWEEN.
2. Join daily stats to campaigns, then filter to campaigns with status = 'ACTIVE' and group by creation_source.