Aggregations and Grouping

-- Create the sales table with realistic e-commerce data
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10, 2),
    quantity INT,
    sale_date DATE,
    region VARCHAR(50),
    customer_id INT
);

-- Insert sample e-commerce data
INSERT INTO sales VALUES
(1, 'Laptop Pro 15"', 'Electronics', 1299.99, 2, '2024-01-15', 'North', 101),
(2, 'Wireless Mouse', 'Electronics', 29.99, 5, '2024-01-15', 'North', 102),
(3, 'Office Chair', 'Furniture', 199.99, 1, '2024-01-16', 'South', 103),
(4, 'USB-C Hub', 'Electronics', 49.99, 3, '2024-01-16', 'East', 104),
(5, 'Standing Desk', 'Furniture', 399.99, 1, '2024-01-17', 'West', 105),
(6, 'Laptop Pro 15"', 'Electronics', 1299.99, 1, '2024-01-17', 'South', 106),
(7, 'Desk Lamp', 'Furniture', 39.99, 4, '2024-01-18', 'North', 107),
(8, 'Webcam HD', 'Electronics', 79.99, 2, '2024-01-18', 'East', 108),
(9, 'Ergonomic Keyboard', 'Electronics', 89.99, 3, '2024-01-19', 'West', 109),
(10, 'Monitor Stand', 'Furniture', 34.99, 6, '2024-01-19', 'South', 110);

-- Calculate total revenue
SELECT 
    SUM(price * quantity) AS total_revenue
FROM sales;

-- Count total number of sales
SELECT COUNT(*) AS total_sales FROM sales;

-- Count unique customers
SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM sales;

-- Count sales by category
SELECT 
    category,
    COUNT(*) AS sales_count
FROM sales
GROUP BY category;

-- Calculate average sale value
SELECT 
    AVG(price * quantity) AS average_sale_value
FROM sales;

-- Average price by category
SELECT 
    category,
    AVG(price) AS average_price,
    MIN(price) AS lowest_price,
    MAX(price) AS highest_price
FROM sales
GROUP BY category;

-- Sales summary by category
SELECT 
    category,
    COUNT(*) AS total_sales,
    SUM(quantity) AS units_sold,
    SUM(price * quantity) AS revenue,
    AVG(price * quantity) AS avg_sale_value
FROM sales
GROUP BY category
ORDER BY revenue DESC;

-- Find categories with revenue over $1000
SELECT 
    category,
    SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY category
HAVING SUM(price * quantity) > 1000;

-- Find regions with more than 3 sales
SELECT 
    region,
    COUNT(*) AS sale_count,
    SUM(price * quantity) AS revenue
FROM sales
GROUP BY region
HAVING COUNT(*) > 2
ORDER BY revenue DESC;

-- Comprehensive sales analytics query
SELECT 
    category,
    region,
    COUNT(*) AS transactions,
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(quantity) AS units_sold,
    SUM(price * quantity) AS total_revenue,
    AVG(price * quantity) AS avg_transaction_value,
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM sales
GROUP BY category, region
HAVING SUM(price * quantity) > 100
ORDER BY total_revenue DESC;