type
status
date
slug
summary
category
tags
password
icon
Medium Tages
Medium Subtitle
Build Your First Sales Dashboard: SQL Aggregation for Beginners
You've learned SQL basics—SELECT, WHERE, ORDER BY. Now what?
Here's the thing most tutorials miss: you need a project that looks good on your portfolio. Something you can show a hiring manager and say "I built this."
Today, we're building a complete sales dashboard from scratch. Not just random queries—a real business analytics project with revenue tracking, customer insights, and product performance metrics.
By the end of this tutorial, you'll have queries that answer actual business questions and understand exactly how companies analyze their data.
What you'll build: A 5-part sales dashboard with revenue analysis, customer segmentation, product performance, time-based trends, and growth metrics.
What We're Building
The Sales Dashboard Reports
Our dashboard will answer these 5 critical business questions:
- Revenue Overview — Total sales, average order value, order count
- Top Customers — Who are our biggest spenders?
- Product Performance — Which products drive the most revenue?
- Monthly Trends — How are sales changing over time?
- Customer Segments — Who are our VIP customers vs. occasional buyers?
The Dataset
We're using a simple e-commerce database with three tables:
- orders — order_id, customer_id, order_date, amount, status
- customers — customer_id, customer_name, country, city
- order_items — item_id, order_id, product_id, quantity, unit_price
Why this matters: These are the same patterns used in real business intelligence dashboards at companies like Shopify, Amazon, and every e-commerce startup.
All the data output results are hypothetical.
Report 1: Revenue Overview
The Business Question
"What's our total revenue, how many orders have we processed, and what's our average order value?"
This is the first thing any executive wants to know.
The SQL Query
Breaking It Down
COUNT(*) counts every completed order. This is your volume metric—how many transactions you're processing.
SUM(amount) adds up all order amounts to get total revenue. This is the number that matters most to any business.
AVG(amount) calculates the average order value, a key metric for pricing strategy. If you know your average is $126, you can test whether offering a $150 bundle increases conversions.
MIN and MAX show the range of order sizes. If your smallest order is $12 and your largest is $1,450, you've got customers across the entire spectrum.
Why WHERE matters: We filter for completed orders only—cancelled and pending orders don't count as revenue. We also focus on this year's data to keep insights relevant.
The output:
Business insight: Average order value of $126 means you can experiment with upselling to push that closer to $150. Even a 10% increase in AOV means an extra $15,000 in revenue at your current volume.
Report 2: Top Customers
The Business Question
"Who are our top 10 customers by total spending?"
Identifying your VIP customers helps you focus retention efforts where they matter most. These are the people you never want to lose.
The SQL Query
Key Concepts
JOIN connects the customers table with the orders table. We need customer names from one table and order data from another.
GROUP BY is where the magic happens. It collapses multiple orders per customer into one row with aggregated stats.
Think of GROUP BY as creating "buckets" for each customer. All of Sarah Johnson's orders get thrown into her bucket, then we calculate stats on that bucket: how many orders, total spending, average order value.
Without GROUP BY: 1,247 rows (one per order)
With GROUP BY: 312 rows (one per customer)
HAVING filters groups after aggregation. We only want customers with 3+ orders—these are your repeat customers, not one-time buyers.
ORDER BY + LIMIT sorts by total spending and grabs the top 10.
The output:
Business insight: Your top 10 customers represent 15% of total revenue. A small loyalty program targeting just these 10 people could have massive ROI. Even keeping them engaged with quarterly check-ins or exclusive early access could be worth thousands.
Report 3: Product Performance
The Business Question
"Which products are driving the most revenue, and how many units are we selling?"
Product performance analysis tells you where to focus inventory, marketing, and development efforts.
The SQL Query
What's Different Here
COUNT(DISTINCT order_id) counts how many unique orders included this product. A product could appear in 145 orders but sell 187 units (because some orders bought 2 or 3).
Calculated aggregation —
SUM(quantity * unit_price) calculates revenue on the fly. We're not storing revenue in the table; we're computing it from quantity and price.HAVING with calculation filters to only show products that drove $5,000+ in revenue. This keeps your report focused on products that actually matter to the bottom line.
The output:
Business insight: Wireless headphones have fewer orders than laptop stands but higher total revenue. That means higher price point and better margins. Your premium positioning is working—lean into it with more high-end accessories.
Report 4: Monthly Sales Trends
The Business Question
"How are our sales trending month-over-month this year?"
Time-based analysis reveals seasonality, growth patterns, and helps with forecasting. If you see a spike every February, you can plan for it next year.
The SQL Query
Date Functions in Aggregation
EXTRACT(MONTH FROM order_date) pulls out just the month number (1-12) from a full date. January 15th and January 28th both become "1".
Grouping by extracted fields means all January orders get rolled up together, all February orders together, etc.
This is powerful because it lets you analyze patterns across time periods without manually creating date range filters.
The output:
Business insight: February had a 14% jump in orders and 18% jump in revenue. That spike lines up with Valentine's Day. Next year, plan inventory accordingly and run Valentine's campaigns in early February.
Report 5: Customer Segmentation
The Business Question
"How can we segment our customers by purchase behavior?"
Not all customers are equal. VIPs need white-glove service. One-time buyers need re-engagement campaigns. Let's categorize them.
The SQL Query
Advanced Concepts
Subquery (the inner SELECT) calculates each customer's lifetime spending first. The outer query then groups those customers into segments.
CASE statement creates custom categories based on spending levels. This is how you turn raw numbers into business segments.
Percentage calculation uses a nested subquery to show what portion of total revenue comes from each segment. This is the Pareto principle in action.
The output:
Business insight: 9% of your customers drive 50% of revenue. This is classic Pareto—a small number of customers are disproportionately valuable. Your retention strategy should focus here first. If you lose a VIP, it takes 80 occasional buyers to replace them.
Conclusion
Congratulations! You just built a complete sales dashboard using SQL aggregation.
What you learned:
The five aggregate functions — COUNT, SUM, AVG, MIN, MAX — are the foundation of business analytics. Every dashboard, every report, every executive summary uses these.
GROUP BY is how you roll up data by categories. Want stats per customer? GROUP BY customer. Per product? GROUP BY product. Per month? GROUP BY month.
HAVING filters aggregated results. WHERE filters before grouping; HAVING filters after. Need customers who spent $1,000+? Use HAVING.
JOIN with aggregation lets you combine data from multiple tables. Real analysis almost always involves multiple tables.
Date functions turn timestamps into time periods. EXTRACT(MONTH) is just the beginning—you can group by quarter, by day of week, by year.
Subqueries and CASE enable advanced segmentation. These are the tools that turn raw data into business intelligence.
Next steps:
Add this to your portfolio. Screenshot your queries and results. Write up your business insights. This is exactly what hiring managers want to see—not just SQL skills, but business thinking.
Extend the dashboard. Try adding product category analysis, geographic breakdowns, or year-over-year growth rates. Each new report you add makes your portfolio stronger.
Learn visualization. These queries are powerful, but connecting them to Tableau, Power BI, or Python libraries turns them into actual dashboards. Visual charts make insights obvious to non-technical stakeholders.
Practice with real data. Download e-commerce datasets from Kaggle or use your company's data (with permission). The more you practice, the faster these patterns become second nature.
The career impact: Data analysts spend 80% of their time writing queries exactly like these. You're not just learning SQL—you're building the exact skills that companies pay $70K-$120K for.
Now go build your dashboard.
上一篇
3 SQL Aggregation Patterns That Will Make Your Boss Think You're a Data Wizard
下一篇
WHERE vs HAVING: The SQL Mistake That Breaks Your Queries
Loading...
.jpg?table=block&id=29b4acfb-1cac-80cb-97e9-d6504a23ba23&t=29b4acfb-1cac-80cb-97e9-d6504a23ba23)
