Quizz
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Empty parentheses
What does OVER () mean?
Hint : No PARTITION BY and no ORDER BY: one window, all rows.
Exercise 2 — Average of the type
Complete the query so that type_avg is the average level of the Codémons of the same type, next to each Codémon.
Hint : The average, in a window split by type.
Exercise 3 — Two counts
SELECT trainer_id, COUNT(*) FROM codemon GROUP BY trainer_id; and SELECT trainer_id, COUNT(*) OVER (PARTITION BY trainer_id) FROM codemon; — how many rows does each return?
Hint : Three trainers plus the NULL group make 4 groups; the window keeps all 8 Codémons.
Exercise 4 — Which function?
Match each need with the window function that answers it.
LAG()LEAD()FIRST_VALUE()ROW_NUMBER()Exercise 5 — Ranking 5, 5, 3
Three rows with values 5, 5 and 3. RANK() OVER (ORDER BY value DESC) gives:
Hint : Two rows share rank 1, so rank 2 is skipped.
Exercise 6 — Strongest first
Complete the query so that pos is 1 for the strongest Codémon, 2 for the next, and so on.
Hint : The window needs an order, from the highest level down.
Exercise 7 — A window in WHERE
SELECT name FROM codemon WHERE RANK() OVER (ORDER BY level) = 1; — what happens?
Hint : Compute the rank in a CTE, then filter the CTE.
Exercise 8 — Top two per trainer
Complete the query so that it keeps the two strongest Codémons of each trainer.
Hint : Filter the CTE, keeping positions 1 and 2.
Exercise 9 — A default for the first row
LAG(level, 1, 0) OVER (ORDER BY level) evaluated on the first row returns:
Hint : The third argument replaces the missing neighbour.
Exercise 10 — Running or not
Match each expression on the codemon table (total of levels: 92) with what it produces.
SUM(level) OVER ()SUM(level) OVER (ORDER BY level)SUM(level) OVER (PARTITION BY trainer_id)SUM(level) … GROUP BY trainer_idExercise 11 — The tenth badge
COUNT(*) OVER (ORDER BY earned_on) evaluated on the most recent of the 10 badges returns:
Hint : A running count: the last row sees every row before it, plus itself.
Exercise 12 — Three at a time
Complete the frame so that smoothed is the average level of each Codémon and the two before it (by codemon_id).
Hint : Physical rows, counted backwards from the current one.
Exercise 13 — Quartiles
NTILE(4) OVER (ORDER BY level DESC) on the 8 Codémons puts how many in each bucket?
Hint : 8 rows shared between 4 buckets.
Exercise 14 — LAST_VALUE by default
LAST_VALUE(name) OVER (ORDER BY level) with no frame clause returns, on each row:
Hint : Widen the frame with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the real last value.
Exercise 15 — A named window
Complete the query so that both columns reuse the same named window, one partition per trainer.
Hint : The window's name, and the clause that defines it after FROM.
Exercise 16 — Dense ranking
Badges given: 3, 2, 2, 2, 1. With DENSE_RANK() OVER (ORDER BY given DESC), the gym with 1 badge gets:
Hint : Dense: 1 for 3 badges, 2 for the three gyms at 2, then 3.
Exercise 17 — Window vocabulary
Match each keyword with its role inside a window function.
OVERPARTITION BYORDER BY (inside OVER)ROWS BETWEENExercise 18 — Most recent badge per trainer
Which query returns each trainer's most recent badge (one row per trainer)?
Hint : Latest first inside each trainer's partition, then position 1 — computed in a CTE.
Exercise 19 — The next battle
Complete the query so that next_battle is the date of the battle that follows each one.
Hint : Look forward, in date order.
Exercise 20 — Team sizes
Team sizes 3, 2, 2 and 0 ranked with RANK() OVER (ORDER BY team_size DESC) give:
Hint : RANK skips after a tie; DENSE_RANK would not.
Exercise 21 — Ties in a running total
Values 1, 2, 2, 2, 3 and SUM(value) OVER (ORDER BY value). What does the first row with value 2 get?
Hint : 1 + 2 + 2 + 2: the peers enter the frame together.
Exercise 22 — Badges of the same gym
Complete the query so that gym_total is, next to each badge, the number of badges handed out by the same gym.
Hint : Count the rows of a window split by gym.
Exercise 23 — Two ORDER BYs
In SELECT name, ROW_NUMBER() OVER (ORDER BY level DESC) AS pos FROM codemon ORDER BY name;, what do the two ORDER BY do?
Hint : Numbering order and display order are independent.
Exercise 24 — Choose the tool
Match each need with the right technique.
AVG(…) OVER (PARTITION BY …)ROW_NUMBER() OVER (ORDER BY …)julianday(x) - julianday(LAG(x) OVER (…))ROW_NUMBER() in a CTE, then WHERE rn <= 3Exercise 25 — Voltachu's leader
FIRST_VALUE(name) OVER (PARTITION BY trainer_id ORDER BY level DESC) evaluated on Voltachu (Alex's team, level 10) returns:
Hint : First row of the partition, strongest first: Aquachu (15).