SQL Case Study: E-commerce Sales Analysis
Part of the Data Analyst Roadmap
In short
This is the project that looks most like a real analyst job: messy business questions, several tables and a short report at the end. Interviewers love asking about it.
A realistic mini-project — given orders, products and customers tables, answer a set of real business questions end-to-end and document your queries and findings.
Think of it like this
The online shop's warehouse walk
A new warehouse manager walks the aisles with a clipboard: which shelves empty fastest, which never move, which customers order every week. Your case study is that walk, done with SQL, ending with advice for the owner.
Watch it happen
The case study plan
1/6Write 6 to 8 business questions: monthly revenue trend, top products, repeat customers, best cities.
The big ideas
What you need to know
Core e-commerce metrics
Revenue, orders, average order value, repeat purchase rate and top products. Define each clearly upfront.
Trends over time
Monthly revenue and order count show seasonality, like a festive season spike.
Customer behaviour
First-time vs repeat customers often tells a bigger story than total revenue.
Findings, not outputs
The report should say what to do. Raw query results belong in an appendix.
See it in code
Walk through the code
1WITH customer_orders AS (2 SELECT customer_id, COUNT(*) AS orders, SUM(total_amount) AS spend3 FROM orders4 WHERE status = 'delivered'5 GROUP BY customer_id6)7SELECT8 CASE WHEN orders = 1 THEN 'one-time' ELSE 'repeat' END AS segment,9 COUNT(*) AS customers,10 SUM(spend) AS revenue,11 ROUND(100.0 * SUM(spend) / SUM(SUM(spend)) OVER (), 1) AS pct_revenue12FROM customer_orders13GROUP BY 1;1/3Per customer: how many delivered orders and how much they spent.
One-time vs repeat customers and their share of revenue. Assumes orders has a total_amount column; adjust to your dataset.
Save yourself the pain
Common mistakes
Oops
Including cancelled and test orders in revenue.
Do this instead
Decide and state your filters at the top of the report, then apply them in every query.
Oops
Ending with 15 tables of numbers and no conclusion.
Do this instead
Lead with three findings and three recommendations. Tables support the story, they aren't the story.
Your mission
Milestone one: questions and schema
Quick check
Your report says average order value is ₹2,100. What should you state alongside it?
Go deeper
- 1Find or construct a small e-commerce dataset (orders, products, customers).
- 2Answer questions like 'top 10 products by revenue' and 'monthly repeat purchase rate'.
- 3Write a short summary of your findings, not just the raw query outputs.
More projects at this stop
Write 20 SQL Queries Against a Sample Database
Stuck on this topic? Ask an Insider
Get 1:1 guidance from people who've walked this exact path — on the InsideEdge app.
