En anglais

Totals that keep every row

Computing · Data & Databases · ages 19-20

Name ______________________ Date ____________

A running sum is partitioned by customer. A's amounts are 6 then 4; B's only amount is 9. How is B's total calculated?
- Add A's 6 and 4 to B's amount 9.
- Use B's 9, without A's amounts.
- Use A's 6 and 4, leaving out B's 9.
Three order rows, ids 21, 22 and 23, reach a window function. With no later filtering or grouping, which rows remain when its result column is added?
- All three order rows stay
- One row per customer stays
- Only the newest order stays
Customer A has only two rows, with amounts 3 and 8. SUM(a) OVER(PARTITION BY c), with no window ORDER BY, gives 11 on both A rows.
Circle one: True False
Within one customer's partition, scores are 95, 81, 81 and 70. Which function, using OVER(PARTITION BY c ORDER BY score DESC), gives ranks 1, 2, 2 and 4?
- ROW_NUMBER
- DENSE_RANK
- RANK
Sales(c,id,a) has several customers; id is a unique non-null order key. Which PostgreSQL query keeps each order and adds its customer's running sum in id order?
- SELECT c,id,a,SUM(a) OVER(PARTITION BY c ORDER BY id ROWS UNBOUNDED PRECEDING) FROM Sales
- SELECT c,id,a,SUM(a) OVER(PARTITION BY c) AS total FROM Sales ORDER BY c,id
- SELECT c,id,a,SUM(a) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS total FROM Sales ORDER BY c,id
Sales has unique non-null order ids. A query uses SUM(a) OVER(PARTITION BY c) and ends with ORDER BY c,id. It repeats full totals. Which change makes each customer's sum run row by row in id order?
- Keep ORDER BY only outside OVER and remove PARTITION BY from the sum.
- Put ORDER BY id inside OVER and use ROWS UNBOUNDED PRECEDING.
- Keep ORDER BY only outside OVER and group the output by each customer.
One customer's rows have (id,a) values (2,11), (5,7), (9,18). LAG(a) OVER(PARTITION BY c ORDER BY id), with no default, returns 0,11,7 in id order.
Circle one: True False
A's scores are 88, 88, 72, 60 and 60; B has a score of 99. What ranks does A get from DENSE_RANK() OVER(PARTITION BY c ORDER BY score DESC), in A's listed order?
- 1, 1, 3, 4, 4
- 1, 2, 3, 4, 5
- 1, 1, 2, 3, 3

Answer key

For grown-ups. Fold this page away before handing over the rest.

Totals that keep every row W1-mt_aaVxFPjWQv-s1

Use B's 9, without A's amounts. · B is a separate customer partition. Its only amount is 9, so its running total is 9.
All three order rows stay · The window adds a result beside each order. It does not collapse the three order rows into customer summaries.
True · Without window ordering, this sum uses A's whole partition. Both retained rows receive 3 + 8 = 11.
RANK · RANK shares rank 2 between the tied scores. Those scores occupy positions 2 and 3, so the next rank is 4.
SELECT c,id,a,SUM(a) OVER(PARTITION BY c ORDER BY id ROWS UNBOUNDED PRECEDING) FROM Sales · Partitioning by c separates customers. Ordering by id and using a frame through the current row gives each order its customer's running sum.
Put ORDER BY id inside OVER and use ROWS UNBOUNDED PRECEDING. · Sorting the output does not control the window calculation. The window needs its own order and a frame from the first row through the current row.
False · The first row has no previous row, so LAG returns NULL without a supplied default. The remaining previous amounts are 11 and 7.
1, 1, 2, 3, 3 · A has three distinct scores. DENSE_RANK gives them ranks 1, 2 and 3. Equal scores share a rank, and B's separate partition does not affect A.