Activity 5: Sales Data Aggregation

-- Simple sales table
CREATE TABLE sales (
    id INT PRIMARY KEY,
    product_name VARCHAR(50),
    category VARCHAR(30),
    quantity INT,
    price DECIMAL(8,2),
    sale_date DATE
);

-- Sample data (10 sales records)
INSERT INTO sales VALUES
(1, 'iPhone 14', 'Electronics', 2, 999.00, '2024-01-15'),
(2, 'Coffee Mug', 'Home', 5, 12.99, '2024-01-16'),
(3, 'Laptop Pro', 'Electronics', 1, 1899.00, '2024-01-17'),
(4, 'Desk Chair', 'Home', 3, 149.99, '2024-01-18'),
(5, 'Headphones', 'Electronics', 4, 79.99, '2024-01-19'),
(6, 'Table Lamp', 'Home', 2, 34.99, '2024-01-20'),
(7, 'Tablet', 'Electronics', 1, 599.00, '2024-01-21'),
(8, 'Bookshelf', 'Home', 1, 89.99, '2024-01-22'),
(9, 'Smart Watch', 'Electronics', 3, 299.00, '2024-01-23'),
(10, 'Throw Pillow', 'Home', 6, 19.99, '2024-01-24');

SELECT column, AGGREGATE_FUNCTION(column)
FROM table
GROUP BY column;

-- Sales by category
SELECT category, COUNT(*) as num_sales
FROM sales
GROUP BY category;

-- Total revenue by category
SELECT category, 
       COUNT(*) as num_sales,
       SUM(quantity * price) as total_revenue,
       AVG(quantity * price) as avg_sale_amount
FROM sales
GROUP BY category
ORDER BY total_revenue DESC;

-- Categories with more than $500 total revenue
SELECT category, 
       SUM(quantity * price) as total_revenue
FROM sales
GROUP BY category
HAVING SUM(quantity * price) > 500
ORDER BY total_revenue DESC;

Electronics | 5 | 4673.95 | 934.79
Home        | 5 |  659.89 | 131.98