In partnership with
We have not done one of these in a while. Today we are going back to basics — SQL and a business question. Let's work through it together.
The question: if Chinook could only put marketing budget behind one genre, which one should it be?
This is the kind of question that comes up in take-home tasks and case study interviews. The most interesting part is not the SQL code — it is what happens when you look at the data from two different angles and get two different answers.
Before we proceed - a small ad. Your clicks on these links help me to cover newsletter hosting fees. Thank you for your support!
100+ coding prompts top engineers use to ship 5X faster
Claude Code, Codex, and Cursor are on every engineer's tech stack. Most still treat them like a search bar and get average output back. Top engineers work from a system instead. These 100+ prompts are that system.
Sign up for The Code and get:
100+ ready-to-use prompts built on real Claude Code, Codex, and Cursor workflows, so you ship in minutes instead of hours
The Code newsletter (5 min daily) to keep sharpening your AI-coding edge after the prompts land
The dataset
Chinook is a digital media store database — tracks, invoices, customers, genres. It is public, and it is a common reference for SQL tutorials.
Download it here: https://github.com/lerocha/chinook-database
Four tables matter for this project:
Invoice — one row per transaction, with customer ID and total
InvoiceLine — one row per track purchased, linked to Invoice
Track — track details including genre ID
Genre — genre names
The relationship: Invoice → InvoiceLine → Track → Genre. You need all four to answer our question.
Also, if you are just starting with SQL - for Windows computer I always default to MS SQL. For Mac I choose either MySQL or Postgresql with DBeaver.
Step 1 — Load the data and understand what you have
My first step is always explore the data to understand what we are working with. And my default query is:
select * from TABLE limit 100
After that we will do:
-- How many tracks per genre?
SELECT g.Name AS Genre, COUNT(t.TrackId) AS TrackCount
FROM Genre g
LEFT JOIN Track t ON g.GenreId = t.GenreId
GROUP BY g.Name
ORDER BY TrackCount DESC;
-- How many invoices and customers total?
SELECT COUNT(DISTINCT InvoiceId) AS Invoices,
COUNT(DISTINCT CustomerId) AS Customers
FROM Invoice;Rock dominates the track catalogue. That already tells you something — if Rock has the most tracks, it will naturally generate more revenue just by volume.
Keep that in mind before you draw conclusions.
Step 2 — Total revenue by genre
SELECT
g.Name AS Genre,
SUM(il.UnitPrice * il.Quantity) AS TotalRevenue,
SUM(il.Quantity) AS TracksSold,
COUNT(DISTINCT i.CustomerId) AS UniqueCustomers
FROM Genre g
JOIN Track t ON g.GenreId = t.GenreId
JOIN InvoiceLine il ON t.TrackId = il.TrackId
JOIN Invoice i ON il.InvoiceId = i.InvoiceId
GROUP BY g.Name
ORDER BY TotalRevenue DESC;Rock wins by a mile. $826.65 in revenue, 835 tracks sold, 59 unique customers.
If you stopped here, you would tell Chinook to double down on Rock.
But stopping here might be the wrong call, so we will keep digging.
Step 3 — Revenue per customer, not just total revenue
Total revenue is a function of volume. A genre with 59 customers will almost always beat one with 4.
The more interesting question is: how much does each customer spend?
SELECT
g.Name AS Genre,
COUNT(DISTINCT i.CustomerId) AS UniqueCustomers,
SUM(il.Quantity) AS TracksSold,
ROUND(SUM(il.UnitPrice * il.Quantity) * 1.0 /
COUNT(DISTINCT i.CustomerId), 2) AS RevenuePerCustomer
FROM Genre g
JOIN Track t ON g.GenreId = t.GenreId
JOIN InvoiceLine il ON t.TrackId = il.TrackId
JOIN Invoice i ON il.InvoiceId = i.InvoiceId
GROUP BY g.Name
ORDER BY RevenuePerCustomer DESC;This is where it gets interesting.
Rock still leads at $14.01 per customer.
But some much smaller genres suddenly start looking more interesting.
TV Shows, for example, generates $4.92 per customer despite having only 19 customers.
At first glance, you might assume:
"Maybe these niche customers are more valuable than we thought."
But this is exactly where we need to ask another question instead of jumping to the conclusion.
Why is their revenue per customer higher?
Step 4 — Check the price
Revenue per customer sounds like a customer-behaviour metric.
But revenue is affected by more than behaviour.
It is:
how much customers buy × how much those products cost.
So before we conclude that TV Shows customers are unusually valuable, let's check whether the products themselves are priced differently.
SELECT
g.Name AS Genre,
ROUND(AVG(il.UnitPrice), 2) AS AvgPrice,
SUM(il.Quantity) AS TracksSold,
COUNT(DISTINCT i.CustomerId) AS UniqueCustomers,
ROUND(
SUM(il.UnitPrice * il.Quantity) * 1.0 /
COUNT(DISTINCT i.CustomerId),
2
) AS RevenuePerCustomer
FROM Genre g
JOIN Track t
ON g.GenreId = t.GenreId
JOIN InvoiceLine il
ON t.TrackId = il.TrackId
JOIN Invoice i
ON il.InvoiceId = i.InvoiceId
GROUP BY g.Name
ORDER BY RevenuePerCustomer DESC;And now we find another piece of the story.
Many music tracks in Chinook sell for $0.99, while some video content sells for $1.99.
That means higher revenue per customer does not automatically mean stronger demand.
A customer can buy fewer items and still generate more revenue simply because those items cost more.
This is why one metric rarely gives you the full answer.
Our first query told us:
Rock generates the most revenue.
Our second told us:
Some smaller genres generate surprisingly high revenue per customer.
And our third tells us:
Part of that difference may be explained by price.
All three statements can be correct at the same time.
Step 5 — Check the sample size
There is one more problem.
Before presenting an interesting pattern as an insight, always check how much data is behind it.
SELECT
g.Name AS Genre,
COUNT(DISTINCT i.CustomerId) AS UniqueCustomers,
COUNT(il.InvoiceLineId) AS TracksSold,
ROUND(SUM(il.UnitPrice * il.Quantity) * 1.0 /
COUNT(DISTINCT i.CustomerId), 2) AS RevenuePerCustomer
FROM Genre g
JOIN Track t ON g.GenreId = t.GenreId
JOIN InvoiceLine il ON t.TrackId = il.TrackId
JOIN Invoice i ON il.InvoiceId = i.InvoiceId
GROUP BY g.Name
HAVING COUNT(DISTINCT i.CustomerId) < 25
ORDER BY RevenuePerCustomer DESC;TV Shows has only 19 customers. Some other niche genres have even fewer.
Maybe niche-genre customers really do behave differently. Or maybe the result is being driven by a handful of unusually active customers.
With a sample this small, we cannot confidently tell the difference.
And presenting a small-sample pattern as a confident business insight is the kind of thing that will raise questions in a case study interview — or, worse, lead to a bad decision in a real business.
Step 6 — What you would actually recommend
So let's go back to the original question:
If Chinook could only put marketing budget behind one genre, which one should it be?
Based on the data we have, I would choose Rock.
It has by far the largest revenue base, reaches all 59 customers, and still leads on revenue per customer.
It is the strongest signal we have.
But I would not ignore what we found in TV Shows and other niche genres.
I would treat those as hypotheses, not recommendations.
For example, Chinook could reserve a small test budget for targeting TV Shows customers and measure whether the higher revenue per customer holds when the audience grows.
If it does, then we have evidence to consider reallocating more marketing spend.
If it does not, we have learned something without betting the entire budget on a pattern from a small sample.
That is often where real analysis ends.
Not with:
"The answer is TV Shows."
But with:
"Rock is the best decision based on the evidence we have today. TV Shows gives us an interesting hypothesis worth testing next."
Many real projects end here — not with a clean answer, but with a good question and suggestion what to do next, with the judgement to know you do not have enough data yet to make a final business decision.
If you want to go further
Python users can do this same analysis with pandas and add a visualisation layer on top. The joins become merges, the GROUP BY becomes groupby, and you can plot the results with seaborn in a few extra lines. The logic is identical.
How to turn this into a portfolio project
Same structure as always:
Overview — Chinook digital media store, goal is to identify which genre should receive marketing investment based on revenue and customer value.
Approach — analysed total revenue by genre, then revenue per customer to control for volume differences, then checked sample sizes before drawing conclusions.
Findings — Rock leads on total revenue ($826.65) and revenue per customer ($14.01). TV Shows and Comedy show higher per-customer spend than their volume would suggest, but sample sizes are too small (19 and 4 customers respectively) to treat this as a reliable signal.
Recommendation — continue investing in Rock as the proven high-volume, high-value genre. Design a small test to grow the TV Shows and Comedy catalogue and customer base before making any reallocation decision.
Limitations — dataset is synthetic and small. Real decisions would require significantly more data, longer time periods, and customer acquisition cost data to calculate true ROI by genre.
Keep pushing 💪,
Karina
This week on YouTube - How to build a resume for a fresh graduate
I built a resume for Sarah — a business graduate with no data analyst experience — and rebuilt it section by section, live on screen. Watch it here → https://youtu.be/HtH4KqKxZ0E
If you are curious about building a personal brand on LinkedIn and eventually making money from it — my friend and I started a separate Substack for exactly that. We are already 200 followers in and sharing everything we know. Check it out here → https://thelinkedinlab.substack.com/
Just starting with Python? Wondering if programming is for you?
Master key data analysis tasks like cleaning, filtering, pivot and grouping data using Pandas, and learn how to present your insights visually with Matplotlib with ‘Data Analysis with Python’ masterclass.
Already know the basics and want something more hands-on?
Take the Python Challenge.
You'll work through a real business problem, complete a portfolio-ready project, and practise the kind of analysis employers expect from junior analysts.
👉 Start with the Masterclass if you're a complete beginner.
👉 Choose the Python Challenge if you're comfortable with the fundamentals and want to apply them to a real project.
Data Analyst & Data Scientist
