Advanced SQL: Joins, Window Functions, CTEs, and Pagination

SELECT o.id AS order_id, c.name AS customer_name, o.total_cents
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;
-- Orders with no matching customer (shouldn't happen with a FK, but could
-- with orphaned data) are silently excluded.

-- All customers, including those who have never placed an order
SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
-- Customers with zero orders still appear, with order_count = 0

-- RIGHT JOIN: rarely used in practice — equivalent to swapping table order
-- and using LEFT JOIN. Most style guides ban it for readability.
SELECT c.name, o.id
FROM orders o
RIGHT JOIN customers c ON c.id = o.customer_id;
 
-- FULL OUTER JOIN: not supported natively in MySQL, but standard in PostgreSQL
-- PostgreSQL:
-- SELECT c.id, c.name, o.id AS order_id
-- FROM customers c
-- FULL OUTER JOIN orders o ON o.customer_id = c.id;
 
-- MySQL equivalent via UNION of LEFT and RIGHT JOIN:
SELECT c.id, c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.id, c.name, o.id AS order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

-- Find every employee alongside their manager's name
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Generate every (size, color) combination for a product configurator
SELECT s.size_label, c.color_name
FROM sizes s
CROSS JOIN colors c;
-- 5 sizes x 8 colors = 40 rows, deliberately

-- Total revenue and order count per customer
SELECT
    customer_id,
    COUNT(*)              AS order_count,
    SUM(total_cents)       AS total_revenue_cents,
    AVG(total_cents)       AS avg_order_cents,
    MAX(total_cents)       AS largest_order_cents
FROM orders
WHERE status = 'CONFIRMED'
GROUP BY customer_id
HAVING COUNT(*) >= 5           -- filters GROUPS, evaluated after aggregation
ORDER BY total_revenue_cents DESC
LIMIT 20;

-- Multi-column grouping
SELECT
    DATE(created_at) AS order_date,
    status,
    COUNT(*) AS count
FROM orders
GROUP BY DATE(created_at), status
ORDER BY order_date DESC, status;

-- Rank customers by revenue, per region
SELECT
    region,
    customer_id,
    total_revenue_cents,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_revenue_cents DESC) AS row_num,
    RANK()       OVER (PARTITION BY region ORDER BY total_revenue_cents DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY total_revenue_cents DESC) AS dense_rnk
FROM customer_revenue;

-- Top 3 highest-value orders per customer (a query that's painful without window functions)
WITH ranked_orders AS (
    SELECT
        id, customer_id, total_cents,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_cents DESC) AS rn
    FROM orders
)
SELECT id, customer_id, total_cents
FROM ranked_orders
WHERE rn <= 3;

-- Running total of daily revenue
SELECT
    order_date,
    daily_revenue_cents,
    SUM(daily_revenue_cents) OVER (ORDER BY order_date) AS running_total_cents,
    AVG(daily_revenue_cents) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS trailing_7day_avg_cents
FROM daily_revenue;

-- Month-over-month revenue change, per customer
SELECT
    customer_id,
    order_month,
    revenue_cents,
    LAG(revenue_cents) OVER (PARTITION BY customer_id ORDER BY order_month) AS prev_month_revenue,
    revenue_cents - LAG(revenue_cents) OVER (PARTITION BY customer_id ORDER BY order_month) AS mom_change
FROM monthly_customer_revenue;

WITH high_value_customers AS (
    SELECT customer_id, SUM(total_cents) AS total_spent
    FROM orders
    WHERE status = 'CONFIRMED'
    GROUP BY customer_id
    HAVING SUM(total_cents) > 1000000
),
recent_activity AS (
    SELECT customer_id, MAX(created_at) AS last_order_at
    FROM orders
    GROUP BY customer_id
)
SELECT
    hvc.customer_id,
    hvc.total_spent,
    ra.last_order_at
FROM high_value_customers hvc
JOIN recent_activity ra ON ra.customer_id = hvc.customer_id
ORDER BY hvc.total_spent DESC;

-- Walk an org chart: find every employee under a given manager, at any depth
WITH RECURSIVE org_chart AS (
    -- Anchor member: the starting manager
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE id = 1
 
    UNION ALL
 
    -- Recursive member: employees reporting to anyone already in the result
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth, name;

-- Standard view: re-executed on every query against it
CREATE VIEW active_customer_summary AS
SELECT
    c.id,
    c.name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_cents), 0) AS lifetime_value_cents
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'CONFIRMED'
GROUP BY c.id, c.name;
 
-- Query it like a table
SELECT * FROM active_customer_summary WHERE lifetime_value_cents > 500000;

-- Materialized view (PostgreSQL): computed once, stored, refreshed on demand
-- CREATE MATERIALIZED VIEW active_customer_summary_mat AS
-- SELECT ... (same query as above);
--
-- REFRESH MATERIALIZED VIEW CONCURRENTLY active_customer_summary_mat;

-- Page 3, 20 rows per page
SELECT id, name, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

-- First page
SELECT id, name, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;
 
-- Next page: use the last row's (created_at, id) as the cursor
SELECT id, name, created_at
FROM orders
WHERE (created_at, id) < ('2026-08-10 14:32:00', 918273)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Uncorrelated: inner query runs once, independent of the outer query
SELECT name FROM products
WHERE id IN (
    SELECT product_id FROM order_items GROUP BY product_id HAVING COUNT(*) > 100
);
 
-- Correlated: inner query re-runs once per outer row, referencing the outer row
SELECT c.name,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;