3. SQL for product analysts

-- events(user_id, event_name, ts, platform, properties)
-- Daily active users by platform, last 30 days
SELECT DATE_TRUNC('day', ts) AS day,
       platform,
       COUNT(DISTINCT user_id) AS dau
FROM   events
WHERE  ts >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1, 2
ORDER BY day;

-- Each user's FIRST purchase (the activation event)
WITH ranked AS (
  SELECT user_id, ts, amount,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts) AS rn
  FROM   events WHERE event_name = 'purchase'
)
SELECT user_id, ts AS first_purchase_at, amount
FROM   ranked WHERE rn = 1;

-- Day-7 retention by signup-week cohort
WITH signups AS (
  SELECT user_id, DATE_TRUNC('week', MIN(ts)) AS cohort
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('day', ts) AS active_day
  FROM events
)
SELECT s.cohort,
       COUNT(DISTINCT s.user_id) AS cohort_size,
       COUNT(DISTINCT CASE WHEN a.active_day = s.cohort + INTERVAL '7 days'
             THEN a.user_id END) AS retained_d7
FROM signups s
LEFT JOIN activity a ON a.user_id = s.user_id
GROUP BY s.cohort ORDER BY s.cohort;

-- View -> Add to cart -> Purchase funnel
SELECT
  COUNT(*)                                    AS viewed,
  COUNT(cart_ts)                              AS added,
  COUNT(CASE WHEN buy_ts > cart_ts THEN 1 END) AS bought
FROM (
  SELECT user_id,
    MIN(CASE WHEN event_name='view' THEN ts END) AS view_ts,
    MIN(CASE WHEN event_name='cart' THEN ts END) AS cart_ts,
    MIN(CASE WHEN event_name='buy'  THEN ts END) AS buy_ts
  FROM events GROUP BY user_id
) u;

WITH gaps AS (
  SELECT user_id, ts,
    CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts)
           > INTERVAL '30 minutes' THEN 1 ELSE 0 END AS new_session
  FROM events
)
SELECT user_id, ts,
  SUM(new_session) OVER (PARTITION BY user_id ORDER BY ts) AS session_id
FROM gaps;