Advanced SQL Techniques

-- ROW_NUMBER: Unique sequential number
SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

-- RANK: Same rank for ties, gaps after
SELECT
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

-- DENSE_RANK: Same rank for ties, no gaps
SELECT
    name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

-- NTILE: Divide into N equal groups
SELECT
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

-- LAG/LEAD: Previous/next row values
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS change
FROM monthly_stats;

-- FIRST_VALUE/LAST_VALUE
SELECT
    name,
    department,
    salary,
    FIRST_VALUE(name) OVER (
        PARTITION BY department ORDER BY salary DESC
    ) AS top_earner
FROM employees;

-- Organizational hierarchy
WITH RECURSIVE org_chart AS (
    -- Base case: CEO (no manager)
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: everyone else
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

-- Generate date series
WITH RECURSIVE dates AS (
    SELECT CURRENT_DATE AS date
    UNION ALL
    SELECT date + INTERVAL '1 day'
    FROM dates
    WHERE date < CURRENT_DATE + INTERVAL '30 days'
)
SELECT date FROM dates;

-- Monthly sales by product category
SELECT
    TO_CHAR(order_date, 'YYYY-MM') AS month,
    SUM(CASE WHEN category = 'Electronics' THEN amount ELSE 0 END) AS electronics,
    SUM(CASE WHEN category = 'Clothing' THEN amount ELSE 0 END) AS clothing,
    SUM(CASE WHEN category = 'Food' THEN amount ELSE 0 END) AS food
FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;

-- Using crosstab function (PostgreSQL)
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
    'SELECT user_id, metric, value FROM user_stats ORDER BY 1, 2',
    'SELECT DISTINCT metric FROM user_stats ORDER BY 1'
) AS ct(user_id INT, login_count INT, post_count INT, like_count INT);

-- Users who have placed at least one order
SELECT u.name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Users who have NEVER placed an order
SELECT u.name
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Products in every active order
SELECT p.name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.status = 'active'
    AND NOT EXISTS (
        SELECT 1 FROM order_items oi
        WHERE oi.order_id = o.id AND oi.product_id = p.id
    )
);

-- Create table with JSONB
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert JSON
INSERT INTO events (data) VALUES
('{"type": "click", "page": "/home", "user": {"id": 1, "name": "Alice"}}');

-- Query JSON fields
SELECT data->>'type' AS event_type FROM events;
SELECT data->>'page' AS page FROM events WHERE data->>'type' = 'click';
SELECT data->'user'->>'name' AS user_name FROM events;

-- Filter with @> (contains)
SELECT * FROM events WHERE data @> '{"type": "click"}';

-- JSON aggregation
SELECT
    data->>'type' AS event_type,
    COUNT(*) AS occurrences
FROM events
GROUP BY data->>'type';

-- Create GIN index for fast JSON queries
CREATE INDEX idx_events_data ON events USING GIN(data);

-- Basic transaction
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Rollback on error
BEGIN;
    INSERT INTO orders (customer_id, total) VALUES (1, 99.99);
    -- If this fails, the order insert is rolled back too
    INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 1);
COMMIT;

-- Savepoints
BEGIN;
    INSERT INTO orders (customer_id, total) VALUES (1, 99.99);
    SAVEPOINT after_order;
    INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 1);
    -- Oops, something wrong — rollback just the item
    ROLLBACK TO SAVEPOINT after_order;
    -- Order still exists, item is gone
    INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 2, 3);
COMMIT;

-- Expensive query, cached as a physical table
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    DATE_TRUNC('month', o.created_at) AS month,
    p.category,
    SUM(oi.quantity * oi.unit_price) AS revenue,
    COUNT(DISTINCT o.id) AS order_count
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'
GROUP BY DATE_TRUNC('month', o.created_at), p.category;

-- Query it like a normal table (FAST!)
SELECT * FROM monthly_sales WHERE month = '2024-01-01';

-- Refresh when data changes
REFRESH MATERIALIZED VIEW monthly_sales;
-- Or concurrently (doesn't lock reads):
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;