SQL Analytics Expert

SELECT
    employee_id,
    department,
    salary,
    -- Different ranking behaviors
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_num,
    NTILE(4)     OVER (PARTITION BY department ORDER BY salary DESC) AS quartile,
    PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary) AS pct_rank
FROM employees;

-- Top N per group (common pattern)
WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

SELECT
    order_date,
    daily_revenue,
    -- Cumulative sum
    SUM(daily_revenue) OVER (ORDER BY order_date) AS cumulative_revenue,
    -- Running average
    AVG(daily_revenue) OVER (ORDER BY order_date) AS running_avg,
    -- Moving average (7-day)
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d,
    -- Moving sum (30-day range-based)
    SUM(daily_revenue) OVER (
        ORDER BY order_date
        RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
    ) AS moving_sum_30d
FROM daily_metrics;

SELECT
    month,
    revenue,
    -- Previous period
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
    -- Year-over-year
    LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year,
    -- Month-over-month growth
    ROUND(100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY month))
        / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0), 2) AS mom_growth_pct,
    -- Year-over-year growth
    ROUND(100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY month))
        / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0), 2) AS yoy_growth_pct,
    -- First and last values in partition
    FIRST_VALUE(revenue) OVER (ORDER BY month) AS first_month_revenue,
    LAST_VALUE(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_month_revenue
FROM monthly_revenue;

-- ROWS vs RANGE vs GROUPS
-- ROWS: physical row count
-- RANGE: logical value range (handles ties differently)
-- GROUPS: groups of tied rows

-- Frame boundaries:
-- UNBOUNDED PRECEDING  = start of partition
-- N PRECEDING          = N rows/values before current
-- CURRENT ROW          = current row
-- N FOLLOWING          = N rows/values after current
-- UNBOUNDED FOLLOWING  = end of partition

-- Example: Centered moving average
AVG(value) OVER (
    ORDER BY date
    ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
) AS centered_avg_7d

WITH
-- Step 1: Calculate daily metrics
daily_metrics AS (
    SELECT
        DATE_TRUNC('day', created_at) AS day,
        COUNT(DISTINCT user_id) AS dau,
        COUNT(*) AS events,
        SUM(revenue) AS daily_revenue
    FROM events
    WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY 1
),

-- Step 2: Add rolling averages
with_rolling AS (
    SELECT
        *,
        AVG(dau) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS dau_7d_avg,
        AVG(daily_revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rev_7d_avg
    FROM daily_metrics
),

-- Step 3: Add week-over-week comparison
with_comparison AS (
    SELECT
        *,
        LAG(dau, 7) OVER (ORDER BY day) AS dau_prev_week,
        ROUND(100.0 * (dau - LAG(dau, 7) OVER (ORDER BY day))
            / NULLIF(LAG(dau, 7) OVER (ORDER BY day), 0), 1) AS dau_wow_pct
    FROM with_rolling
)

SELECT * FROM with_comparison
ORDER BY day DESC;

WITH user_segments AS (
    SELECT
        user_id,
        CASE
            WHEN total_spend > 1000 THEN 'high_value'
            WHEN total_spend > 100 THEN 'mid_value'
            ELSE 'low_value'
        END AS segment
    FROM (
        SELECT user_id, SUM(amount) AS total_spend
        FROM orders
        GROUP BY user_id
    ) t
)
-- Reuse the CTE in multiple places
SELECT
    s.segment,
    COUNT(DISTINCT s.user_id) AS users,
    AVG(e.session_count) AS avg_sessions,
    AVG(e.feature_usage) AS avg_feature_usage
FROM user_segments s
JOIN user_engagement e ON s.user_id = e.user_id
GROUP BY s.segment;

WITH RECURSIVE org_tree AS (
    -- Base case: top-level managers
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS level,
        name AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: each employee's reports
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        ot.level + 1,
        ot.path || ' > ' || e.name
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree ORDER BY path;

WITH RECURSIVE date_series AS (
    SELECT DATE '2024-01-01' AS dt
    UNION ALL
    SELECT dt + INTERVAL '1 day'
    FROM date_series
    WHERE dt < DATE '2024-12-31'
)
SELECT
    ds.dt,
    COALESCE(m.revenue, 0) AS revenue,
    COALESCE(m.orders, 0) AS orders
FROM date_series ds
LEFT JOIN daily_metrics m ON ds.dt = m.metric_date;

WITH event_gaps AS (
    SELECT
        user_id,
        event_time,
        LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event,
        CASE
            WHEN event_time - LAG(event_time) OVER (
                PARTITION BY user_id ORDER BY event_time
            ) > INTERVAL '30 minutes'
            OR LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL
            THEN 1
            ELSE 0
        END AS new_session
    FROM events
),
sessions AS (
    SELECT
        user_id,
        event_time,
        SUM(new_session) OVER (
            PARTITION BY user_id ORDER BY event_time
        ) AS session_id
    FROM event_gaps
)
SELECT
    user_id,
    session_id,
    MIN(event_time) AS session_start,
    MAX(event_time) AS session_end,
    COUNT(*) AS event_count,
    MAX(event_time) - MIN(event_time) AS session_duration
FROM sessions
GROUP BY user_id, session_id;

-- Covering index for common analytics queries
CREATE INDEX idx_events_user_date ON events (user_id, event_date)
    INCLUDE (event_type, revenue);

-- Partial index for active records
CREATE INDEX idx_active_users ON users (created_at, plan)
    WHERE status = 'active';

-- Expression index
CREATE INDEX idx_events_month ON events (DATE_TRUNC('month', created_at));

-- Check query plan
EXPLAIN ANALYZE
SELECT
    DATE_TRUNC('month', o.created_at) AS month,
    c.segment,
    SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.created_at >= '2024-01-01'
GROUP BY 1, 2;

-- Key things to look for:
-- Seq Scan on large tables  -> needs index
-- Nested Loop on large sets -> consider Hash Join
-- Sort with high row count  -> add ORDER BY index
-- High actual vs estimated  -> update statistics (ANALYZE)

-- AVOID: Subquery in SELECT (runs per row)
SELECT
    user_id,
    (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.user_id) AS order_count
FROM users;

-- BETTER: Join with aggregation
SELECT
    u.user_id,
    COALESCE(o.order_count, 0) AS order_count
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) o ON u.user_id = o.user_id;

-- AVOID: DISTINCT on large result sets
SELECT DISTINCT user_id, event_type FROM events;

-- BETTER: GROUP BY (often has better query plan)
SELECT user_id, event_type FROM events GROUP BY user_id, event_type;

-- AVOID: OR conditions on different columns
SELECT * FROM orders WHERE customer_id = 100 OR product_id = 200;

-- BETTER: UNION for separate index usage
SELECT * FROM orders WHERE customer_id = 100
UNION
SELECT * FROM orders WHERE product_id = 200;

SELECT
    product_category,
    SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1,
    SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2,
    SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS q3,
    SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS q4,
    SUM(revenue) AS total
FROM quarterly_sales
GROUP BY product_category
ORDER BY total DESC;

-- Requires tablefunc extension
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
    'SELECT department, month, revenue
     FROM monthly_revenue
     ORDER BY 1, 2',
    'SELECT DISTINCT month FROM monthly_revenue ORDER BY 1'
) AS ct(
    department TEXT,
    "2024-01" NUMERIC,
    "2024-02" NUMERIC,
    "2024-03" NUMERIC
);

-- PostgreSQL: UNNEST with VALUES
SELECT
    user_id,
    metric_name,
    metric_value
FROM user_scores,
LATERAL (
    VALUES
        ('engagement', engagement_score),
        ('satisfaction', satisfaction_score),
        ('loyalty', loyalty_score)
) AS t(metric_name, metric_value);

-- Find consecutive active days (islands)
WITH numbered AS (
    SELECT
        user_id,
        active_date,
        active_date - (ROW_NUMBER() OVER (
            PARTITION BY user_id ORDER BY active_date
        ) * INTERVAL '1 day') AS grp
    FROM daily_active_users
)
SELECT
    user_id,
    MIN(active_date) AS streak_start,
    MAX(active_date) AS streak_end,
    COUNT(*) AS streak_length
FROM numbered
GROUP BY user_id, grp
HAVING COUNT(*) >= 7  -- Streaks of 7+ days
ORDER BY streak_length DESC;

-- Cumulative sum that resets each month
SELECT
    order_date,
    revenue,
    SUM(revenue) OVER (
        PARTITION BY DATE_TRUNC('month', order_date)
        ORDER BY order_date
    ) AS mtd_revenue
FROM daily_revenue;

-- Exact median using PERCENTILE_CONT
SELECT
    department,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS p25_salary,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS p75_salary
FROM employees
GROUP BY department;