This mini project writes reporting queries against the schema you built in the previous tutorial. Assumes the shop tables (customers, orders, order_items, products, categories) with sample data. Adapt table names if you chose blog or booking.
1. Revenue by month
Total sales from paid and shipped orders, grouped by calendar month:
SELECT
DATE_FORMAT(o.placed_at, '%Y-%m') AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.id
WHERE o.status IN ('paid', 'shipped')
GROUP BY DATE_FORMAT(o.placed_at, '%Y-%m')
ORDER BY month;2. Top customers by spend
WITH line_totals AS (
SELECT order_id, SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
)
SELECT
c.id,
c.name,
c.email,
COUNT(o.id) AS order_count,
SUM(lt.order_total) AS total_spent
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
INNER JOIN line_totals lt ON lt.order_id = o.id
WHERE o.status IN ('paid', 'shipped')
GROUP BY c.id, c.name, c.email
ORDER BY total_spent DESC
LIMIT 10;3. Best-selling products
SELECT
p.id,
p.name,
cat.name AS category,
SUM(oi.quantity) AS units_sold,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
LEFT JOIN categories cat ON cat.id = p.category_id
INNER JOIN order_items oi ON oi.product_id = p.id
INNER JOIN orders o ON o.id = oi.order_id
WHERE o.status IN ('paid', 'shipped')
GROUP BY p.id, p.name, cat.name
ORDER BY units_sold DESC;4. Products never sold
SELECT p.id, p.name, p.stock
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.id IS NULL;5. Average order value
SELECT AVG(order_total) AS avg_order_value
FROM (
SELECT o.id, SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.id
WHERE o.status IN ('paid', 'shipped')
GROUP BY o.id
) AS totals;6. Orders pending over 7 days
SELECT o.id, c.name, c.email, o.placed_at
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
AND o.placed_at < NOW() - INTERVAL 7 DAY
ORDER BY o.placed_at;7. Category revenue share
SELECT
IFNULL(cat.name, 'Uncategorised') AS category,
SUM(oi.quantity * oi.unit_price) AS revenue,
ROUND(
100 * SUM(oi.quantity * oi.unit_price) /
SUM(SUM(oi.quantity * oi.unit_price)) OVER (),
1
) AS pct_of_total
FROM order_items oi
INNER JOIN orders o ON o.id = oi.order_id
INNER JOIN products p ON p.id = oi.product_id
LEFT JOIN categories cat ON cat.id = p.category_id
WHERE o.status IN ('paid', 'shipped')
GROUP BY cat.name
ORDER BY revenue DESC;Blog adaptations
If you built a blog, try equivalent questions:
- Posts published per month
- Authors ranked by post count and comment count
- Tags used most often (JOIN post_tags, GROUP BY tag)
- Published posts with zero comments (LEFT JOIN + IS NULL)
Booking adaptations
- Room utilisation – count of booked hours per room per week
- Customers with most completed appointments
- Upcoming appointments in the next 14 days
- Cancelled slots that freed capacity (filter status)
Polish pass
For each query you keep:
- Run EXPLAIN – any full table scans that should use an index?
- Save the best as views if you would run them weekly
- Check totals by hand on a small dataset – aggregation bugs hide easily
That finishes Going further with MySQL. You should be able to model a domain, normalise it, index for your queries, join and aggregate with confidence, protect data with constraints and transactions, and maintain schema and backups like something you would run in production. The advanced tutorials goes deeper on replication, locking, JSON columns, and deliberate performance work.