Mini Project: Sales Dashboard on payments
Build a complete sales dashboard by querying the ecommerce_db using every aggregate technique from this module. You'll generate revenue summaries, customer analytics, payment breakdowns, and trend analysis.
Dashboard Components
- Overall revenue KPIs (total, average, min, max)
- Revenue by customer with order counts
- Payment method breakdown (pivot)
- Top customers by spend
- Order status distribution
Sales Dashboard Queries
-- 1. Overall KPIs
SELECT
COUNT(DISTINCT o.id) AS total_orders,
COUNT(DISTINCT o.user_id) AS active_customers,
ROUND(SUM(p.amount), 2) AS total_revenue,
ROUND(AVG(o.total_amount), 2) AS avg_order_value,
ROUND(MAX(o.total_amount), 2) AS largest_order
FROM orders o
LEFT JOIN payments p ON o.id = p.order_id;
-- 2. Revenue by customer
SELECT
u.name AS customer,
COUNT(o.id) AS num_orders,
ROUND(SUM(o.total_amount), 2) AS lifetime_value,
ROUND(AVG(o.total_amount), 2) AS avg_order
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY lifetime_value DESC;
-- 3. Payment method breakdown
SELECT
COALESCE(payment_method, 'Unpaid') AS method,
COUNT(*) AS transactions,
ROUND(SUM(amount), 2) AS revenue,
ROUND(AVG(amount), 2) AS avg_amount
FROM payments
GROUP BY payment_method
ORDER BY revenue DESC;
-- 4. Order status distribution
SELECT
status,
COUNT(*) AS count,
ROUND(SUM(total_amount), 2) AS revenue,
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct
FROM orders
GROUP BY status
ORDER BY count DESC;
-- 5. High-value customers (> avg spend)
SELECT u.name, ROUND(SUM(o.total_amount), 2) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING SUM(o.total_amount) > (SELECT AVG(total_amount) FROM orders);Quick Quiz
Tip
Tip
Practice Mini Project Sales Dashboard on payments in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Inner query runs first, results used by outer query. CTEs are often more readable.
Common Mistake
Warning
A common mistake with Mini Project Sales Dashboard on payments is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Mini Project Sales Dashboard on payments from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- Build a complete sales dashboard by querying the ecommerce_db using every aggregate technique from this module.
- Overall revenue KPIs (total, average, min, max)
- Revenue by customer with order counts
- Payment method breakdown (pivot)