Query 1 compares Store 1 to Store 2 on total revenue, broken out by film category — but neither piece of that comparison sits anywhere near the other in the schema. Aggregating one fact, then splitting it by a second fact pulled from an unrelated part of the schema can relate seemingly disparate pieces of data. The same idea works on any relational database: for example, comparing Team A to Team B on total home runs, broken out by whether the game-day temperature was above or below 70°F. Game day weather doesn’t live on the player stats table — it would require reaching into a separate weather table with no natural adjacency to home run counts. Taken further, that second fact doesn’t even need to live in the same database at all — game-day temperature might come from an external weather dataset, joined entirely from outside the baseball database itself.
Annotated Schema
Tables used in this query are highlighted. Others are shown for context.
Reasoning
Revenue on its own is simple — sum every row in payment. The interesting part is how many ways that sum can be disaggregated simultaneously across multiple axes.
Splitting revenue by store is a short hop: payment → rental → inventory gets you to inventory.store_id, one additional join beyond the base aggregation. Splitting it 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 splits in a single result means joining six tables — inventory, rental, payment, film, film_category, category — to disaggregate total company revenue across two axes at once — inventory.store_id and category.name.
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;
Appendix
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.