Practice and reinforce the concepts from Lesson 5
Time needed: 45-60 minutes
What you'll build: A complete analytics dashboard for "TechMart", an e-commerce store! You'll use SQL aggregation functions to analyze sales data, identify trends, and create reports that real businesses use every day. By the end, you'll have built queries that answer critical business questions like "What's our best-selling product?" and "Which regions generate the most revenue?" 💰📈
✅ Make sure you have access to a SQL environment (like SQLiteOnline, DB Fiddle, or your local database)
✅ Have a text editor ready to save your queries
✅ Get ready to become a data analyst!
You've just been hired as a Data Analyst at TechMart! The CEO needs insights into the business performance, and you'll build the analytics to help make data-driven decisions.
⏱️ Time: 5 minutes
Copy and run this SQL to set up your store's database:
-- Create the TechMart sales database
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
unit_price DECIMAL(10, 2),
supplier_id INT
);
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
product_id INT,
quantity INT,
sale_date DATE,
region VARCHAR(50),
customer_id INT,
discount_percent INT DEFAULT 0
);
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
customer_type VARCHAR(20),
join_date DATE
);
-- Insert product data
INSERT INTO products VALUES
(1, 'Gaming Laptop Pro', 'Electronics', 1499.99, 101),
(2, 'Wireless Mouse', 'Electronics', 29.99, 102),
(3, 'Office Chair Deluxe', 'Furniture', 299.99, 103),
(4, 'USB-C Hub', 'Electronics', 49.99, 102),
(5, 'Standing Desk', 'Furniture', 599.99, 103),
(6, '4K Webcam', 'Electronics', 129.99, 101),
(7, 'Ergonomic Keyboard', 'Electronics', 89.99, 102),
(8, 'Monitor Stand', 'Furniture', 39.99, 103),
(9, 'Noise-Canceling Headphones', 'Electronics', 249.99, 101),
(10, 'Desk Lamp LED', 'Furniture', 59.99, 103);
-- Insert customer data
INSERT INTO customers VALUES
(1, 'Tech Startup Inc', 'Business', '2023-01-15'),
(2, 'John Smith', 'Individual', '2023-03-20'),
(3, 'Digital Agency Co', 'Business', '2023-02-10'),
(4, 'Sarah Johnson', 'Individual', '2023-04-05'),
(5, 'Innovation Labs', 'Business', '2023-01-30');
-- Insert sales data (January 2024)
INSERT INTO sales VALUES
(1, 1, 2, '2024-01-15', 'North', 1, 10),
(2, 2, 5, '2024-01-15', 'North', 2, 0),
(3, 3, 1, '2024-01-16', 'South', 3, 5),
(4, 4, 3, '2024-01-16', 'East', 4, 0),
(5, 5, 1, '2024-01-17', 'West', 5, 15),
(6, 1, 1, '2024-01-17', 'South', 2, 0),
(7, 7, 4, '2024-01-18', 'North', 1, 10),
(8, 6, 2, '2024-01-18', 'East', 3, 5),
(9, 9, 3, '2024-01-19', 'West', 4, 0),
(10, 8, 6, '2024-01-19', 'South', 5, 20),
(11, 2, 10, '2024-01-20', 'North', 1, 15),
(12, 4, 8, '2024-01-20', 'East', 2, 10),
(13, 9, 1, '2024-01-21', 'South', 3, 0),
(14, 3, 2, '2024-01-21', 'West', 4, 5),
(15, 7, 5, '2024-01-22', 'North', 5, 10);
💡 Tip Save this setup code! You might need to recreate the database if you're working across multiple sessions.
⏱️ Time: 10 minutes
The CEO wants to know the total revenue. But wait - we need to consider discounts!
Your Task:
-- Hint: Revenue = (unit_price * quantity)
-- Discounted Revenue = (unit_price * quantity) * (1 - discount_percent/100)
-- Try writing these queries yourself before looking at the solution!
⏱️ Time: 10 minutes
Your Task: Create a report showing:
Bonus: Which customer type (Business vs Individual) generates more transactions?
⏱️ Time: 15 minutes
The marketing team needs to know which product categories perform best.
Your Task: Create a comprehensive category report showing:
Extra Challenge: Sort the results to show the best-performing category first!
-- Start with joining all three tables
-- Group by category
-- Use multiple aggregate functions
⏱️ Time: 10 minutes
Your Task: The sales team wants to know:
💡 Tip Use HAVING to filter aggregated results! It's like WHERE but for grouped data.
⏱️ Time: 15 minutes
Your Task: Create a "Top Products" report that shows:
⏱️ Time: 10 minutes
Your Task: Analyze customer behavior:
⏱️ Time: 15 minutes
Create ONE query that provides a complete overview for the CEO showing:
The report should:
-- This is a complex query combining:
-- Multiple JOINs
-- GROUP BY with multiple columns
-- Multiple aggregate functions
-- HAVING clause
-- ORDER BY
-- Challenge 1: Total Revenue Calculation
-- Query 1: Revenue without discounts
SELECT ...
-- Query 2: Revenue with discounts
SELECT ...
-- Challenge 2: Sales Volume Analysis
-- etc...
Your submission will be evaluated on:
💡 Tip Remember these key concepts:
If you finish early, try these:
ℹ️ Stuck on a challenge?
- Review the lesson materials on aggregation functions
- Try breaking complex queries into smaller parts
- Test each part separately before combining
- Remember: There's often more than one correct solution!
Good luck, Data Analyst! Show TechMart the power of data-driven insights! 🚀📊