Project 2: Sales Analytics and Reporting

What you're building#

A reporting database that answers business questions over a large order history, fast. You'll practice analytical SQL, then precompute the expensive parts with a scheduled event.

Requirements#

1. Start from the shop schema from Project 1, loaded with at least 500,000 order items.
2. Produce a set of analytical queries using CTEs and window functions.
3. Create summary tables refreshed by a MySQL event.
4. Meet a performance target: every dashboard query under 200 ms.
5. Document each query's purpose and its EXPLAIN result.

Step 1: Generate data#

Use a recursive CTE or a script to create realistic volume: customers who join over two years, seasonal order spikes, a long tail of products with a few best sellers. Raise cte_max_recursion_depth if needed. Realistic skew matters, since uniform random data hides index problems.

Step 2: Core analytics#

Write each of these as a saved .sql file with a comment explaining the business question:

Revenue trend. Monthly revenue, with percentage change versus the previous month using LAG.

Product ranking. Top 3 products per category by revenue using ROW_NUMBER() OVER (PARTITION BY category ...).

Customer segments. Using NTILE(4) over lifetime spend, label customers as bronze, silver, gold, platinum.

Repeat purchase rate. Share of customers with at least two orders, by signup month.

Cohort retention. For each signup month, how many customers ordered in month 0, 1, 2, and 3 after signup. Use a CTE for each customer's first order month and conditional aggregation:

WITH first_order AS (
  SELECT customer_id, DATE_FORMAT(MIN(ordered_at), '%Y-%m-01') AS cohort
  FROM orders GROUP BY customer_id
)
SELECT f.cohort,
       COUNT(DISTINCT f.customer_id) AS size,
       COUNT(DISTINCT CASE WHEN TIMESTAMPDIFF(MONTH, f.cohort, o.ordered_at) = 1
                           THEN o.customer_id END) AS month_1
FROM first_order f
JOIN orders o ON o.customer_id = f.customer_id
GROUP BY f.cohort
ORDER BY f.cohort;

Extend it to months 2 and 3, then to percentages.

Step 3: Measure and index#

Run EXPLAIN ANALYZE on every query. Identify scans and filesorts. Add composite indexes, and check for covering index opportunities on the hottest query. Record each change and its effect on time in NOTES.md.

Step 4: Summary tables#

Some queries will still be too slow to run live. Precompute them:

CREATE TABLE daily_sales (
  sales_date DATE NOT NULL,
  category   VARCHAR(40) NOT NULL,
  orders     INT NOT NULL,
  revenue    DECIMAL(14,2) NOT NULL,
  PRIMARY KEY (sales_date, category)
);

CREATE EVENT ev_refresh_daily_sales
ON SCHEDULE EVERY 1 HOUR
DO
  REPLACE INTO daily_sales
  SELECT DATE(o.ordered_at), p.category, COUNT(DISTINCT o.id),
         SUM(oi.qty * oi.unit_price)
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  WHERE o.status <> 'cancelled'
    AND o.ordered_at >= CURDATE() - INTERVAL 2 DAY
  GROUP BY DATE(o.ordered_at), p.category;

Refreshing only recent days keeps the job cheap. Compare the dashboard query's time against the raw tables versus the summary.

Step 5: Access control#

Create a read-only analyst account limited to the summary tables and views. Confirm it can't read customer emails.

Acceptance checklist#

• Every analytical query is saved with its business question and verified against a small hand-checked sample.
• Dashboard queries run in under 200 ms, with evidence from EXPLAIN ANALYZE.
• The event runs and the summary matches a direct query for the same period.
• The analyst account sees aggregates only.

Common pitfalls#

• Joining items to orders and then summing order-level fields, which multiplies them.
• Using RANK where ROW_NUMBER is needed for a strict top N.
• Summary tables that drift from the source because of late-arriving orders.

Practice#

Add a week_over_week view over daily_sales, and write a check query that compares the summary with live data and returns any days that differ.