SQL Interview Edge: Master Window Functions in 60 Seconds

Window functions are an interview favorite because they compute per-row results without collapsing your data. The core pattern to remember:

<function>(...) OVER (
  PARTITION BY <cols>    -- optional: splits rows into groups
  ORDER BY <cols>        -- optional: defines ordering inside each partition
  ROWS|RANGE BETWEEN ...  -- optional: frame control for moving aggregates
)

Quick guide to the most-used functions:

• ROW_NUMBER(): gives a unique sequential number to rows in the window. Use when you need a deterministic, tie-breaking sequence.
• RANK(): assigns the same rank to ties but leaves gaps after ties (1, 2, 2, 4).
• DENSE_RANK(): like RANK() but without gaps (1, 2, 2, 3).
• SUM() OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING): running total.
• AVG() OVER (... ROWS BETWEEN n PRECEDING AND CURRENT ROW): moving average.

Important tips:

• Window functions do NOT reduce the number of rows — they add per-row calculations.
• You must specify ORDER BY for running totals and moving aggregates to be deterministic.
• Use ROWS for a physical number of rows (e.g., last 3 rows), RANGE for value-based frames (can behave differently with ties).
• Window functions cannot be used in WHERE (use a subquery or CTE), but they can appear in SELECT and ORDER BY.

Examples you can memorize and adapt:

1) Running total by date

SELECT
  order_id,
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date
                    ROWS UNBOUNDED PRECEDING) AS running_total
FROM orders;

2) Salary rank within a department

SELECT
  employee_id,
  department,
  salary,
  RANK() OVER (PARTITION BY department
               ORDER BY salary DESC) AS dept_rank
FROM employees;

If you want no gaps in ranks, replace RANK() with DENSE_RANK().

3) 3-month moving average (per store)

SELECT
  store_id,
  month_date,
  sales,
  AVG(sales) OVER (PARTITION BY store_id
                    ORDER BY month_date
                    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3m
FROM store_monthly_sales;

Practice these 3 exercises until you can write the queries from memory:

• Running total for orders (global or per-customer).
• Department salary rank (and switch between RANK/DENSE_RANK/ROW_NUMBER).
• 3-period moving average using ROWS BETWEEN n PRECEDING AND CURRENT ROW.

Mastering these patterns gives you immediate leverage in interviews — window functions are a fast way to show SQL fluency.