Where a Digital Media Store Makes Its Money
I took a fictional music store's sales history and asked three real business questions: who are the most valuable customers, which countries are growing, and which artists were being undercounted because of messy data. Below are the queries I wrote to answer each one — click any of them to load and run it.
Dataset: Chinook (digital music store). Analysis and queries are my own.
Three business questions, three queries
Each card loads a full query. Run it as-is, read the notes below the results, or edit the SQL and run your own version.
Query output
Results appear here after you run a query.
Database reference 11 tables · click to preview
Techniques used
What each concept means, in plain English.
-
CTE Common Table Expression
A way of breaking a complex query into named steps — like writing a recipe where each ingredient is prepared separately before combining them. Makes the logic easier to read, test, and maintain.
-
Window Function LAG · NTILE · SUM OVER
Calculations that look across multiple rows without collapsing the data. Used here to track each customer's running spend over time, flag gaps between purchases, and rank customers into four equal value groups.
-
Conditional Pivot CASE WHEN aggregation
Turning rows into columns inside a single query — for example, showing each country's revenue broken down by year across one row instead of many. This is the pattern BI tools use under the hood.
-
1NF Fix First Normal Form — data normalisation
The Composer column in this dataset stores multiple names in one cell (e.g. "Lennon, McCartney"). That inflates counts. A recursive split corrects it at query time so every name is counted once, without altering the database.