Practical Reporting Queries: A Mini-Project

-- Add a ratings table
CREATE TABLE reviews (
    id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    review_date DATE NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Insert reviews
INSERT INTO reviews VALUES (1, 1, 1, 5, '2024-01-20');
INSERT INTO reviews VALUES (2, 1, 2, 4, '2024-01-25');
INSERT INTO reviews VALUES (3, 1, 3, 5, '2024-02-10');
INSERT INTO reviews VALUES (4, 2, 1, 5, '2024-01-22');
INSERT INTO reviews VALUES (5, 2, 4, 4, '2024-02-15');
INSERT INTO reviews VALUES (6, 2, 5, 3, '2024-03-01');
INSERT INTO reviews VALUES (7, 3, 2, 4, '2024-02-05');
INSERT INTO reviews VALUES (8, 3, 6, 5, '2024-03-12');
INSERT INTO reviews VALUES (9, 4, 1, 5, '2024-01-30');
INSERT INTO reviews VALUES (10, 4, 3, 4, '2024-02-20');
INSERT INTO reviews VALUES (11, 5, 4, 3, '2024-03-05');
INSERT INTO reviews VALUES (12, 6, 3, 5, '2024-03-10');
INSERT INTO reviews VALUES (13, 6, 1, 4, '2024-03-18');
INSERT INTO reviews VALUES (14, 7, 5, 5, '2024-02-28');
INSERT INTO reviews VALUES (15, 7, 2, 4, '2024-03-15');
INSERT INTO reviews VALUES (16, 8, 4, 5, '2024-03-08');
INSERT INTO reviews VALUES (17, 8, 6, 4, '2024-03-22');
INSERT INTO reviews VALUES (18, 8, 1, 5, '2024-04-01');
INSERT INTO reviews VALUES (19, 10, 2, 4, '2024-02-12');
INSERT INTO reviews VALUES (20, 10, 5, 5, '2024-03-20');
INSERT INTO reviews VALUES (21, 10, 3, 4, '2024-04-05');

-- Add more orders for richer reporting data
INSERT INTO orders VALUES (9, 1, '2024-04-15', 89.99, 'completed');
INSERT INTO orders VALUES (10, 3, '2024-04-18', 63.99, 'completed');
INSERT INTO orders VALUES (11, 2, '2024-04-22', 174.99, 'shipped');
INSERT INTO orders VALUES (12, 4, '2024-04-25', 45.00, 'pending');

INSERT INTO order_items VALUES (14, 9, 2, 1, 89.99);
INSERT INTO order_items VALUES (15, 10, 7, 1, 38.00);
INSERT INTO order_items VALUES (16, 10, 10, 1, 24.99);
INSERT INTO order_items VALUES (17, 11, 2, 1, 89.99);
INSERT INTO order_items VALUES (18, 11, 8, 1, 110.00);
INSERT INTO order_items VALUES (19, 12, 3, 1, 45.00);

SELECT 'reviews' AS table_name, COUNT(*) AS rows FROM reviews
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'order_items', COUNT(*) FROM order_items;

SELECT id, order_date, total_amount, status
FROM orders
ORDER BY order_date;

-- PostgreSQL syntax
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
       COUNT(*) AS total_orders,
       SUM(total_amount) AS revenue
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;

SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
       COUNT(*) AS total_orders,
       ROUND(SUM(total_amount), 2) AS revenue,
       ROUND(AVG(total_amount), 2) AS avg_order_value,
       MIN(total_amount) AS smallest_order,
       MAX(total_amount) AS largest_order
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;

SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
       status,
       COUNT(*) AS orders,
       ROUND(SUM(total_amount), 2) AS revenue
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM'), status
ORDER BY month, status;

SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
       COUNT(*) AS confirmed_orders,
       ROUND(SUM(total_amount), 2) AS confirmed_revenue,
       ROUND(AVG(total_amount), 2) AS avg_confirmed_order
FROM orders
WHERE status IN ('completed', 'shipped')
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

SELECT c.first_name || ' ' || c.last_name AS customer,
       c.email,
       c.city,
       COUNT(o.id) AS total_orders,
       ROUND(SUM(o.total_amount), 2) AS total_spent,
       ROUND(AVG(o.total_amount), 2) AS avg_order_value,
       MIN(o.order_date) AS first_order,
       MAX(o.order_date) AS last_order
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name, c.email, c.city
ORDER BY total_spent DESC
LIMIT 5;

SELECT c.first_name || ' ' || c.last_name AS customer,
       COUNT(o.id) AS total_orders,
       ROUND(SUM(o.total_amount), 2) AS total_spent,
       ROUND(AVG(o.total_amount), 2) AS avg_order_value,
       CASE
           WHEN SUM(o.total_amount) >= 300 THEN 'Platinum'
           WHEN SUM(o.total_amount) >= 200 THEN 'Gold'
           WHEN SUM(o.total_amount) >= 100 THEN 'Silver'
           ELSE 'Bronze'
       END AS customer_tier
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY total_spent DESC;

SELECT c.first_name || ' ' || c.last_name AS customer,
       c.city,
       COUNT(o.id) AS total_orders,
       COALESCE(ROUND(SUM(o.total_amount), 2), 0) AS total_spent,
       CASE
           WHEN SUM(o.total_amount) >= 300 THEN 'Platinum'
           WHEN SUM(o.total_amount) >= 200 THEN 'Gold'
           WHEN SUM(o.total_amount) >= 100 THEN 'Silver'
           WHEN SUM(o.total_amount) > 0 THEN 'Bronze'
           ELSE 'Inactive'
       END AS customer_tier
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name, c.city
ORDER BY total_spent DESC;

SELECT r.product_id, p.name, r.rating, r.review_date
FROM reviews r
JOIN products p ON r.product_id = p.id
ORDER BY p.name, r.review_date
LIMIT 10;

SELECT p.name AS product,
       COUNT(r.id) AS review_count,
       ROUND(AVG(r.rating), 2) AS avg_rating,
       MIN(r.rating) AS lowest_rating,
       MAX(r.rating) AS highest_rating
FROM products p
JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name
ORDER BY avg_rating DESC, review_count DESC;

SELECT c.name AS category,
       COUNT(DISTINCT p.id) AS products_reviewed,
       COUNT(r.id) AS total_reviews,
       ROUND(AVG(r.rating), 2) AS avg_rating,
       MIN(r.rating) AS lowest_rating,
       MAX(r.rating) AS highest_rating
FROM categories c
JOIN products p ON c.id = p.category_id
JOIN reviews r ON p.id = r.product_id
GROUP BY c.id, c.name
ORDER BY avg_rating DESC;

SELECT c.name AS category,
       COUNT(DISTINCT p.id) AS total_products,
       ROUND(AVG(p.price), 2) AS avg_price,
       SUM(p.stock_quantity) AS total_stock,
       COUNT(r.id) AS total_reviews,
       ROUND(AVG(r.rating), 2) AS avg_rating,
       SUM(CASE WHEN r.rating >= 4 THEN 1 ELSE 0 END) AS positive_reviews,
       ROUND(
           SUM(CASE WHEN r.rating >= 4 THEN 1 ELSE 0 END) * 100.0 / COUNT(r.id),
           1
       ) AS positive_pct
FROM categories c
JOIN products p ON c.id = p.category_id
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY c.id, c.name
ORDER BY avg_rating DESC;

SELECT c.name AS category,
       COUNT(p.id) AS product_count
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.name
ORDER BY product_count DESC;

SELECT c.name AS category,
       COUNT(p.id) AS product_count,
       ROUND(AVG(p.price), 2) AS avg_price,
       SUM(p.stock_quantity) AS total_stock
FROM categories c
JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.name
HAVING COUNT(p.id) > 2
ORDER BY product_count DESC;

SELECT c.name AS category,
       COUNT(p.id) AS product_count,
       COUNT(CASE WHEN p.is_available = true THEN 1 END) AS available_count,
       COUNT(CASE WHEN p.is_available = false THEN 1 END) AS unavailable_count,
       ROUND(AVG(p.price), 2) AS avg_price,
       SUM(p.stock_quantity) AS total_stock,
       ROUND(SUM(p.price * p.stock_quantity), 2) AS inventory_value,
       CASE
           WHEN COUNT(p.id) >= 4 THEN 'Well Stocked'
           WHEN COUNT(p.id) >= 2 THEN 'Adequate'
           ELSE 'Needs Expansion'
       END AS catalog_status
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.name
ORDER BY product_count DESC;

SELECT p.name AS product,
       c.name AS category,
       p.price,
       p.stock_quantity AS stock,
       p.is_available AS available,
       COALESCE(order_data.times_ordered, 0) AS times_ordered,
       COALESCE(order_data.total_units_sold, 0) AS units_sold,
       COALESCE(order_data.total_revenue, 0) AS revenue,
       COALESCE(review_data.review_count, 0) AS reviews,
       COALESCE(review_data.avg_rating, 0) AS avg_rating,
       CASE
           WHEN COALESCE(order_data.total_revenue, 0) = 0 THEN 'No Sales'
           WHEN order_data.total_revenue >= 150 THEN 'Top Performer'
           WHEN order_data.total_revenue >= 50 THEN 'Solid'
           ELSE 'Underperforming'
       END AS performance_tier
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN (
    SELECT product_id,
           COUNT(*) AS times_ordered,
           SUM(quantity) AS total_units_sold,
           ROUND(SUM(quantity * unit_price), 2) AS total_revenue
    FROM order_items
    GROUP BY product_id
) AS order_data ON p.id = order_data.product_id
LEFT JOIN (
    SELECT product_id,
           COUNT(*) AS review_count,
           ROUND(AVG(rating), 2) AS avg_rating
    FROM reviews
    GROUP BY product_id
) AS review_data ON p.id = review_data.product_id
ORDER BY revenue DESC;

SELECT
    -- Order metrics
    (SELECT COUNT(*) FROM orders) AS total_orders,
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') AS completed_orders,
    (SELECT ROUND(SUM(total_amount), 2) FROM orders) AS gross_revenue,
    (SELECT ROUND(SUM(total_amount), 2) FROM orders
     WHERE status IN ('completed', 'shipped')) AS confirmed_revenue,
    (SELECT ROUND(AVG(total_amount), 2) FROM orders) AS avg_order_value,
    -- Customer metrics
    (SELECT COUNT(*) FROM customers) AS total_customers,
    (SELECT COUNT(DISTINCT customer_id) FROM orders) AS active_customers,
    -- Product metrics
    (SELECT COUNT(*) FROM products) AS total_products,
    (SELECT COUNT(*) FROM products WHERE is_available = true) AS available_products,
    (SELECT COUNT(*) FROM products WHERE stock_quantity = 0) AS out_of_stock,
    -- Review metrics
    (SELECT COUNT(*) FROM reviews) AS total_reviews,
    (SELECT ROUND(AVG(rating), 2) FROM reviews) AS overall_rating;

-- PostgreSQL syntax using LAG window function
SELECT month,
       total_orders,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
       CASE
           WHEN LAG(revenue) OVER (ORDER BY month) IS NULL THEN 'N/A'
           WHEN revenue > LAG(revenue) OVER (ORDER BY month) THEN 'Up'
           WHEN revenue < LAG(revenue) OVER (ORDER BY month) THEN 'Down'
           ELSE 'Flat'
       END AS trend
FROM (
    SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
           COUNT(*) AS total_orders,
           ROUND(SUM(total_amount), 2) AS revenue
    FROM orders
    GROUP BY TO_CHAR(order_date, 'YYYY-MM')
) AS monthly_data
ORDER BY month;

-- Step 1: Basic data retrieval
SELECT * FROM orders LIMIT 5;

-- Step 2: Add grouping
SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;

-- Step 3: Add aggregates
SELECT customer_id, COUNT(*), SUM(total_amount) FROM orders GROUP BY customer_id;

-- Step 4: Add joins
SELECT c.first_name, COUNT(o.id), SUM(o.total_amount)
FROM customers c JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name;

-- Step 5: Add filters, aliases, sorting
SELECT c.first_name || ' ' || c.last_name AS customer,
       COUNT(o.id) AS orders,
       ROUND(SUM(o.total_amount), 2) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.id, c.first_name, c.last_name
HAVING SUM(o.total_amount) > 100
ORDER BY total_spent DESC;

-- Raw: Hard to read
SELECT category_id, avg(price), sum(stock_quantity) FROM products GROUP BY category_id;

-- Polished: Report-ready
SELECT c.name AS "Category",
       COUNT(p.id) AS "Products",
       ROUND(AVG(p.price), 2) AS "Avg Price",
       SUM(p.stock_quantity) AS "Total Stock",
       ROUND(SUM(p.price * p.stock_quantity), 2) AS "Inventory Value"
FROM categories c
JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.name
ORDER BY "Inventory Value" DESC;