Advanced SQL Concepts

function_name(expression) OVER (
    PARTITION BY column      -- reset the window for each group
    ORDER BY column          -- order within the window
    ROWS/RANGE frame_spec    -- optional: define the window size
)

SELECT
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_dense_rank,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS seven_day_moving_avg
FROM daily_sales;

SELECT
    product_id,
    sale_date,
    revenue,
    LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY sale_date) AS prev_day_revenue,
    revenue - LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY sale_date) AS day_over_day_change
FROM product_sales;

WITH
customer_orders AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spent,
        MAX(order_date) AS last_order_date
    FROM orders
    GROUP BY customer_id
),
customer_segments AS (
    SELECT
        customer_id,
        total_spent,
        CASE
            WHEN total_spent >= 5000 THEN 'Platinum'
            WHEN total_spent >= 1000 THEN 'Gold'
            WHEN total_spent >= 250  THEN 'Silver'
            ELSE 'Bronze'
        END AS segment
    FROM customer_orders
)
SELECT
    cs.segment,
    COUNT(*) AS customer_count,
    AVG(co.total_spent) AS avg_spend,
    AVG(co.order_count) AS avg_orders
FROM customer_segments cs
JOIN customer_orders co USING (customer_id)
GROUP BY cs.segment
ORDER BY avg_spend DESC;

-- Walk an org chart from a given manager down
WITH RECURSIVE org_tree AS (
    -- Anchor: start with the given manager
    SELECT employee_id, name, manager_id, 0 AS depth
    FROM employees
    WHERE employee_id = 100  -- starting manager

    UNION ALL

    -- Recursive: find direct reports of each person in the tree
    SELECT e.employee_id, e.name, e.manager_id, ot.depth + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT name, depth
FROM org_tree
ORDER BY depth, name;

-- For each customer, get their 3 most recent orders
SELECT
    c.customer_id,
    c.name,
    recent.order_id,
    recent.amount,
    recent.order_date
FROM customers c
CROSS JOIN LATERAL (
    SELECT order_id, amount, order_date
    FROM orders o
    WHERE o.customer_id = c.customer_id   -- references c from outer query
    ORDER BY order_date DESC
    LIMIT 3
) recent;

SELECT
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed,
    COUNT(*) FILTER (WHERE status = 'refunded') AS refunded,
    SUM(amount) FILTER (WHERE status = 'completed') AS completed_revenue
FROM orders;

SELECT
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_revenue,
    SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded_revenue
FROM orders;

-- EXISTS is typically faster when the subquery result is large
SELECT customer_id, name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.amount > 1000
);

-- Extract fields from JSON column
SELECT
    id,
    event_data->>'user_id' AS user_id,
    (event_data->>'amount')::numeric AS amount,
    event_data->'metadata'->>'source' AS source
FROM events
WHERE event_data->>'type' = 'purchase';

-- Query arrays in JSON
SELECT id, jsonb_array_elements_text(event_data->'tags') AS tag
FROM events;

SELECT
    LOWER(TRIM(email)) AS normalized_email,
    SPLIT_PART(email, '@', 2) AS domain,
    LEFT(phone, 3) AS area_code,
    REGEXP_REPLACE(description, '[^a-zA-Z0-9 ]', '', 'g') AS clean_description
FROM users;

EXPLAIN ANALYZE
SELECT c.name, SUM(o.amount)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2025-01-01'
GROUP BY c.customer_id, c.name
ORDER BY SUM(o.amount) DESC;

-- BAD: function on indexed column prevents index use
WHERE YEAR(order_date) = 2025

-- GOOD: range comparison uses the index
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'

-- BAD: leading wildcard prevents index use
WHERE name LIKE '%smith%'

-- GOOD: leading literal uses index
WHERE name LIKE 'smith%'

-- BAD: SELECT * when you need 2 columns from a 50-column table
SELECT * FROM orders WHERE status = 'pending'

-- GOOD: only fetch what you need
SELECT order_id, amount FROM orders WHERE status = 'pending'

-- Sales by product, pivoted to months as columns
SELECT
    product_name,
    SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 1 THEN amount END) AS jan,
    SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 2 THEN amount END) AS feb,
    SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 3 THEN amount END) AS mar,
    SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 4 THEN amount END) AS apr
FROM sales
WHERE EXTRACT(YEAR FROM sale_date) = 2025
GROUP BY product_name;

-- All customers and suppliers in one list
SELECT name, 'customer' AS type FROM customers
UNION
SELECT company_name, 'supplier' AS type FROM suppliers;

-- Customers who are also registered as suppliers
SELECT email FROM customers
INTERSECT
SELECT contact_email FROM suppliers;

-- Customers who have NOT placed an order in 2025
SELECT customer_id FROM customers
EXCEPT
SELECT DISTINCT customer_id FROM orders WHERE order_date >= '2025-01-01';

-- PostgreSQL
SELECT
    NOW() AS current_timestamp,
    CURRENT_DATE AS today,
    CURRENT_DATE - INTERVAL '30 days' AS thirty_days_ago,
    DATE_TRUNC('week', order_date) AS week_start,
    EXTRACT(DOW FROM order_date) AS day_of_week,  -- 0=Sunday, 6=Saturday
    order_date + INTERVAL '1 month' AS next_month
FROM orders;

-- Calculate age / days between dates
SELECT
    customer_id,
    CURRENT_DATE - first_order_date AS days_since_first_order,
    AGE(last_order_date, first_order_date) AS order_lifespan
FROM customer_summary;