Lesson 20.3

The Most Common Value in SQL

MODE() returns the most frequent value in one call, but it hides how frequent and silently picks a winner when values tie.

The average and the median describe numbers. For a category such as a genre, a landing page, or an order status, the useful summary is the mode: the value that turns up most often. DuckDB has an aggregate for it:

Pattern

select c.city,
       mode(m.genre) as most_rented_genre
from rentals as r
join customers as c on c.customer_id = r.customer_id
join movies as m on m.movie_id = r.movie_id
group by c.city
order by c.city

What mode() does not tell you

That query gives every city a genre, and it looks finished. Count the rentals behind it and two problems show:

The counts behind the mode

select c.city,
       m.genre,
       count(*) as rentals
from rentals as r
join customers as c on c.customer_id = r.customer_id
join movies as m on m.movie_id = r.movie_id
group by c.city, m.genre
order by c.city, rentals desc, m.genre

Austin's Thriller wins with 3 rentals. Denver's top two genres have 2 each, and Portland has a three-way tie at 2. mode() returns one of the tied values without saying there was a tie, and which one it returns is not something to rely on. It also drops the count, so a genre rented twice looks as decisive as one rented thirty times.

Count, then pick

The dependable version counts first and then keeps the top row per group, the pattern from Lesson 19.1 · Ranking and ties. row_number() with a tie-breaker in the ORDER BY gives exactly one winner, chosen by a rule you can state. If a tie should show every winner, use rank() instead:

Every tied favorite

with genre_rentals as (
  select c.city, m.genre, count(*) as rentals
  from rentals as r
  join customers as c on c.customer_id = r.customer_id
  join movies as m on m.movie_id = r.movie_id
  group by c.city, m.genre
)
select city, genre, rentals
from genre_rentals
qualify rank() over (partition by city order by rentals desc) = 1
order by city, genre

QUALIFY is DuckDB shorthand; in PostgreSQL or MySQL, compute the row_number() in a CTE and filter on it in the outer query. PostgreSQL's own version of the aggregate is mode() within group (order by genre), with the same blind spot for ties.

Schema · Movie Rentals Tables · rentals, customers, movies 3 tables

Table · rentals

One row per rental. Movies still out have a null returned_date.

6 columns · 26 rows

rental_id int customer_id int movie_id int rental_date date returned_date date amount decimal

Table · customers

One row per customer.

5 columns · 10 rows

customer_id int first_name text last_name text city text joined_date date

Table · movies

One row per movie in the catalog.

5 columns · 12 rows

movie_id int title text genre text release_year int rating decimal

Syntax reference

Need syntax details? See the SQL statistical aggregate reference

Your task

For each customer city, return city, genre (the genre rented most often by customers in that city), and rentals (how many times it was rented). When genres tie, keep the one that comes first alphabetically. Sort by city.

Movie Rentals · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: city, genre, rentals.
Rows: one per city, 4 in all.
Austin's favorite is clear: Thriller with 3 rentals. Denver and Portland are ties, settled alphabetically: Drama and Animation.

Previous · 20.2 Standard deviation

Next · 20.4 Building a histogram →

Reference

SQL Statistical Aggregate Functions

Median, percentiles, standard deviation, mode, and histograms.

Cheatsheet

SQL Distributions Cheatsheet

Median, percentiles, spread, mode, and histogram recipes.

Mistake

Why rank() = 1 Returns More Than One Row

Tied rows share rank 1; break the tie on purpose.

Reference