Analyze spend cohort and source shifts
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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)` where `creation_source` can take values such as `MANUAL`, `AI_ASSISTED`, `IMAGE_TEMPLATE`, and `VIDEO_TEMPLATE`
- `ad_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:
1. 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`, and `cohort_share_2024`.
2. 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 whose `advertiser_type` is in `('INTERNAL', 'POLITICAL', 'TEST')`. Also include, for each month, the amount of spend from advertisers whose `AI_ASSISTED` spend increased versus the same month in 2023 while their `MANUAL` spend decreased, so the team can assess possible cannibalization between creation sources.
Overview: 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.
Read the full Meta Data Scientist interview experience this question came from
1) Identify advertisers whose total spend in 2023 exceeded 1,000 USD and compute what percentage of all 2024 platform spend came from this cohort. 2) Return monthly 2024 spend by creation_source (excluding advertiser_type in ('INTERNAL','POLITICAL','TEST')) and, per month, include the amount of 2024 spend from advertisers whose AI_ASSISTED spend increased vs the same month in 2023 while their MANUAL spend decreased.
Tables
advertisers(advertiser_id BIGINT, advertiser_type VARCHAR, country VARCHAR, created_at TIMESTAMP)
ads(ad_id BIGINT, advertiser_id BIGINT, creation_source VARCHAR)
ad_spend_daily(spend_date DATE, ad_id BIGINT, spend_usd DECIMAL(18,2))
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.
Community answers
Answer by nekkoya
-- Write your SQL query here
--expected outcome: percentage of all platform spend came from this cohort--critiera: advertiser total spend in 2023 exceeded 1,000 USD--timeline: 2024
With Advertiser_2023 AS ( SELECT advertiser_id, SUM(spend_usd) AS Spend2023 FROM ads JOIN ad_spend_daily spend ON ads.ad_id = spend.ad_id WHERE spend.spend_date BETWEEN '2023/01/01' AND '2023/12/31' GROUP BY advertiser_id HAVING SUM(spend_usd) > 1000), Spend_2024 AS ( SELECT a2023.advertiser_id, SUM(spend_usd) AS Spend2024 FROM Advertiser_2023 a2023 JOIN ads ON ads.advertiser_id = a2023.advertiser_id LEFT JOIN ad_spend_daily spend ON spend.ad_id = ads.ad_id WHERE spend.spend_date BETWEEN '2024/01/01' AND '2024/12/31' GROUP BY a2023.advertiser_id)
SELECT ROUND(SUM(Spend2024)*1.00/AVG(Tspend),5) AS cohort_share_2024, ROUND(SUM(Spend2024),0) cohort_spend_2024, COUNT(advertiser_id) AS eligible_advertisers, 'Q1' AS result_set, ROUND(AVG(TSPEND),0) AS total_platform_spend_2024FROM Spend_2024CROSS JOIN (SELECT SUM(spend_usd) AS TSpend FROM ad_spend_daily WHERE spend_date BETWEEN '2024/01/01' AND '2024/12/31') K
Answer by nekkoya
Q2 With TSpend AS (SELECT EXTRACT(YEAR FROM spend_date) AS year, EXTRACT(MONTH FROM spend_date) as month, creation_source, SUM(spend_usd) AS TspendFROM advertisersLEFT JOIN ads ON advertisers.advertiser_id = ads.advertiser_id LEFT JOIN ad_spend_daily spend ON spend.ad_id = ads.ad_id WHERE advertiser_type NOT in ('INTERNAL','POLITICAL','TEST') and spend_date BETWEEN ('2023/01/01') AND '2024/12/31'GROUP BY EXTRACT(YEAR FROM spend_date), EXTRACT(MONTH FROM created_at),creation_source)
SELECT t1., t2., ABS(t2.tspend - t1.tspend) AS differnce FROM Tspend t1JOIN Tspend t2 ON T1.year = '2023' AND T2.year = '2024' AND T1.Month = T2.Month AND T1.creation_source = T2.creation_source WHERE (t1.tspend < t2.tspend and t1.creation_source = 'AI_ASSISTED') OR (t1.tspend > t2.tspend and t1.creation_source = 'MANUAL')