A category-by-category breakdown of rental revenue between the two stores.
Business Question
How much more (or less) did Store 1 generate in rental revenue versus Store 2, in each film category?
Annotated Schema
Tables used in this query are highlighted. Others are shown for context.
Query Output
Key Finding
Across the 16 film categories, Store 1 outperformed Store 2 in 7, ranging from a +$485 lead in Drama down to a +$126 lead in Family. Store 2 outperformed Store 1 in the remaining 9, from a narrow -$60 gap in Games down to a -$398 deficit in Documentary. These category-level revenue gaps could inform resource allocation between the two stores, or help a store-level manager make stocking or promotional decisions specific to that location.
Reasoning
Revenue on its own is simple — sum every row in payment. The interesting part is how far that number can be broken apart before the schema stops giving you an easy path.
Breaking revenue out by store is a short hop: payment → rental → inventory gets you to store_id, one additional join beyond the base aggregation. Breaking it out by film category is a longer one — category isn’t adjacent to revenue at all. It lives off film, connected through the film_category junction table, three joins removed from payment in the opposite direction from store.
Combining both breakouts in a single result meant joining six tables — inventory, rental, payment, film, film_category, category — to synthesize two dimensions that don’t live anywhere near each other in the schema, rather than settling for whichever breakout the table structure made convenient.
The comparison itself uses a window function rather than a self-join. After grouping by category and store, each category has exactly two rows — one per store. LEAD(), partitioned by category and ordered by store, pulls Store 2’s revenue directly into Store 1’s row within that two-row partition, so the difference can be calculated without ever joining the aggregated result back to itself.
/* table1 creates a table of every combination of film category and store_id. It then uses an aggregation to create a column of the total rental revenue (sum of payment.amount) for each film category and store_id combination. It then uses a Window Function to create a column to help compare store1 revenue versus store2 revenue for each film category. Lastly, it creates a column representing the difference in store1 versus store2 revenue for each film category and store_id combination. */
WITH table1 AS (
SELECT c.name AS category_name,
i.store_id,
SUM (p.amount) AS rental_revenue,
LEAD (SUM (p.amount)) OVER (PARTITION BY c.name ORDER BY i.store_id) AS next_store_rental_revenue,
(SUM (p.amount)) - (LEAD (SUM (p.amount)) OVER (PARTITION BY c.name ORDER BY i.store_id)) AS store_rental_revenue_difference
FROM inventory i
JOIN rental r
ON i.inventory_id = r.inventory_id
JOIN payment p
ON r.rental_id = p.rental_id
JOIN film f
ON f.film_id = i.film_id
JOIN film_category fc
ON fc.film_id = f.film_id
JOIN category c
ON fc.category_id = c.category_id
GROUP BY 1, 2
ORDER BY 1, 2
)
/* the output table cleans up table1 so that you are left with a column of category names and a second column representing the difference in rental revenue between store1 and store2 for each category. A positive difference in rental revenue represents a category where store1 outperformed store2. A negative difference in rental revenue represents a category where store1 underperformed store2. */
SELECT category_name,
store_rental_revenue_difference
FROM table1
WHERE store_rental_revenue_difference IS NOT NULL
ORDER BY 2 DESC;