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.

sql
total_customers | total_orders | total_revenue | avg_order_value | 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:

sql
month | revenue | orders | prev_month | mom_growth_pct
──────────────────────────────────────────────────────────────────────
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:

sql
name | category | units_sold | revenue | avg_review_rating
──────────────────────────────────────────────────────────────────────
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:

sql
name | city | frequency | monetary | segment | spend_quartile
──────────────────────────────────────────────────────────────────────
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:

sql
seller_id | company_name | city | seller_rating | orders_fulfilled | revenue | revenue_rank | pct_of_total_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:

sql
category | products | units_sold | revenue | revenue_share_pct
──────────────────────────────────────────────────────────────────────
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

Question 1 of 5
What SQL pattern gives you month-over-month growth?
Question 2 of 5
In the RFM model, what does 'F' stand for?
Question 3 of 5
How do you calculate percentage of total in SQL?
Question 4 of 5
Which window function assigns ranks with no gaps?
Question 5 of 5
Why use a CTE instead of a subquery for a report?
0
/5
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

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 →