Quizz

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 1 — Empty parentheses

What does OVER () mean?

Options
• The window is the whole result: every row sees all the others
• The window is empty, so the function returns NULL
• The rows are grouped like with GROUP BY
• Only the current row is in the window

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?

Options
• 4 and 8
• 8 and 8
• 4 and 4
• 8 and 4

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.

Items to match
• The previous row's value
• The next row's value
• The first row of the frame
• A position with no ties
Definitions
• 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:

Options
• 1, 1, 3
• 1, 1, 2
• 1, 2, 3
• 1, 2, 2

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?

Options
• An error: a window function is not allowed in WHERE
• It returns the weakest Codémon
• It returns the strongest Codémon
• It returns every Codémon

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:

Options
• 0 — the default given as third argument
• NULL
• The row's own level
• An error

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.

Items to match
• SUM(level) OVER ()
• SUM(level) OVER (ORDER BY level)
• SUM(level) OVER (PARTITION BY trainer_id)
• SUM(level) … GROUP BY trainer_id
Definitions
• 92 on every row
• 8, 17, 27 … up to 92: a running total
• The team total on every row of the team
• One row per team, the Codémons disappear

Exercise 11 — The tenth badge

COUNT(*) OVER (ORDER BY earned_on) evaluated on the most recent of the 10 badges returns:

Options
• 10
• 1
• NULL
• 9

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?

Options
• 2
• 4
• 1
• 8

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:

Options
• The current row's own name — the default frame stops at the current row
• The strongest Codémon's name
• The weakest Codémon's name
• NULL

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:

Options
• 3
• 5
• 4
• 2

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.

Items to match
• OVER
• PARTITION BY
• ORDER BY (inside OVER)
• ROWS BETWEEN
Definitions
• Turns an aggregate or a ranking function into a window function
• Splits the window into groups
• Sets the order used for numbering and accumulating
• Chooses which neighbouring rows are in the frame

Exercise 18 — Most recent badge per trainer

Which query returns each trainer's most recent badge (one row per trainer)?

Options
• WITH r AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY trainer_id ORDER BY earned_on DESC) AS rn FROM badges ) SELECT * FROM r WHERE rn = 1;
• WITH r AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY trainer_id ORDER BY earned_on) AS rn FROM badges ) SELECT * FROM r WHERE rn = 1;
• SELECT * FROM badges ORDER BY earned_on DESC LIMIT 1;
• SELECT * FROM badges WHERE ROW_NUMBER() OVER (PARTITION BY trainer_id ORDER BY earned_on DESC) = 1;

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:

Options
• 1, 2, 2, 4
• 1, 2, 2, 3
• 1, 2, 3, 4
• 4, 2, 2, 1

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?

Options
• 7 — the default RANGE frame includes all the tied rows
• 3 — one row at a time
• 1
• 10

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?

Options
• The inner one numbers by level; the outer one displays the rows alphabetically
• The outer one wins: the numbering follows the names
• The inner one wins: the rows are displayed by level
• Two ORDER BY in one query is an error

Hint : Numbering order and display order are independent.

Exercise 24 — Choose the tool

Match each need with the right technique.

Items to match
• Compare each row with its group's average
• Number rows 1, 2, 3 without ties
• Days since the previous event
• The top 3 of each group
Definitions
• AVG(…) OVER (PARTITION BY …)
• ROW_NUMBER() OVER (ORDER BY …)
• julianday(x) - julianday(LAG(x) OVER (…))
• ROW_NUMBER() in a CTE, then WHERE rn <= 3

Exercise 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:

Options
• Aquachu — the strongest of Alex's team
• Voltachu — itself
• Aquachu's level, 15
• Plantachu — the weakest overall

Hint : First row of the partition, strongest first: Aquachu (15).

Back to the course