Task
You are given Lyft Ads data for advertisers, campaigns, and daily performance. Write a PostgreSQL query that analyzes advertiser performance by monthly signup cohort and month since cohort start. For each cohort month and cohort_age_month, return the number of advertisers in the cohort, the number of active advertisers in that period, total spend, total attributed rides, average spend per active advertiser, and the prior-period spend change using a window function. Treat an advertiser as active in a month if total spend in that month is greater than 0. Include advertisers with no campaign activity after signup so cohort sizes are not understated.
Schema
advertisers
| column | type | description |
|---|
| advertiser_id | INT | Unique advertiser ID |
| advertiser_name | VARCHAR(100) | Advertiser name |
| signup_date | DATE | Date the advertiser first joined Lyft Ads |
| vertical | VARCHAR(50) | Advertiser industry vertical |
campaigns
| column | type | description |
|---|
| campaign_id | INT | Unique campaign ID |
| advertiser_id | INT | Advertiser that owns the campaign |
| campaign_name | VARCHAR(100) | Campaign name |
| launch_surface | VARCHAR(50) | Lyft surface where the ad ran |
daily_campaign_performance
| column | type | description |
|---|
| performance_id | INT | Unique performance row ID |
| campaign_id | INT | Campaign ID |
| activity_date | DATE | Performance date |
| spend_usd | NUMERIC(10,2) | Daily ad spend |
| attributed_rides | INT | Rides attributed to the campaign |
Sample data
advertisers
| advertiser_id | advertiser_name | signup_date | vertical |
|---|
| 1 | Urban Eats | 2024-01-05 | Restaurants |
| 2 | City Style | 2024-01-20 | Retail |
| 3 | Night Owl | 2024-02-02 | Entertainment |
daily_campaign_performance
| performance_id | campaign_id | activity_date | spend_usd | attributed_rides |
|---|
| 1001 | 101 | 2024-01-10 | 120.00 | 14 |
| 1002 | 101 | 2024-02-03 | 80.00 | 9 |
| 1003 | 102 | 2024-02-14 | 60.00 | 7 |
Expected output
| cohort_month | cohort_age_month | cohort_size | active_advertisers | total_spend_usd | total_attributed_rides | avg_spend_per_active_advertiser | prior_period_spend_change |
|---|
| 2024-01 | 0 | 2 | 1 | 120.00 | 14 | 120.00 | null |
| 2024-01 | 1 | 2 | 2 | 140.00 | 16 | 70.00 | 20.00 |
