The Brief
You're a data analyst at ShopStream, an e-commerce marketplace. The executive team needs a quarterly business report to evaluate performance, spot trends, and guide strategy. Your job: build six SQL queries that together form a complete analytics dashboard.
This capstone brings together everything from Arc B: CTEs for readability, window functions for trends and ranking, multi-table joins for rich insights, CASE statements for business logic, and window functions for percentages and quartiles.
By the end, you'll have a portfolio-ready SQL analytics report that combines real business questions with clean, production-grade queries.
Q1: Executive Summary
Start with a high-level snapshot: total customers, total orders, total revenue, average order value, and delivered orders.
──────────────────────────────────────────────────────────────────────
100 | 300 | 220574.59 | 735.25 | 214
Breakdown:
COUNT(DISTINCT c.customer_id)— Unique customers in the system.SUM(o.total_amount)— Total revenue from all orders.AVG(o.total_amount)— Average order value (rounded to 2 decimals).CASE WHEN o.status = 'delivered'— Count only delivered orders, ignoring pending/cancelled.LEFT JOIN— Ensures all customers are counted, even those with no orders.
Q2: Monthly Revenue Trend (Month-over-Month Growth)
Track revenue by month and calculate the month-over-month growth percentage. This is where CTEs and window functions shine:
──────────────────────────────────────────────────────────────────────
2024-01-01 | 12178.83 | 14 | NULL | NULL
2024-02-01 | 4888.49 | 7 | 12178.83 | -59.9
2024-03-01 | 6539.65 | 10 | 4888.49 | 33.8
2024-04-01 | 9014.85 | 11 | 6539.65 | 37.8
2024-05-01 | 13573.40 | 20 | 9014.85 | 50.6
...
The CTE + Window Function Pattern:
WITH monthly AS (...)— CTE groups data by month. Makes the query readable and reusable.LAG(revenue) OVER (ORDER BY month)— Gets the previous month's revenue without a self-join.NULLIF(LAG(...), 0)— Avoids division by zero for the first month (NULL prev_month).ROUND(..., 1)— Rounds growth % to 1 decimal place for readability.WHERE status = 'delivered'— Only counts confirmed revenue, not pending orders.
This query demonstrates why CTEs are preferred: the WITH clause cleanly separates aggregation from analysis, making the logic transparent to anyone reading your code.
Q3: Top 10 Products by Revenue
Find the best-selling products by revenue, including customer review ratings:
──────────────────────────────────────────────────────────────────────
Power Bank 20000mAh | Electronics | 189 | 34670.16 | 4.9
Earbuds Pro | Electronics | 120 | 27981.60 | 3.8
USB-C Hub | Electronics | 87 | 27547.68 | 2.7
Mouse Wireless | Electronics | 56 | 20067.04 | 4.0
Denim Jacket | Clothing | 175 | 17277.75 | 3.4
Algorithms Illuminated| Books | 300 | 14121.00 | 4.3
...
Multi-Table Join Strategy:
JOIN order_items— Links products to sales data.LEFT JOIN reviews— Includes products even if they have no reviews yet.GROUP BY p.product_id, p.name, p.category— Must include all non-aggregated columns when using functions like SUM() and AVG().SUM(oi.quantity * oi.unit_price)— Calculates total revenue per product across all orders.LIMIT 10— Show only the top 10 by revenue.
Q4: Customer Segmentation (RFM-lite)
Segment customers using a simplified RFM (Recency, Frequency, Monetary) model with CASE statements for business logic and NTILE for quartiles:
──────────────────────────────────────────────────────────────────────
Lucas Taylor | Chicago | 5 | 4972.10 | Loyal | 1
Amelia Taylor | Seattle | 4 | 4964.11 | Loyal | 1
Priya Cruz | Miami | 5 | 4465.95 | Loyal | 1
Mason Wilson | Chicago | 5 | 4347.86 | Loyal | 1
...
Business Logic in SQL with CASE and NTILE:
MAX(o.order_date)— Most recent order (Recency).COUNT(o.order_id)— Number of orders (Frequency).SUM(o.total_amount)— Total spending (Monetary).COALESCE(..., 0)— Converts NULL to 0 for customers with no orders.CASE WHEN...THEN...ELSE— Applies your business rules: Champions spend >1000 and order >5 times; Loyal spend >500; etc.NTILE(4)— Divides customers into 4 quartiles by spending. Quartile 1 = top 25% spenders.
This is the kind of segmentation hiring managers want to see: you're not just querying data, you're implementing strategic business logic.
Q5: Seller Performance Dashboard
Rank sellers by revenue and show their percentage of total platform revenue:
──────────────────────────────────────────────────────────────────────
7 | SoundWave Audio | Portland | 4.25 | 59 | 15676.23 | 1 | 10.1
19 | FashionForward Co | Seattle | 4.74 | 40 | 14931.21 | 2 | 9.6
26 | ValueMart Deals | Portland | 2.90 | 28 | 13172.27 | 3 | 8.5
...
Window Function for Ranking and Percentages:
RANK() OVER (ORDER BY SUM(...) DESC)— Ranks sellers by revenue. Ties get the same rank, and the next rank skips (no gaps with RANK).SUM(revenue) OVER ()— Sum of all seller revenue (the denominator for percentage).revenue / SUM(revenue) OVER () * 100— Percentage of total platform revenue. This pattern is the gold standard for "share of total" calculations.
Q6: Category Revenue Mix Analysis
Show revenue contribution by product category:
──────────────────────────────────────────────────────────────────────
Electronics | 14 | 306 | 67554.59 | 43.3
Home & Kitchen | 12 | 219 | 27927.50 | 17.9
Clothing | 13 | 306 | 24166.68 | 15.5
Toys | 10 | 214 | 13870.17 | 8.9
Sports | 10 | 197 | 11978.42 | 7.7
Books | 12 | 276 | 10357.18 | 6.6
Window Function for "Share of Total":
SUM(SUM(...)) OVER ()— Nested aggregation: SUM the inner aggregation to get the total.- This pattern calculates each category's percentage of total revenue. Electronics drives 43.3% of all revenue.
Putting It All Together
You've just built a complete business intelligence report using only SQL:
- Q1: Executive Summary — High-level KPIs (customers, orders, revenue, AOV).
- Q2: Monthly Revenue Trend — Tracks growth over time with LAG and NULLIF.
- Q3: Top Products — Revenue drivers and customer sentiment (reviews).
- Q4: Customer Segmentation — RFM-based segments for targeted strategy.
- Q5: Seller Performance — Top sellers and their market share.
- Q6: Category Mix — Which product categories are most valuable.
This is the output hiring managers want to see in your portfolio: real business questions answered with clean, readable SQL. You're not just writing queries; you're telling stories with data.
Challenge: Add a 7th query — find the top 3 sellers per city by revenue using a CTE with ROW_NUMBER. Which sellers dominate in each market?
Test Your Knowledge
You just built a real analytics report.
Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer →