-- TEMPLATE: Top N per group
WITH ranked AS (
    SELECT
        group_col,
        value_col,
        other_cols,
        DENSE_RANK() OVER (
            PARTITION BY group_col          -- reset rank for each group
            ORDER BY value_col DESC         -- rank by this (DESC = highest first)
        ) AS rnk
    FROM source_table
)
SELECT group_col, value_col, other_cols
FROM ranked
WHERE rnk <= N;   -- change N to 1, 2, 3 etc.

WITH category_spend AS (
    -- Step 1: Aggregate total spend per category+product pair
    SELECT
        category,
        product,
        SUM(spend) AS total_spend
    FROM product_spend
    WHERE EXTRACT(YEAR FROM transaction_date) = 2022
    GROUP BY category, product
),

ranked_products AS (
    -- Step 2: Rank products within each category by spend
    SELECT
        category,
        product,
        total_spend,
        DENSE_RANK() OVER (
            PARTITION BY category
            ORDER BY total_spend DESC
        ) AS spend_rank
    FROM category_spend
)

-- Step 3: Keep only top 2
SELECT category, product, total_spend
FROM ranked_products
WHERE spend_rank <= 2
ORDER BY category, spend_rank;

WITH dept_salary_rank AS (
    SELECT
        e.name AS employee_name,
        d.name AS department_name,
        e.salary,
        DENSE_RANK() OVER (
            PARTITION BY e.department_id
            ORDER BY e.salary DESC
        ) AS salary_rank
    FROM employees e
    JOIN departments d ON e.department_id = d.id
)

SELECT department_name, employee_name, salary
FROM dept_salary_rank
WHERE salary_rank <= 3;

-- ⚠️ Edge case: if a dept has only 2 employees → returns 2 rows (correct!)
-- ⚠️ Ties: if 3rd and 4th salary are equal → DENSE_RANK returns BOTH at rank 3
--    Both are "Top 3" so this is CORRECT behavior for interview questions

-- TEMPLATE: Running total / cumulative sum
SELECT
    partition_col,
    date_col,
    daily_value,

    -- Cumulative sum (reset per partition)
    SUM(daily_value) OVER (
        PARTITION BY partition_col      -- reset for each partition (optional)
        ORDER BY date_col               -- accumulate in this order
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- from start to now
    ) AS running_total,

    -- Rolling N-day average (e.g., 7-day)
    AVG(daily_value) OVER (
        PARTITION BY partition_col
        ORDER BY date_col
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- current + 6 previous = 7 rows
    ) AS rolling_7day_avg

FROM daily_metrics;

WITH monthly_txns AS (
    SELECT
        merchant_id,
        transaction_date,
        amount,
        -- Extract year+month for the PARTITION (reset each month)
        DATE_TRUNC('month', transaction_date) AS txn_month
    FROM transactions
)

SELECT
    merchant_id,
    transaction_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY merchant_id, txn_month  -- partition = merchant + month → resets monthly
        ORDER BY transaction_date            -- accumulate in date order within the month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS monthly_running_balance
FROM monthly_txns
ORDER BY merchant_id, transaction_date;

WITH daily_tweets AS (
    SELECT
        user_id,
        tweet_date,
        COUNT(*) AS tweet_count
    FROM tweets
    GROUP BY user_id, tweet_date
)

SELECT
    user_id,
    tweet_date,
    tweet_count,
    ROUND(
        AVG(tweet_count) OVER (
            PARTITION BY user_id
            ORDER BY tweet_date
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 3 rows: today + 2 before
        ), 2
    ) AS rolling_3day_avg
FROM daily_tweets
ORDER BY user_id, tweet_date;

-- TEMPLATE: LAG / LEAD comparison
SELECT
    partition_col,
    date_col,
    value_col,

    -- Previous row's value
    LAG(value_col, 1) OVER (
        PARTITION BY partition_col
        ORDER BY date_col
    ) AS prev_value,

    -- Compute change
    value_col - LAG(value_col, 1) OVER (
        PARTITION BY partition_col
        ORDER BY date_col
    ) AS change_from_prev,

    -- % change
    ROUND(
        100.0 * (value_col - LAG(value_col,1) OVER (PARTITION BY partition_col ORDER BY date_col))
        / NULLIF(LAG(value_col,1) OVER (PARTITION BY partition_col ORDER BY date_col), 0),
    2) AS pct_change,

    -- Next row's value
    LEAD(value_col, 1) OVER (
        PARTITION BY partition_col
        ORDER BY date_col
    ) AS next_value

FROM source_table;

-- ⚠️ NULLIF(..., 0) prevents division by zero when previous value = 0
-- ⚠️ First row: LAG returns NULL (no previous row) — handle with COALESCE if needed

WITH payment_gaps AS (
    SELECT
        merchant_id,
        credit_card_id,
        amount,
        transaction_timestamp,

        -- Get the PREVIOUS transaction timestamp for same merchant+card+amount
        LAG(transaction_timestamp) OVER (
            PARTITION BY merchant_id, credit_card_id, amount
            ORDER BY transaction_timestamp
        ) AS prev_timestamp
    FROM transactions
)

SELECT COUNT(*) AS duplicate_count
FROM payment_gaps
WHERE
    prev_timestamp IS NOT NULL  -- not the first transaction in this group
    AND transaction_timestamp - prev_timestamp <= INTERVAL '10 minutes';
    -- In Hive/Spark: (UNIX_TIMESTAMP(transaction_timestamp) - UNIX_TIMESTAMP(prev_timestamp)) <= 600

WITH purchase_sequence AS (
    SELECT
        user_id,
        product_name,
        -- Get the NEXT purchase for each user
        LEAD(product_name, 1) OVER (
            PARTITION BY user_id
            ORDER BY purchase_date
        ) AS next_product
    FROM purchases
),

iphone_buyers AS (
    SELECT COUNT(DISTINCT user_id) AS total_iphone_buyers
    FROM purchase_sequence
    WHERE product_name = 'iPhone'
),

iphone_then_airpod AS (
    SELECT COUNT(DISTINCT user_id) AS airpod_after_iphone
    FROM purchase_sequence
    WHERE product_name = 'iPhone'
      AND next_product = 'AirPods'
)

SELECT
    ROUND(
        100.0 * a.airpod_after_iphone / i.total_iphone_buyers,
    2) AS airpod_after_iphone_pct
FROM iphone_then_airpod a, iphone_buyers i;

WITH ordered_transactions AS (
    SELECT
        user_id,
        transaction_date,
        amount,
        LAG(amount) OVER (
            PARTITION BY user_id
            ORDER BY transaction_date
        ) AS prev_amount
    FROM transactions
)

SELECT user_id, transaction_date, amount, prev_amount
FROM ordered_transactions
WHERE prev_amount IS NOT NULL   -- skip first row per user
  AND amount > prev_amount;     -- only rows where amount increased