Coding questions · Meta
Analyze spend cohort and source shifts
This question evaluates data manipulation and analytical SQL/Python skills, focusing on cohort analysis, time-series aggregation, joins and filtering, and comparative spend analytics across calendar years.
You work on an ads platform. Assume all timestamps are in UTC. Interpret last year as calendar year 2023 and this year as calendar year 2024.
Tables:
advertisers(advertiser_id BIGINT, advertiser_type VARCHAR, country VARCHAR, created_at TIMESTAMP)ads(ad_id BIGINT, advertiser_id BIGINT, creation_source VARCHAR)wherecreation_sourcecan take values such asMANUAL,AI_ASSISTED,IMAGE_TEMPLATE, andVIDEO_TEMPLATEad_spend_daily(spend_date DATE, ad_id BIGINT, spend_usd DECIMAL(18,2))
Key relationships:
- One advertiser can have many ads.
- One ad can have many daily spend records.
Questions:
- Identify advertisers whose total spend in 2023 exceeded 1,000 USD. Among all platform spend in 2024, what percentage of 2024 spend came from this advertiser cohort? Return the following columns:
eligible_advertisers,cohort_spend_2024,total_platform_spend_2024, andcohort_share_2024. - PMs noticed that spend from one ad creation source is growing and want to know whether that growth may be driven by a decline in another source. Write a query that returns monthly 2024 spend by
creation_source, while excluding advertisers whoseadvertiser_typeis in('INTERNAL', 'POLITICAL', 'TEST'). Also include, for each month, the amount of spend from advertisers whoseAI_ASSISTEDspend increased versus the same month in 2023 while theirMANUALspend decreased, so the team can assess possible cannibalization between creation sources.
Analyze spend cohort and source shifts
Table advertisers
| column | type | constraints |
|---|---|---|
| advertiser_id | BIGINT | PRIMARY KEY, NOT NULL |
| advertiser_type | VARCHAR | NOT NULL |
| country | VARCHAR | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
Sample data:
| advertiser_id | advertiser_type | country | created_at |
|---|---|---|---|
| 1 | BRAND | US | 2022-05-15 10:00:00 |
| 2 | AGENCY | UK | 2023-01-03 12:00:00 |
| 3 | BRAND | CA | 2021-07-20 09:00:00 |
| 4 | TEST | US | 2022-11-11 15:30:00 |
Table ads
| column | type | constraints |
|---|---|---|
| ad_id | BIGINT | PRIMARY KEY, NOT NULL |
| advertiser_id | BIGINT | NOT NULL, FOREIGN KEY REFERENCES advertisers(advertiser_id) |
| creation_source | VARCHAR | NOT NULL |
Sample data:
| ad_id | advertiser_id | creation_source |
|---|---|---|
| 101 | 1 | MANUAL |
| 102 | 1 | AI_ASSISTED |
| 201 | 2 | MANUAL |
| 301 | 3 | MANUAL |
| 302 | 3 | AI_ASSISTED |
| 401 | 4 | AI_ASSISTED |
Table ad_spend_daily
| column | type | constraints |
|---|---|---|
| spend_date | DATE | NOT NULL |
| ad_id | BIGINT | NOT NULL, FOREIGN KEY REFERENCES ads(ad_id) |
| spend_usd | DECIMAL(18,2) | NOT NULL |
Sample data:
| ad_id | spend_date | spend_usd |
|---|---|---|
| 101 | 2023-03-15 | 1000 |
| 102 | 2023-06-20 | 500 |
| 201 | 2023-02-10 | 900 |
| 301 | 2023-04-05 | 1200 |
| 302 | 2023-10-12 | 800 |
| 401 | 2023-08-01 | 300 |
| 101 | 2024-01-10 | 1200 |
| 102 | 2024-02-05 | 800 |
| 201 | 2024-03-08 | 1000 |
| 301 | 2024-01-20 | 1800 |
| 302 | 2024-04-22 | 1200 |
| 401 | 2024-02-14 | 400 |
Expected output
One-row summary for Q1 showing the number of eligible advertisers (2023 spend > 1,000), their total 2024 spend, the platform's total 2024 spend, and the share of 2024 spend from this cohort. The result_set column labels this output as Q1.
| cohort_share_2024 | cohort_spend_2024 | eligible_advertisers | result_set | total_platform_spend_2024 |
|---|---|---|---|---|
| 0.78125 | 5000 | 2 | Q1 | 6400 |
Hints
- Treat last year as 2023 and this year as 2024: use spend_date >= '2023-01-01' and < '2024-01-01' for 2023, and spend_date >= '2024-01-01' and < '2025-01-01' for 2024.
- Build a 2023 advertiser spend CTE, filter to > 1000 to define the cohort, then compute 2024 spend for the cohort and for the whole platform; divide for the share.
- For Q2: Aggregate monthly spend in 2024 by creation_source after joining ad_spend_daily -> ads -> advertisers and excluding advertiser_type in ('INTERNAL','POLITICAL','TEST').
- For the cannibalization flag in Q2: compute advertiser-month spend for 2023 and 2024 by creation_source, pivot AI_ASSISTED and MANUAL, then flag advertisers where AI_2024 > AI_2023 and MANUAL_2024 < MANUAL_2023; sum their 2024 spend for the month into a separate column.