Final Project: Build a Production-Ready Database 💼

-- CHALLENGE 1: Design & Build the Schema
-- Create a complete e-commerce database from scratch

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    is_vip BOOLEAN DEFAULT false
);

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT REFERENCES cate
...

-- CHALLENGE 2: Business Analytics Queries
-- Answer real business questions with SQL

-- 1. Monthly revenue report with month-over-month growth
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        COUNT(DISTINCT order_id) AS total_orders,
        COUNT(DISTINCT customer_id) AS unique_customers,
        ROUND(SUM(total), 2) AS revenue
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
    month,
    total_orders
...

-- CHALLENGE 3: Stored Procedure with Transaction Safety
-- Process an order with stock validation and audit logging

CREATE TABLE order_audit (
    id SERIAL PRIMARY KEY,
    order_id INT,
    action VARCHAR(50) NOT NULL,
    details JSONB,
    performed_by TEXT DEFAULT current_user,
    performed_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION place_order(
    p_customer_id INT,
    p_items JSONB  -- [{"product_id": 1, "quantity": 2}, ...]
)
RETURNS INT AS $$
DECLARE
    v_order_id
...

-- CHALLENGE 4: Dashboard Views & Inventory Alerts

-- Revenue dashboard with KPIs
CREATE VIEW dashboard_kpis AS
WITH current_month AS (
    SELECT SUM(total) AS revenue, COUNT(*) AS orders
    FROM orders
    WHERE created_at >= DATE_TRUNC('month', NOW())
      AND status != 'cancelled'
),
prev_month AS (
    SELECT SUM(total) AS revenue, COUNT(*) AS orders
    FROM orders
    WHERE created_at >= DATE_TRUNC('month', NOW()) - INTERVAL '1 month'
      AND created_at < DATE_TRUNC('month', NOW())
 
...

-- CHALLENGE 5: Performance Optimisation & Security

-- 1. Create a materialised view for slow reports
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT
    created_at::DATE AS sale_date,
    COUNT(*) AS orders,
    ROUND(SUM(total), 2) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE status != 'cancelled'
GROUP BY created_at::DATE;

CREATE UNIQUE INDEX idx_mv_daily_date ON mv_daily_sales(sale_date);
-- Refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sal
...