Quiz · Hard
Running totals and frames, LAG, LAST_VALUE, ranking with ties, NTILE, moving averages, integer division and gaps-and-islands, all with worked tables.
sales(day, rep, amount)
2025-01-01 Ana 100 | 2025-01-02 Ana 150 | 2025-01-02 Ben 150
2025-01-03 Ana 120 | 2025-01-03 Ben 90 | 2025-01-04 Ben 200 | 2025-01-05 Ana 60What value does run have in the row (2025-01-02, Ben)?
SELECT day, rep, SUM(amount) OVER (ORDER BY day) AS run
FROM sales ORDER BY day, rep400
250
150
100
With ORDER BY and no frame, the default is RANGE UNBOUNDED PRECEDING to CURRENT ROW, which includes all peers (rows with the same day). Both 2025-01-02 rows therefore show 100 + 150 + 150 = 400.
sales(day, rep, amount)
2025-01-01 Ana 100 | 2025-01-02 Ana 150 | 2025-01-02 Ben 150
2025-01-03 Ana 120 | 2025-01-03 Ben 90 | 2025-01-04 Ben 200 | 2025-01-05 Ana 60What value does run have in the row (2025-01-03, Ben)?
SELECT day, rep, SUM(amount) OVER (
ORDER BY day, rep ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run
FROM sales520
400
610
710
Ordered by day then rep, the rows before and including (01-03, Ben) are 100, 150, 150, 120 and 90, which add to 610. Ordering by a unique key makes the running total deterministic.
sales(day, rep, amount)
2025-01-01 Ana 100 | 2025-01-02 Ana 150 | 2025-01-02 Ben 150
2025-01-03 Ana 120 | 2025-01-03 Ben 90 | 2025-01-04 Ben 200 | 2025-01-05 Ana 60What is prev for the day 2025-01-03?
SELECT day, total, LAG(total) OVER (ORDER BY day) AS prev
FROM (SELECT day, SUM(amount) AS total FROM sales GROUP BY day) d210
300
100
NULL
Daily totals are 100, 300, 210, 200, 60. LAG returns the previous row's total: 300 for 01-03. Only the first day gets NULL.
sales(day, rep, amount)
2025-01-01 Ana 100 | 2025-01-02 Ana 150 | 2025-01-02 Ben 150
2025-01-03 Ana 120 | 2025-01-03 Ben 90 | 2025-01-04 Ben 200 | 2025-01-05 Ana 60What is lv in the row (2025-01-01, Ana)?
SELECT day, rep, amount, LAST_VALUE(amount) OVER (
PARTITION BY rep ORDER BY day) AS lv
FROM sales60
150
NULL
100
The default frame ends at the current row, so LAST_VALUE is the current row's own amount. To get Ana's last amount (60) add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
emp(id, name, manager_id, dept, salary)
1 Rhea NULL Eng 150 | 2 Sam 1 Eng 120 | 3 Tia 1 Eng 120
4 Uma 2 Eng 100 | 5 Vik 3 Ops 90 | 6 Wes 1 Ops 95What is dr for Uma (Eng, salary 100)?
SELECT name, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr FROM emp4
3
2
1
Eng salaries in descending order: 150 (1), 120 and 120 (both 2), 100 (3). DENSE_RANK has no gaps. RANK would give Uma 4.
emp(id, name, manager_id, dept, salary)
1 Rhea NULL Eng 150 | 2 Sam 1 Eng 120 | 3 Tia 1 Eng 120
4 Uma 2 Eng 100 | 5 Vik 3 Ops 90 | 6 Wes 1 Ops 95How many Eng employees does WHERE rk <= 2 return when rk is RANK() by salary descending within the department? (ROW_NUMBER with the same filter would return 2.)
2
4
1
3
Rhea has rank 1; Sam and Tia tie for rank 2. All three satisfy rk <= 2, while ROW_NUMBER would split the tie arbitrarily and return only two.
emp(id, name, manager_id, dept, salary)
1 Rhea NULL Eng 150 | 2 Sam 1 Eng 120 | 3 Tia 1 Eng 120
4 Uma 2 Eng 100 | 5 Vik 3 Ops 90 | 6 Wes 1 Ops 95NTILE(4) is applied to these 6 rows ordered by salary. How many rows go into each of buckets 1, 2, 3 and 4?
2, 2, 1, 1
2, 2, 2, 0
1, 1, 2, 2
2, 1, 2, 1
6 rows over 4 buckets: each bucket gets 1 row and the remaining 2 rows go to the first buckets, giving sizes 2, 2, 1, 1.
sales(day, rep, amount)
2025-01-01 Ana 100 | 2025-01-02 Ana 150 | 2025-01-02 Ben 150
2025-01-03 Ana 120 | 2025-01-03 Ben 90 | 2025-01-04 Ben 200 | 2025-01-05 Ana 60What percent of the grand total (870) is Ben's 200 sale on 2025-01-04, computed as 100.0 * amount / SUM(amount) OVER ()?
20.0%
11.5%
23.0%
33.3%
200 / 870 = 22.99%, rounded to 23.0. OVER () with nothing inside makes the whole table one window.
sales(day, rep, amount)
2025-01-01 Ana 100 | 2025-01-02 Ana 150 | 2025-01-02 Ben 150
2025-01-03 Ana 120 | 2025-01-03 Ben 90 | 2025-01-04 Ben 200 | 2025-01-05 Ana 60Daily totals are 100, 300, 210, 200, 60. What is ma3 on 2025-01-03?
SELECT day, AVG(total) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3
FROM (SELECT day, SUM(amount) AS total FROM sales GROUP BY day) d203.3
300.0
210.0
236.7
The frame holds the current row and the 2 before it: (100 + 300 + 210) / 3 = 203.3. On 01-04 it would be (300 + 210 + 200) / 3 = 236.7.
What is wrong with this query?
SELECT name FROM emp
WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) = 1;ROW_NUMBER needs a PARTITION BY clause.
DESC is not allowed in a window ORDER BY.
Window functions cannot be used in WHERE; compute it in a subquery or CTE and filter outside.
Nothing, it returns the top earner.
Window functions are evaluated after WHERE, GROUP BY and HAVING. Wrap the window in a subquery and put WHERE rn = 1 in the outer query.
In PostgreSQL, what does SELECT 7 / 2 return?
3.5
3 (integer division)
4
2
Both operands are integers, so the result is an integer, truncated toward zero. Use 7 / 2.0 or 7::numeric / 2 for 3.5. MySQL's / would return 3.5 instead.
What does this return?
SELECT COALESCE(NULLIF(0, 0), -1)0
NULL
An error: division by zero
-1
NULLIF(0, 0) returns NULL because the arguments are equal; COALESCE then returns the first non-NULL argument, -1.
sales(day, rep, amount)
2025-01-01 Ana 100 | 2025-01-02 Ana 150 | 2025-01-02 Ben 150
2025-01-03 Ana 120 | 2025-01-03 Ben 90 | 2025-01-04 Ben 200 | 2025-01-05 Ana 60What does this conditional aggregation return?
SELECT SUM(CASE WHEN day > '2025-01-03' THEN amount ELSE 0 END) FROM sales WHERE rep = 'Ben'440
200
240
60
Ben's sales after 2025-01-03: only the 200 on 01-04. 440 is Ben's total and 240 is his total up to 01-03.
The activity days for one user are 1, 2, 3, 5, 6 (as day numbers). In the gaps-and-islands technique you subtract ROW_NUMBER() OVER (ORDER BY day) from each day. How many distinct values (islands) result?
1
5
3
2
Day minus row number gives 0, 0, 0, 1, 1 for the days 1, 2, 3, 5, 6, so there are 2 islands: days 1-3 and days 5-6.
You want the 3 most recent orders per customer. Which approach is correct?
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) in a subquery, then WHERE rn <= 3
ORDER BY order_date DESC LIMIT 3 GROUP BY customer_id
SELECT TOP 3 customer_id, MAX(order_date) FROM orders GROUP BY customer_id
WHERE COUNT(*) <= 3
LIMIT applies to the whole result, not per group, and aggregates cannot be used in WHERE. A ranked window per customer followed by an outer filter is the standard pattern (LATERAL joins also work in PostgreSQL).