Query 3 infers customer preference from product attributes, not customer data — this database has no field describing what any customer actually likes, only a detailed description of what they rented, including each film’s length. By categorizing every rental according to a characteristic of the film itself — here, whether it falls in the shortest quartile by length — then aggregating that categorization by whatever customer segment is available (here, country), a preference emerges that was never recorded directly anywhere.
The same idea works in any database that logs interactions between customers and products with describable attributes: a streaming platform has no field for “prefers short-form content,” but average watch time, categorized by content runtime and aggregated by subscriber region, reveals the same pattern. You’re not asking customers what they like — you’re inferring it from the measurable characteristics of what they actually chose.
Annotated Schema
Tables used in this query are highlighted. Others are shown for context.
Reasoning
NTILE(4), applied to every film ordered by length, splits the catalog into even quartiles — the shortest 25% of films by count become quartile 1. Worth noting: film length in this dataset turns out to be close to evenly distributed from 46 to 185 minutes, with no natural clustering around any particular runtime — so the quartile cutoff (80 minutes) isn’t marking a distinct “short film” category so much as a clean, rank-based 25% line through an otherwise smooth spread. Determining which quartile a film falls into needs only film.length and NTILE(4), ordered by length across every row in film — a single-table calculation with no join required.
Tying that per-film quartile to a specific country, however, requires the same kind of long join chain as Query 2 — six joins in total to attach a country to a specific film.
That film-level quartile gets tied to individual rentals through a CASE WHEN ... IN (...) subquery: for each rental, check whether its film.film_id appears in the pre-computed list of quartile-1 film IDs, and flag it with a 1 if so. From there, it’s a straightforward ratio per country — count of short-film rentals divided by total rentals, as a percentage — sorted to surface the top 10.
/*
Query 3 - Query used for Slide 3
Question: What percent of each country's total rentals are represented by films in the shortest quartile (by length). Show only the top 10 countries.
*/
/* table1 creates table of each rental_id, the country of its customer, its film_id, and whether that film was in the shortest quartile (by length), or not. It uses a Window Function to determine films in the shortest quartile, and then uses as CASE WHEN statement to put a '1' in column ‘short’ if the film is in the shortest quartile. Otherwise, it leaves the column null. */
WITH table1 AS (
SELECT country,
rental_id,
f.film_id,
CASE WHEN i.film_id IN (
/* This subquery creates a table of film_id’s for the shortest quartile of films (by length) */
SELECT film_id
FROM (
SELECT f.film_id,
f.length,
NTILE (4) OVER (ORDER BY f.length) AS length_quartile
FROM film f
)sub1
WHERE length_quartile = 1
) THEN '1' END AS short
FROM country c
JOIN city c2
ON c.country_id = c2.country_id
JOIN address a
ON c2.city_id = a.city_id
JOIN customer c3
ON a.address_id = c3.address_id
JOIN rental r
ON c3.customer_id = r.customer_id
JOIN inventory i
ON r.inventory_id = i.inventory_id
JOIN film f
ON i.film_id = f.film_id
ORDER BY 1
)
/* The output table uses an aggregation to group table1 by country, sums the number of rentals in the shortest quartile, sums the number of total rentals, and calculates the ratio of rentals in the shortest quartile to total rentals. It is ordered by this ratio, from highest to lowest, showing only the top 10 countries. */
SELECT country,
COUNT (short) AS short_count,
COUNT (rental_id) AS rental_count,
CAST (COUNT (short) as FLOAT) / (COUNT (rental_id)) * 100 AS percent_of_rentals_in_shortest_quartile
FROM table1
GROUP BY 1
ORDER BY 4 DESC
LIMIT 10;
Appendix
Key Finding
Among the ten countries with the highest share of short-film rentals, French Guiana leads — over 40% of the movies its customers rented fall in the shortest quartile by length, the only country above 40%. Malawi (39.3%) and Ethiopia (39.1%) follow closely, with the remaining seven ranging down to Liechtenstein at 32.1%.