SQL Project · Beginner · 3 to 4 hours
Answer a coffee-gear seller's business questions in SQL
JOINs, GROUP BY, and date functions on a Shopee-style store's real questions: repeat buyers, top provinces, and best months.
The brief
Kape Diaries PH sells coffee gear and local beans online. The owner exports two files, customers and orders, and has marketing money to spend but no idea where. She asks: who are my repeat buyers, which provinces should I target with ads, which products carry the store, and which month had the strongest average order value?
Your role
You are the store's first data analyst. Deliverable: a set of queries plus a short written answer to each business question.
The datasets
Customer master list with province
ecommerce-customers.csv · 150 rows
Columns: customer_id, customer_name, province, signup_date
Orders from January to June 2026
ecommerce-orders.csv · 363 rows
Columns: order_id, customer_id, order_date, product, category, qty, unit_price
Setup
- Pick a free tool: DB Browser for SQLite (easiest), BigQuery sandbox, or MySQL/PostgreSQL if you have one installed.
- Import ecommerce-customers.csv as table customers and ecommerce-orders.csv as table orders.
- Confirm the import: customers has 150 rows, orders has 363 rows.
Your tasks
Work through these in order, the way the engagement would actually run.
- 1Write a query for total revenue (qty times unit_price summed across all orders).
- 2Count distinct buyers, then count buyers with more than one order (HAVING), and compute the repeat-purchase rate.
- 3JOIN orders to customers and rank provinces by total revenue.
- 4Rank products by revenue and by units sold; note where the two rankings disagree.
- 5Group revenue and order count by month, then compute average order value per month.
- 6Find the top 5 customers by lifetime spend, with name and province.
- 7Write each answer in one plain-English sentence under the query, the way you would report to the owner.
Work like an AI-powered analyst
The modern analyst uses AI as a thinking partner, not a shortcut that skips the learning. Try these on this project.
- When a query errors, paste the error and your SQL into ChatGPT or Claude and have it explain the fix instead of just fixing it.
- Ask the AI to generate 5 more business questions this schema can answer, then write the SQL yourself.
- Have the AI review your final queries for readability: aliases, formatting, and comments.
Expected output
- A .sql file with 6 to 8 clean, commented queries.
- A one-paragraph marketing recommendation: which provinces to target, which products to push, and why.
Check your numbers
Your results should match these. If they do not, that is the real learning: find out why.
- Total revenue is ₱441,907 across 363 orders from 108 distinct buyers.
- The repeat-purchase rate is about 77 percent of buyers.
- Metro Manila is the top province at ₱130,758 in revenue.
- The top product by revenue is the Gooseneck Kettle 1L at ₱100,000.
- April 2026 has the highest average order value at about ₱1,336.
Finished it? Put it in your portfolio.
This is exactly the kind of output the bootcamp builds with you live, with mentor feedback and an AI badge and certificate of completion at the end.