SQL Window Functions Practice

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY event_timestamp DESC, event_id DESC
    ) AS rn
  FROM event_data
)
SELECT user_id, event_timestamp, event_id, payload
FROM ranked
WHERE rn = 1;

The single most useful window function pattern in data engineering. ROW_NUMBER assigns a unique rank inside each partition; filtering rn = 1 keeps exactly one row per key. Postgres has DISTINCT ON for this; BigQuery and Snowflake have QUALIFY. The CTE form works everywhere. Common mistakes: using DISTINCT (no control over which duplicate survives), GROUP BY with MAX (loses the other columns), ASC instead of DESC.

WITH ranked AS (
  SELECT
    department,
    emp_name,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS rk
  FROM employees
)
SELECT department, emp_name, salary
FROM ranked
WHERE rk <= 3
ORDER BY department, salary DESC;

Top 3 salaries per department, handling ties correctly. DENSE_RANK keeps tied employees and continues counting. RANK skips numbers after ties; if 2 share top, your WHERE rk <= 3 misses the next tier. ROW_NUMBER arbitrarily breaks ties and drops a real top-3 finisher. The interviewer's follow-up is always 'what changes if 2 employees share the highest salary'.

SELECT
  transaction_date,
  total_amount,
  SUM(total_amount) OVER (
    ORDER BY transaction_date
    ROWS UNBOUNDED PRECEDING
  ) AS running_total
FROM transactions
ORDER BY transaction_date;

Cumulative sum that grows with each row. ROWS UNBOUNDED PRECEDING means 'all rows up to and including the current one'. The frame clause is what makes this powerful. The trap: omitting the frame clause changes the default to RANGE UNBOUNDED PRECEDING, which behaves differently when the ORDER BY column has ties.

WITH daily_revenue_summary AS (
  SELECT
    date(transaction_date) AS order_date,
    SUM(total_amount) AS daily_revenue
  FROM transactions
  GROUP BY date(transaction_date)
)
SELECT
  order_date,
  daily_revenue,
  ROUND(AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS rolling_7d_avg
FROM daily_revenue_summary
ORDER BY order_date;

7-day rolling average. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW gives a frame of exactly 7 physical rows. The interviewer's follow-up is always about the first 6 rows where the frame is shorter than 7. Mentioning partial windows before being asked is the senior signal. Use ROWS over RANGE because RANGE collapses ties on the ORDER BY column, which on intraday or duplicated dates silently changes the answer.

WITH monthly AS (
  SELECT
    strftime('%Y-%m', transaction_date) AS month,
    SUM(total_amount) AS revenue
  FROM transactions
  GROUP BY 1
)
SELECT
  month,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS mom_delta,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS mom_pct
FROM monthly
ORDER BY month;

LAG accesses the previous row inline without a self-join. The CTE chain makes the 3 steps obvious: aggregate to month, LAG 1 row back, compute percent change. NULLIF on the LAG result is what interviewers want you to volunteer; the first month has no predecessor and naive division crashes or silently produces infinity.

-- Sessionize events with a 30-minute inactivity gap.
WITH with_gap AS (
  SELECT
    user_id,
    event_timestamp AS event_ts,
    LAG(event_timestamp) OVER (PARTITION BY user_id ORDER BY event_timestamp)
      AS prev_ts
  FROM event_data
),
flagged AS (
  SELECT
    user_id,
    event_ts,
    CASE
      WHEN prev_ts IS NULL
        OR (julianday(event_ts) - julianday(prev_ts)) * 24 * 60 > 30
      THEN 1 ELSE 0
    END AS new_session
  FROM with_gap
)
SELECT
  user_id,
  event_ts,
  SUM(new_session) OVER (
    PARTITION BY user_id ORDER BY event_ts
  ) AS session_id
FROM flagged;

Sessionize events using LAG to compare each event to the previous one within the user partition, then a running SUM over a binary flag to assign session IDs. The pattern combines 3 window-function ideas: PARTITION BY for per-user state, LAG for previous-row comparison, SUM OVER for cumulative ID assignment. Shows up at L5 and above when interviewers want to see whether you can compose window functions.