E-commerce Sales Analytics
SQL · Beginner · about 70 minutes · 7 required tasks
The scenario
You have just joined the analytics team of an online store. Sales, marketing and finance all ask the same two questions: who are our customers and what have they bought? Every task in this project queries the same two tables, customers and orders, so each report builds on the one before it: link orders to customers, measure customer activity, then produce the revenue views leadership asks for.
What you will build
- An orders-with-customer-names listing and a list of customers who have never ordered
- Per-customer order counts, average order amounts and most recent order dates
- A monthly revenue report and a top-5 customers-by-spend leaderboard
Skills practiced
- INNER and LEFT joins
- Anti-joins
- GROUP BY aggregates (COUNT, AVG, SUM, MAX)
- Monthly date grouping
- Top-N reporting
Stages
Connect orders to customers
Join the two tables and find the customers who have not ordered yet.
2 required tasks
Measure customer activity
Count orders, average order amounts and last-order dates per customer.
3 required tasks, 1 optional stretch task
Report revenue and top customers
Roll orders up into monthly revenue and rank customers by total spend.
2 required tasks, 1 optional stretch task
Every task opens in the regular auto-graded challenge editor. Tasks you have already solved count automatically once you sign in.