Project · SQL and Databases
Project: sales analysis for an online handloom shop
The brief
Handloom Haat, a small online shop in Jaipur, wants its first proper sales review for January to April 2025. Join four tables to value each order, find customers who never received anything, split revenue and GST by category, and use CTEs and window functions for top customers, product rankings, running totals and month-on-month change.
Why it matters
This is the everyday work of a data analyst at an e-commerce company or a D2C brand: join orders to customers and products, count only delivered sales, then rank and trend the numbers. Interviewers ask exactly these queries.
Skills you practise
- INNER and LEFT JOIN
- joining four tables
- CTEs (WITH)
- subqueries
- SUM OVER for running totals
- RANK with PARTITION BY
- LAG
What you will have at the end
Seven SQL files that make a four-month sales review for an online shop: order values, a win-back list, revenue and GST by category, top customers, product rankings, a running total and month-on-month change.
How it is marked (rubric, 100 points)
| Correct resultsAll seven checks pass, and only delivered orders count as revenue everywhere. | 40 |
|---|---|
| Joins and filters in the right placeJoin conditions are complete, and the status filter sits in ON for the LEFT JOIN and in WHERE elsewhere. | 20 |
| CTEs and window functions used wellEach CTE does one job with a clear name; PARTITION BY, ORDER BY and LAG are used where they fit. | 20 |
| Business summaryA half-page note for the owner: best category, March's dip, who to win back and which products to stock more of, with numbers. | 20 |
0/7 steps done
Step 1 of 7
What each delivered order was worth
01-order-values.sql lists each delivered order's id, the customer's name, the order date and its value (sum of qty × unit_price), by order id.
Four tables: customers, products, orders (one row per order, with a status) and order_items (one row per product in an order, with qty and the unit_price actually charged). An order's value is the sum of qty × unit_price over its items.
Join orders to customers on customer_id and to order_items on order_id, keep status = 'Delivered' (cancelled and returned orders earned nothing), then GROUP BY the order. Use unit_price from order_items, not the list price in products: the shop gave a discount on order 1006.
Run shows what your code prints. Check runs the step's tests. Ctrl+Enter checks too.