Skip to content
Tech Interview Prep
Technical interview guide

Window Functions

Per-row calculations across a related set of rows (running totals, rankings, and row-over-row comparisons) without collapsing rows like GROUP BY does.

Read
23 min
Practice MCQs
25
Interview Q&A
25
Edition
v3
Editorial status
Reviewed

Scope: SQL principles with PostgreSQL 18 examples; vendor-specific behavior must be verified.

Curated: · Written: · Reviewed:

25 questions

MCQ-16Table `daily_sales` has one row per day: | day | amount | | 2024-01-01 | 100 | | 2024-01-02 | 130 | | 2024-01-03 | 120 | | 2024-01-04 | 150 | A query computes day-over-day change: ```sql SELECT day, amount - LAG(amount, 1, 0) OVER (ORDER BY day) AS delta FROM daily_sales; ``` What is `delta` for 2024-01-01?
MCQ-7A table daily_signups(day, region, signups) holds one row per region per day. For region 'eu' the rows are 2024-03-01 → 40, 2024-03-02 → 55, 2024-03-03 → 52. The team runs: SELECT day, signups - LAG(signups, 1, 0) OVER (PARTITION BY region ORDER BY day) AS delta FROM daily_signups; In day order, what does delta hold for the three eu rows?
MCQ-25A `spend` table holds one row per customer per month: `(customer_id, month, amount)`. The rows were inserted in this order: `(7,'2024-01',100)`, `(7,'2024-03',120)`, `(7,'2024-02',130)`, `(9,'2024-02',80)`, `(9,'2024-01',50)`. A teammate wrote this query to compute month-over-month change: ```sql SELECT customer_id, month, amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY amount) AS mom FROM spend ORDER BY customer_id, month; ``` What does the `mom` column contain, in the order the query returns the rows?
MCQ-1An engineer needs one row per order with the customer's total spend repeated on every row of that customer, and writes: ```sql SELECT order_id, customer_id, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_id) AS cust_total FROM orders; ``` The `orders` table is: | order_id | customer_id | amount | | --- | --- | --- | | 1 | 10 | 100 | | 2 | 10 | 50 | | 3 | 20 | 200 | | 4 | 20 | 30 | | 5 | 20 | 70 | What does `cust_total` hold on the row for `order_id = 4`?
MCQ-6An `orders` table holds five rows: | order_id | region | amount | | --- | --- | --- | | 1 | east | 10 | | 2 | west | 20 | | 3 | east | 30 | | 4 | west | 40 | | 5 | east | 50 | This query runs against it: ```sql SELECT order_id, region, amount, SUM(amount) OVER (PARTITION BY region ORDER BY order_id) AS running FROM orders; ``` `order_id` is unique, so the default `RANGE` frame and an equivalent `ROWS` frame produce the same result. What value does `running` hold on the row where `order_id = 3`?
MCQ-14A 10-row partition is ordered by spend descending and `NTILE(4) OVER (ORDER BY spend DESC)` is computed. What value does the function assign to the row with the highest spend?
MCQ-3A leaderboard table `scores(player, pts)` holds five rows: alice 90, bob 90, carol 80, dave 80, erin 70. On PostgreSQL 16 the query `SELECT player, RANK() OVER (ORDER BY pts DESC) AS rnk, DENSE_RANK() OVER (ORDER BY pts DESC) AS drnk FROM scores;` runs. What are `rnk` and `drnk` on carol's row?
MCQ-19Postgres 16. Table games(player, score) holds (alice, 90), (bob, NULL), (carol, 75), (dave, 90). The query SELECT player, row_number() OVER (ORDER BY score DESC) AS rn FROM games; runs. Which player is assigned rn = 1?
MCQ-24A `sales` table has columns `region`, `sale_day`, `revenue`. This query runs against it: ```sql SELECT region, sale_day, revenue, SUM(revenue) OVER (PARTITION BY region ORDER BY sale_day) AS running FROM sales ORDER BY revenue DESC; ``` What does the result set look like?
MCQ-4A table `events` holds four rows: (ts=1, v=1), (ts=2, v=2), (ts=2, v=4), (ts=3, v=8). On PostgreSQL 16 an analyst runs: ```sql SELECT ts, v, SUM(v) OVER (ORDER BY ts) AS running FROM events ORDER BY ts; ``` No frame clause is written. What does the running column show for the two rows with ts=2?
MCQ-23A table `trades(day, amount)` holds (1,10), (2,20), (2,30), (3,5). `SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running FROM trades ORDER BY day` returns 10, 60, 60, 65; the engineer expected 10, 30, 60, 65. Which statement explains the two 60s?
MCQ-5A query `SELECT ts, LAST_VALUE(status) OVER (PARTITION BY account_id ORDER BY ts) AS latest FROM events` returns each row's own status instead of the account's most recent status. Which statement explains the result?
MCQ-18A table `readings(ts int, value int)` holds five rows: | ts | value | |----|-------| | 1 | 10 | | 2 | NULL | | 3 | 20 | | 4 | NULL | | 5 | 30 | On PostgreSQL 16 the team runs: ```sql SELECT ts, AVG(value) OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_avg FROM readings ORDER BY ts; ``` What does `running_avg` hold on the `ts = 5` row?
MCQ-8A table `sales(region, amount)` holds East: 120, 80 and West: 250. This query runs: ```sql SELECT region, SUM(amount) AS region_total, SUM(SUM(amount)) OVER () AS x FROM sales GROUP BY region; ``` What value does `x` hold on the East row?
MCQ-17A report returns one row per order, with a figure about the ordering customer beside it. The team's rule is that every such figure must come from grouping the orders by customer and joining the one-row-per-customer result back to the order rows. Which figure cannot be produced that way?
MCQ-20Table `orders` has these rows: | order_id | customer_id | amount | status | | --- | --- | --- | --- | | 1 | A | 120 | paid | | 2 | A | 30 | paid | | 3 | A | 200 | refunded | | 4 | B | 50 | paid | | 5 | B | 75 | paid | | 6 | B | 300 | refunded | The query is: ```sql SELECT order_id, COUNT(*) OVER (PARTITION BY customer_id) AS all_orders, COUNT(*) FILTER (WHERE status = 'paid') OVER (PARTITION BY customer_id) AS paid_orders FROM orders; ``` What does the query return for order_id = 3?
MCQ-2On PostgreSQL 15, a query keeps paid orders with `WHERE status = 'paid'` and ranks them with `ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC)`. It must return only the newest paid order per customer. Where does the `rn = 1` filter go?
MCQ-10An orders table has 1,000 rows, 400 of them paid. This query returns 400 rows: ```sql SELECT ts, amount, SUM(amount) OVER (ORDER BY ts) AS running FROM orders WHERE status = 'paid'; ``` What is the value of `running` on the row with the largest `ts`?
MCQ-22PostgreSQL 16 rejects both forms below: the first with `column "rnk" does not exist`, the second with `window functions are not allowed in HAVING`: ```sql SELECT customer_id, SUM(total) AS cust_total, RANK() OVER (ORDER BY SUM(total) DESC) AS rnk FROM orders GROUP BY customer_id HAVING rnk <= 10; SELECT customer_id, SUM(total) AS cust_total FROM orders GROUP BY customer_id HAVING RANK() OVER (ORDER BY SUM(total) DESC) <= 10; ``` Why can a window function not appear in a HAVING clause?
MCQ-9A query ranks sales reps within each region by amount and keeps only the rows the ranking function places in positions 1 through 3. It must return exactly 3 rows per region, and the same 3 rows on every run. In region EU the amounts are 900, 800, 700 and 700. Which expression satisfies both requirements?
MCQ-13A per-customer top-three report keeps rows where ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY revenue DESC) <= 3. Orders for one customer can share the same revenue, and the report must return the same three rows on every run, including after the table is dumped and restored into a fresh cluster. The table has a surrogate primary key order_id and a created_at timestamp recorded to the second. Which ORDER BY makes the result reproducible?
MCQ-12A query computes `SUM(x) OVER (PARTITION BY a ORDER BY t)` and `AVG(y) OVER (PARTITION BY a ORDER BY t ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)`. The team rewrites it as `WINDOW w AS (PARTITION BY a ORDER BY t)`, then `SUM(x) OVER w` and `AVG(y) OVER (w ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)`. What does naming the window contribute here?
MCQ-11A table `readings(day date, value numeric)` holds one row per day that has a reading; about one day in five has no row. A query computes `avg(value) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)` and the result is published as a '7-day average'. Which statement about that published figure is correct?
MCQ-21An orders table has duplicate order_date values. For each row, an analyst wants the current row plus the rows for the three previous distinct order dates, with every row sharing a date counted as one step. Which frame specification produces that?
MCQ-15In PostgreSQL, a query computes row_number() OVER (PARTITION BY account_id ORDER BY created_at) over a large events table and reads only account_id and created_at from it. With an index on (account_id, created_at), the plan shows WindowAgg reading the index with no sort step; with an index on (created_at, account_id), it shows a Sort. Both indexes hold the same two columns and both plans read the index rather than the table. What explains the difference?