Situation: Our analytics team needed to know why weekly active users (WAU) dropped by ~12% in the last month to decide whether to throttle an ad campaign.Schema:- events(user_id, event_time, event_type, product_id, platform)- users(user_id, created_at, country)- ad_impressions(user_id, impression_time, campaign_id)Task: Produce WAU by week, detect cohorts with biggest drop, and attribute to platform/campaign.Action — query I wrote (BigQuery / Postgres SQL style):sql
WITH user_events AS (
SELECT
user_id,
DATE_TRUNC(event_time, WEEK) AS week,
platform
FROM events
WHERE event_time BETWEEN '2025-09-01' AND '2025-10-31'
AND event_type = 'open'
),
weekly_active AS (
SELECT
week,
platform,
COUNT(DISTINCT user_id) AS wau
FROM user_events
GROUP BY week, platform
),
week_delta AS (
SELECT
week,
platform,
wau,
LAG(wau) OVER (PARTITION BY platform ORDER BY week) AS prev_wau,
SAFE_DIVIDE(wau - LAG(wau) OVER (PARTITION BY platform ORDER BY week),
LAG(wau) OVER (PARTITION BY platform ORDER BY week)) AS pct_change
FROM weekly_active
)
SELECT * FROM week_delta ORDER BY week, platform;
