Practice SQL Queries (by Interview Pattern)

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY group_col
      ORDER BY rank_col DESC, tiebreak_col
    ) AS rn
  FROM source
)
SELECT * FROM ranked WHERE rn <= N;

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY natural_key
      ORDER BY updated_at DESC, id DESC
    ) AS rn
  FROM source
)
SELECT * FROM ranked WHERE rn = 1;

WITH gapped AS (
  SELECT user_id, event_at,
    CASE WHEN event_at - LAG(event_at) OVER (
      PARTITION BY user_id ORDER BY event_at) > INTERVAL '30 minutes'
    THEN 1 ELSE 0 END AS new_session
  FROM events
),
sessioned AS (
  SELECT *, SUM(new_session) OVER (
    PARTITION BY user_id ORDER BY event_at) AS session_id
  FROM gapped
)
SELECT user_id, session_id, MIN(event_at), MAX(event_at)
FROM sessioned
GROUP BY user_id, session_id;

SELECT user_id,
       MIN(activity_date) AS streak_start,
       MAX(activity_date) AS streak_end,
       COUNT(*) AS streak_length
FROM (
  SELECT user_id, activity_date,
    activity_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY activity_date
    )) * INTERVAL '1 day' AS streak_key
  FROM user_activity
) t
GROUP BY user_id, streak_key
ORDER BY user_id, streak_start;

SELECT f.fact_id, f.event_time, d.attribute_value
FROM fact_table f
LEFT JOIN dim_scd2 d
  ON d.entity_id = f.entity_id
 AND d.effective_from <= f.event_time
 AND (d.effective_to IS NULL OR f.event_time < d.effective_to);

SELECT
  signup_month,
  COUNT(*) FILTER (WHERE status = 'active') AS active,
  COUNT(*) FILTER (WHERE status = 'churned') AS churned,
  100.0 * COUNT(*) FILTER (WHERE status = 'active') / COUNT(*) AS active_pct
FROM users
GROUP BY signup_month;

SELECT
  account_id, posted_at, amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY posted_at
    ROWS UNBOUNDED PRECEDING
  ) AS running_balance,
  AVG(amount) OVER (
    PARTITION BY account_id
    ORDER BY posted_at
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7
FROM transactions;

MERGE INTO daily_revenue d
USING (
  SELECT
    DATE(event_at) AS revenue_date,
    SUM(amount) AS revenue
  FROM events
  WHERE processed_at >= :since
  GROUP BY DATE(event_at)
) src
ON d.revenue_date = src.revenue_date
WHEN MATCHED THEN UPDATE SET revenue = d.revenue + src.revenue
WHEN NOT MATCHED THEN INSERT (revenue_date, revenue)
VALUES (src.revenue_date, src.revenue);