Google SQL
Interview Questions

1 · ROW_NUMBER — top-N per group

The pattern that shows up in analyst-round questions as "the best / latest / second-highest X for every Y". Number the rows inside each partition, then keep the numbers you want. The WHERE rn <= 2 has to live outside the window — a window function cannot be filtered in the same SELECT that defines it, which is why the CTE is there.

WITH ranked AS (
  SELECT name, department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT department, name, salary, rn
FROM ranked
WHERE rn <= 2
ORDER BY department, rn

The trap is ties. ROW_NUMBER breaks them arbitrarily and always returns exactly N rows per group; DENSE_RANK lets tied salaries share a rank, so "second highest" can return two people. Say which one the question wants before you write it — that sentence is usually worth more than the query.

2 · LAG — month-over-month growth

The pattern behind every "how did this month compare to last month" question: aggregate to one row per period first, then reach back one row with LAG. Doing the LAG over raw orders compares orders, not months — the GROUP BY has to happen in the first CTE.

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(total) AS revenue
  FROM orders
  GROUP BY 1
),
with_prev AS (
  SELECT month, revenue,
         LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly
)
SELECT month, revenue, prev_revenue,
       ROUND(100.0 * (revenue - prev_revenue) / prev_revenue, 1) AS mom_pct
FROM with_prev
ORDER BY month

The trap is the first row. LAG has nothing to look back at, so prev_revenue is NULL and the division comes out NULL rather than erroring — which is fine, as long as you say so. The same two-step (period aggregate, then LAG) is also how consecutive-day streaks and gap detection start; only the PARTITION BY changes.