Sales Analytics Dashboard Queries
What you'll build
A collection of SQL queries that power a sales analytics dashboard: total revenue, top products, sales by category, and best customers.
Goals
- Use GROUP BY to aggregate sales by product and category
- Apply HAVING to filter groups by aggregate values
- Compute revenue, averages, and rankings
- Combine JOINs with aggregations for rich reports
Prerequisites
- SELECT and WHERE basics
- JOIN fundamentals
- Aggregate function concepts
Steps
- 1
Create the schema
Create products and sales tables. Products have a category; sales record each purchase with a quantity and price.
CodeCREATE TABLE products ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL ); CREATE TABLE sales ( id SERIAL PRIMARY KEY, product_id INTEGER REFERENCES products(id), quantity INTEGER NOT NULL, price NUMERIC(10,2) NOT NULL, sold_at TIMESTAMP DEFAULT NOW() );Expected result
Two tables are created with a foreign key relationship.
- 2
Insert sample data
Insert products in different categories and several sales records.
CodeINSERT INTO products (name, category) VALUES ('Widget', 'Tools'), ('Gadget', 'Tools'), ('Book', 'Media'), ('Pen', 'Office'); INSERT INTO sales (product_id, quantity, price) VALUES (1, 5, 9.99), (1, 3, 9.99), (2, 2, 19.99), (3, 10, 12.50), (4, 8, 1.99), (4, 4, 1.99);Expected result
Four products and six sales records are inserted.
- 3
Revenue by category
Write a query that computes total revenue per product category using GROUP BY and SUM.
CodeSELECT p.category, COUNT(*) AS num_sales, SUM(s.quantity * s.price) AS revenue FROM sales s INNER JOIN products p ON s.product_id = p.id GROUP BY p.category ORDER BY revenue DESC;Expected result
A summary showing each category's sales count and total revenue.
- 4
Top products with HAVING
Find products that have sold more than 5 total units, using HAVING to filter after aggregation.
CodeSELECT p.name, SUM(s.quantity) AS total_units, SUM(s.quantity * s.price) AS revenue FROM sales s INNER JOIN products p ON s.product_id = p.id GROUP BY p.name HAVING SUM(s.quantity) > 5 ORDER BY revenue DESC;Expected result
Only products with more than 5 units sold are returned.
- 5
Add indexes to optimize queries
Create indexes on frequently queried columns to improve query performance. Indexes are the primary means of database performance optimization, but be mindful of write overhead.
Code-- Create indexes on frequently filtered columns CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_orders_created_at ON orders(created_at DESC); -- Verify the index is used EXPLAIN SELECT * FROM orders WHERE user_id = 42;Expected result
EXPLAIN output shows an index scan using idx_orders_user_id instead of a full table scan.
Try it yourself
What you learned
You built real analytics queries combining JOINs, GROUP BY, HAVING, and multiple aggregate functions — the same patterns used in business intelligence dashboards.
Related Resources
Related Lessons
- GROUP BY
Learn grouping and aggregation.
- Aggregations
Learn aggregate functions.
Related Glossary
- Aggregation
Summarizing data.
- GROUP BY
Grouping rows.
- HAVING
Filtering groups.
Related Glossary
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
- Aggregation
Summarizing data.
- GROUP BY
Grouping rows.
- HAVING
Filtering groups.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.