Answering eight real business questions with SQL on the sales database of a digital store: revenue KPIs, market concentration, catalogue performance, customer segmentation (RFM), cohort retention, team performance and basket analysis.
Stack: SQLite · SQL (CTEs, window functions, NTILE, LAG, running totals) · Python (pandas, matplotlib)
A digital store's management wants to know: Are we growing? Where does revenue come from? Which customers should we protect? Is the team balanced? This project answers each question with a documented SQL query and turns the results into recommendations.
Chinook is an open sample database that models an online music store. It has 11 tables covering customers, invoices, invoice lines, tracks, genres and employees: 412 orders, 59 customers and 24 countries between 2009 and 2013 ($2,329 revenue). The data is synthetic. Values such as order counts per customer are unusually uniform, and the analysis says so wherever it matters.
Customer ─< Invoice ─< InvoiceLine >─ Track >─ Genre
│ └──── Album >─ Artist
└── SupportRep (Employee)
Each question is one .sql file in sql/. The file starts with the business question and why it matters.
| # | Question | SQL techniques |
|---|---|---|
| 1 | Headline KPIs by year | aggregates, LAG for YoY growth |
| 2 | Monthly trend & seasonality | 3-month moving average window |
| 3 | Revenue by market & concentration | revenue share, running cumulative share |
| 4 | Genre performance vs. catalogue | multi-join, LEFT JOIN for unsold items |
| 5 | Customer value segmentation (RFM) | NTILE scoring, CASE segments |
| 6 | Cohort retention | first-purchase cohorts, retention % |
| 7 | Sales rep performance | attribution via customer → rep |
| 8 | Genres bought together | self-join for pairs |
scripts/run_analysis.py runs every query and writes the tables to results/RESULTS.md (plus CSVs) and the charts to results/charts/.
1. Revenue is flat because customer acquisition stopped. Revenue has sat at about $450–480 a year, with 83 orders a year, since 2009. Every one of the 59 customers made their first purchase in 2009 or 2010, and none were acquired after that. Retention is healthy (72–85% of each cohort buys again every year), so the business isn't losing customers. It just isn't gaining any. → Action: growth has to come from acquisition, such as referral offers or campaigns in new markets, not from retention spend.
2. Market size depends on how many customers a country has, not on how much each one spends. The USA and Canada bring in 35.5% of revenue, and the top 5 countries bring in 59%. But revenue per customer is almost identical everywhere (about $38–46). → Action: every new customer is worth roughly the same wherever they are, so acquisition budgets can go to the cheapest channels and markets.
3. Rock carries the store, and 43% of the catalogue has never sold. Rock is 35% of revenue. Only 56.6% of the 3,503 tracks have ever been bought. → Action: review licensing costs for tracks that have never sold. Feature best-selling genres in promotions.
4. 14 high-value customers are drifting away.
RFM segmentation flags 14 customers as "At risk (high value)". Together they have spent $579 (25% of all revenue), but their median time since the last order is 317 days, compared with 48 days for Champions.
→ Action: a targeted win-back email with a discount for these 14 names (listed in results/05_customer_rfm.csv).
5. The sales team is balanced. Revenue splits 36% / 33% / 31% across the three reps, and revenue per customer is almost the same for each. Differences come from the number of customers each rep manages, not from how well they perform. → Action: assign any new customers to reps by current workload.
6. Clear bundle opportunities. Rock is most often bought together with Alternative & Punk (54 orders), Latin (51) and Metal (46). → Action: "If you like Rock…" recommendations and genre bundles.
git clone https://github.com/nisha1324/sql-business-analytics.git
cd sql-business-analytics
pip install -r requirements.txt
python scripts/download_data.py # downloads the ~1 MB SQLite database into data/
python scripts/run_analysis.py # runs all queries -> results/You can also open data/chinook.sqlite in any SQL client (DB Browser for SQLite, DBeaver) and run the files in sql/ directly.
sql/ one documented query per business question
scripts/ data download + analysis runner
results/RESULTS.md all query outputs in one report
results/charts/ charts used above