-- STEP 1: Understand the grain
--   "What does ONE ROW in the output represent?"
--   Example: one row = one product per category

-- STEP 2: Build intermediate CTEs
WITH raw_data AS (
    -- Clean or filter source data
    SELECT ...
    FROM source_table
    WHERE conditions
),

intermediate AS (
    -- Apply window function or join
    SELECT
        ...,
        ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col) AS rn
    FROM raw_data
)

-- STEP 3: Filter to final result
SELECT ...
FROM intermediate
WHERE rn = 1;  -- or whatever condition applies

-- STEP 4: Check edge cases in your head:
--   - What if there are NULL values in partition/order columns?
--   - What if a group has only 1 row? (top-2 query returns 1 row per group — is that OK?)
--   - Are ties handled correctly? (RANK vs DENSE_RANK)
--   - What if the date column has gaps?

-- STEP 5: Verbalize to interviewer:
--   "First I'm partitioning by X because each group needs its own ranking..."
--   "I chose DENSE_RANK over RANK because I want ties to share the same rank..."

-- Bank transfer: Move ₹5000 from account 101 to account 202

BEGIN TRANSACTION;

  -- Step 1: Debit sender
  UPDATE accounts
  SET balance = balance - 5000
  WHERE account_id = 101;

  -- Step 2: Credit receiver
  UPDATE accounts
  SET balance = balance + 5000
  WHERE account_id = 202;

  -- Step 3: Log the transfer
  INSERT INTO transfer_log (from_acct, to_acct, amount, txn_time)
  VALUES (101, 202, 5000, CURRENT_TIMESTAMP);

COMMIT;
-- If ANY step fails → ROLLBACK undoes everything (Atomicity)

-- What happens on failure:
BEGIN TRANSACTION;

  UPDATE accounts SET balance = balance - 5000 WHERE account_id = 101;
  -- ^^^ This succeeds

  UPDATE accounts SET balance = balance + 5000 WHERE account_id = 999;
  -- ^^^ Account 999 doesn't exist — ERROR

ROLLBACK;
-- Both updates are undone. Account 101 keeps its original balance.

-- Setting isolation level (PostgreSQL syntax)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;
  SELECT balance FROM accounts WHERE account_id = 101;
  -- If another transaction changes this row and commits,
  -- a second SELECT in this transaction WILL see the new value.
  -- (Non-repeatable read is possible)
COMMIT;

-- Setting isolation level (MySQL syntax)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;
  SELECT balance FROM accounts WHERE account_id = 101;
  -- Result: 50000

  -- Another session updates balance to 60000 and commits.

  SELECT balance FROM accounts WHERE account_id = 101;
  -- Result: STILL 50000 (snapshot from start of transaction)
COMMIT;

-- SQL Server syntax
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;
  SELECT * FROM orders WHERE customer_id = 42;
  -- No other transaction can INSERT/UPDATE/DELETE rows
  -- matching customer_id = 42 until this transaction ends.
COMMIT;

-- Normalized: Requires JOIN every time
SELECT o.order_id, c.customer_name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- Denormalized: customer_name stored directly in orders table
-- Faster reads, but you must update it everywhere if the name changes
SELECT order_id, customer_name, total
FROM orders_denormalized;

Without index: Full Table Scan — reads ALL 10 million rows
With index:    B-Tree lookup — reads ~20 nodes to find the row

B-Tree structure (balanced tree):
                    [M]
                   /   \
               [D,H]   [R,V]
              / | \    / | \
           [A-C][E-G][I-L][N-Q][S-U][W-Z]
                              ↓
                         Points to actual rows on disk

-- Basic index
CREATE INDEX idx_customers_email
ON customers (email);

-- Composite index (multi-column — order matters!)
CREATE INDEX idx_orders_cust_date
ON orders (customer_id, order_date);
-- ✅ This helps: WHERE customer_id = 42 AND order_date > '2025-01-01'
-- ✅ This helps: WHERE customer_id = 42 (leftmost prefix)
-- ❌ This does NOT help: WHERE order_date > '2025-01-01' (skips first column)

-- Covering index: includes all columns the query needs
-- The DB can answer the query entirely from the index without touching the table
CREATE INDEX idx_orders_covering
ON orders (customer_id, order_date)
INCLUDE (total_amount, status);

-- Unique index
CREATE UNIQUE INDEX idx_users_email
ON users (email);

-- Drop an index
DROP INDEX idx_customers_email;

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
  AND order_date > '2025-01-01';

-- Example EXPLAIN output (PostgreSQL):
-- Index Scan using idx_orders_cust_date on orders
--   Index Cond: (customer_id = 42)
--   Filter: (order_date > '2025-01-01')
--   Rows Removed by Filter: 12
--   actual time=0.045..0.089 rows=38 loops=1

-- Translation: Used the index on customer_id, then filtered by date.
-- Very fast (0.089 ms). Returned 38 rows.

-- Approach 1: IN
SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders
);
-- Works fine for small orders table
-- PROBLEM: If orders.customer_id has NULLs, IN can behave unexpectedly

-- Approach 2: EXISTS (generally preferred for existence checks)
SELECT customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);
-- Stops scanning orders as soon as it finds ONE match (short-circuit)
-- Handles NULLs safely

-- Approach 3: JOIN
SELECT DISTINCT c.customer_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
-- Need DISTINCT because a customer with 5 orders appears 5 times
-- Best when you also need order columns in the output

-- This is the classic trap interviewers love:

-- Suppose orders.customer_id has values: (1, 2, NULL)

-- NOT IN with NULLs: RETURNS NOTHING!
SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
-- SQL evaluates: customer_id != 1 AND customer_id != 2 AND customer_id != NULL
-- Anything compared to NULL → UNKNOWN → entire WHERE clause → UNKNOWN → row excluded
-- Result: ZERO rows returned, regardless of data!

-- NOT EXISTS: Works correctly
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Returns customers who genuinely have no orders

-- ❌ WRONG — this will NEVER find NULL rows
SELECT * FROM employees WHERE manager_id = NULL;
-- Evaluates to NULL (not TRUE), so no rows returned

-- ✅ CORRECT
SELECT * FROM employees WHERE manager_id IS NULL;

-- ✅ Also correct
SELECT * FROM employees WHERE manager_id IS NOT NULL;

-- Sample data:
-- sales: (100, 200, NULL, 300, NULL)

SELECT
    COUNT(*)          AS total_rows,      -- 5 (counts ALL rows including NULLs)
    COUNT(amount)     AS non_null_count,  -- 3 (skips NULLs)
    SUM(amount)       AS total,           -- 600 (NULLs ignored)
    AVG(amount)       AS average,         -- 200 (600/3, NOT 600/5!)
    MIN(amount)       AS minimum,         -- 100 (NULLs ignored)
    MAX(amount)       AS maximum          -- 300 (NULLs ignored)
FROM sales;

-- KEY INSIGHT: AVG ignores NULLs.
-- If you want NULLs treated as 0:
SELECT AVG(COALESCE(amount, 0)) FROM sales;  -- 600/5 = 120

-- NULLs NEVER match in JOINs
-- If orders.customer_id = NULL and customers.customer_id = NULL,
-- they will NOT join together because NULL = NULL → NULL (not TRUE)

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- Rows with NULL customer_id are silently dropped from both sides

-- COALESCE: Returns the first non-NULL value
SELECT COALESCE(phone, mobile, email, 'No Contact')
FROM employees;
-- If phone is NULL → try mobile → try email → fallback to 'No Contact'

-- COALESCE for safe calculations
SELECT
    employee_name,
    base_salary + COALESCE(bonus, 0) + COALESCE(commission, 0) AS total_comp
FROM employees;
-- Without COALESCE: if bonus is NULL, total_comp becomes NULL

-- NULLIF: Returns NULL if the two values are equal
SELECT NULLIF(actual_count, 0)  -- Returns NULL if actual_count is 0
FROM inventory;
-- Useful to prevent division-by-zero:
SELECT total / NULLIF(count, 0) AS average  -- Returns NULL instead of error
FROM summary;

-- IFNULL (MySQL) / ISNULL (SQL Server): Two-argument COALESCE
SELECT IFNULL(phone, 'N/A') FROM employees;        -- MySQL
SELECT ISNULL(phone, 'N/A') FROM employees;        -- SQL Server
SELECT COALESCE(phone, 'N/A') FROM employees;      -- Standard SQL (use this)

DELETE FROM orders WHERE order_date < '2020-01-01';  -- Remove old orders
TRUNCATE TABLE temp_staging;                          -- Empty staging table
DROP TABLE IF EXISTS old_backup;                      -- Remove table entirely

-- WHERE filters rows, HAVING filters groups
SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE status = 'active'          -- Filter rows BEFORE grouping
GROUP BY department
HAVING COUNT(*) > 5;             -- Filter groups AFTER grouping

-- UNION ALL is almost always what you want in data pipelines
SELECT name FROM employees_us
UNION ALL
SELECT name FROM employees_eu;

-- UNION only when dedup is needed
SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;

CREATE TABLE users (
    country_code  CHAR(2),        -- Always exactly 2 chars: 'US', 'IN', 'UK'
    email         VARCHAR(255)    -- Could be 5 chars or 200 chars
);

CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,             -- One per table, no NULLs
    email         VARCHAR(255) UNIQUE,         -- Must be unique, but can be NULL
    ssn           VARCHAR(11) UNIQUE           -- Another unique constraint
);

-- Regular View (no data stored)
CREATE VIEW active_customers AS
SELECT customer_id, name, email
FROM customers
WHERE status = 'active';

-- Materialized View (PostgreSQL)
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(total) AS revenue
FROM orders
GROUP BY 1;

-- Must manually refresh:
REFRESH MATERIALIZED VIEW monthly_sales;

-- Stored Procedure (PostgreSQL)
CREATE PROCEDURE transfer_funds(sender INT, receiver INT, amount DECIMAL)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = sender;
    UPDATE accounts SET balance = balance + amount WHERE id = receiver;
    COMMIT;
END;
$$;

CALL transfer_funds(101, 202, 5000);

-- Function (PostgreSQL)
CREATE FUNCTION get_full_name(first_name TEXT, last_name TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN first_name || ' ' || last_name;
END;
$$ LANGUAGE plpgsql;

SELECT get_full_name(first_name, last_name) FROM employees;

-- Audit trigger: Log every salary change
CREATE TRIGGER log_salary_change
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_at)
    VALUES (OLD.employee_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
END;

-- Temp Table
CREATE TEMPORARY TABLE high_value_orders AS
SELECT * FROM orders WHERE total > 10000;
-- Can now run multiple queries against it, add indexes, etc.

-- CTE (preferred for interview coding)
WITH high_value AS (
    SELECT * FROM orders WHERE total > 10000
),
customer_totals AS (
    SELECT customer_id, SUM(total) AS total_spent
    FROM high_value
    GROUP BY customer_id
)
SELECT * FROM customer_totals WHERE total_spent > 50000;

-- Subquery (avoid for complex logic — hard to read)
SELECT *
FROM (
    SELECT customer_id, SUM(total) AS total_spent
    FROM orders
    WHERE total > 10000
    GROUP BY customer_id
) sub
WHERE total_spent > 50000;

ACID:    Atomicity (all-or-nothing) | Consistency (valid state) | Isolation (no interference) | Durability (survives crash)

ISOLATION LEVELS (low → high protection):
  READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE

NORMAL FORMS:
  1NF: Atomic values, no repeating groups
  2NF: 1NF + no partial dependencies
  3NF: 2NF + no transitive dependencies

INDEX RULES:
  Create: High cardinality + used in WHERE/JOIN + read-heavy table
  Skip:   Low cardinality + write-heavy table + small table

NULL RULES:
  NULL = NULL → NULL (not TRUE!)
  Use IS NULL, never = NULL
  COUNT(*) counts NULLs, COUNT(col) skips them
  AVG skips NULLs (changes denominator!)
  NOT IN with NULLs → returns NOTHING. Use NOT EXISTS.

QUICK PAIRS:
  DELETE (rows, logged) vs TRUNCATE (all rows, fast) vs DROP (table gone)
  WHERE (before GROUP BY) vs HAVING (after GROUP BY)
  UNION (dedup) vs UNION ALL (keep all — faster, default choice)
  View (saved query) vs Materialized View (saved result)
  CTE (readable, single query) vs Temp Table (persistent, indexable)

-- 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

-- TEMPLATE: Gaps & Islands
-- Find all consecutive date sequences per user/entity

WITH activity AS (
    -- Step 1: Get one row per user per active date (deduplicate if needed)
    SELECT DISTINCT
        user_id,
        activity_date
    FROM events
),

numbered AS (
    -- Step 2: Assign row number PER USER ordered by date
    SELECT
        user_id,
        activity_date,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY activity_date
        ) AS rn
    FROM activity
),

islands AS (
    -- Step 3: The KEY TRICK — subtract row number from date
    -- Consecutive dates → same (date - rn) value = same island group
    SELECT
        user_id,
        activity_date,
        rn,
        -- This creates a constant "group key" for each consecutive run:
        (activity_date - INTERVAL '1 day' * (rn - 1)) AS island_group
        -- In Hive: DATE_SUB(activity_date, rn - 1) AS island_group
        -- In Spark: DATE_SUB(activity_date, rn - 1) AS island_group
    FROM numbered
)

-- Step 4: Aggregate each island
SELECT
    user_id,
    MIN(activity_date) AS streak_start,
    MAX(activity_date) AS streak_end,
    COUNT(*) AS streak_length
FROM islands
GROUP BY user_id, island_group
ORDER BY user_id, streak_start;

WITH deduplicated AS (
    -- Remove duplicate logins on same day for same user
    SELECT DISTINCT user_id, login_date
    FROM logins
),

numbered AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY login_date
        ) AS rn
    FROM deduplicated
),

islands AS (
    SELECT
        user_id,
        login_date,
        -- THE TRICK: consecutive dates minus sequential row numbers = constant
        (login_date - CAST(rn AS INT)) AS island_key
        -- Postgres: login_date - rn  (integer subtraction from date)
        -- Hive/Spark: DATE_SUB(login_date, rn)
    FROM numbered
),

streaks AS (
    SELECT
        user_id,
        MIN(login_date) AS streak_start,
        MAX(login_date) AS streak_end,
        COUNT(*) AS streak_length
    FROM islands
    GROUP BY user_id, island_key
),

ranked_streaks AS (
    SELECT
        user_id,
        streak_start,
        streak_end,
        streak_length,
        RANK() OVER (ORDER BY streak_length DESC) AS streak_rank
    FROM streaks
)

SELECT user_id, streak_start, streak_end, streak_length
FROM ranked_streaks
WHERE streak_rank <= 3
ORDER BY streak_length DESC;

WITH price_direction AS (
    SELECT
        stock_symbol,
        price_date,
        closing_price,
        LAG(closing_price) OVER (
            PARTITION BY stock_symbol
            ORDER BY price_date
        ) AS prev_price,
        -- Flag: 1 if price increased from yesterday, 0 otherwise
        CASE WHEN closing_price > LAG(closing_price) OVER (
            PARTITION BY stock_symbol ORDER BY price_date
        ) THEN 1 ELSE 0 END AS is_increase
    FROM stock_prices
),

increases_only AS (
    -- Keep only the increasing-price rows
    SELECT stock_symbol, price_date
    FROM price_direction
    WHERE is_increase = 1
),

numbered AS (
    SELECT
        stock_symbol,
        price_date,
        ROW_NUMBER() OVER (PARTITION BY stock_symbol ORDER BY price_date) AS rn
    FROM increases_only
),

islands AS (
    SELECT
        stock_symbol,
        price_date,
        (price_date - CAST(rn AS INT)) AS island_key
    FROM numbered
),

streaks AS (
    SELECT
        stock_symbol,
        MIN(price_date) AS streak_start,
        MAX(price_date) AS streak_end,
        COUNT(*) AS streak_length
    FROM islands
    GROUP BY stock_symbol, island_key
)

SELECT stock_symbol, streak_start, streak_end, streak_length
FROM streaks
WHERE streak_length >= 3
ORDER BY streak_length DESC;

-- TEMPLATE: Sessionization (create session_id from events)

WITH events_with_prev AS (
    SELECT
        user_id,
        event_time,
        event_type,
        -- Get the previous event time for this user
        LAG(event_time) OVER (
            PARTITION BY user_id
            ORDER BY event_time
        ) AS prev_event_time
    FROM events
),

session_flags AS (
    SELECT
        user_id,
        event_time,
        event_type,
        -- Flag: 1 = start of a NEW session (gap > 30 min OR first event)
        CASE
            WHEN prev_event_time IS NULL THEN 1                       -- first event ever
            WHEN event_time - prev_event_time > INTERVAL '30 minutes' THEN 1  -- gap > threshold
            ELSE 0
        END AS is_new_session
    FROM events_with_prev
),

sessions AS (
    SELECT
        user_id,
        event_time,
        event_type,
        is_new_session,
        -- Cumulative sum of new_session flags = session_id per user!
        SUM(is_new_session) OVER (
            PARTITION BY user_id
            ORDER BY event_time
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS session_id
    FROM session_flags
)

-- Now you have session_id on every event. Use it for aggregation:
SELECT
    user_id,
    session_id,
    MIN(event_time) AS session_start,
    MAX(event_time) AS session_end,
    COUNT(*) AS events_in_session,
    MAX(event_time) - MIN(event_time) AS session_duration
FROM sessions
GROUP BY user_id, session_id
ORDER BY user_id, session_id;

WITH events_ordered AS (
    SELECT
        user_id,
        event_id,
        event_time,
        event_type,
        LAG(event_time) OVER (
            PARTITION BY user_id
            ORDER BY event_time
        ) AS prev_event_time
    FROM user_events
),

new_session_flags AS (
    SELECT
        user_id,
        event_id,
        event_time,
        event_type,
        CASE
            WHEN prev_event_time IS NULL THEN 1
            WHEN EXTRACT(EPOCH FROM (event_time - prev_event_time)) / 60 > 30 THEN 1
            -- Hive/Spark: (UNIX_TIMESTAMP(event_time) - UNIX_TIMESTAMP(prev_event_time)) / 60 > 30
            ELSE 0
        END AS new_session
    FROM events_ordered
)

SELECT
    user_id,
    event_id,
    event_time,
    event_type,
    SUM(new_session) OVER (
        PARTITION BY user_id
        ORDER BY event_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_id   -- starts at 1 for first session, increments at each new session
FROM new_session_flags
ORDER BY user_id, event_time;

-- TEMPLATE: Deduplication — keep most recent record per key
WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY unique_key_col     -- "what defines a duplicate?"
            ORDER BY updated_at DESC        -- "which one to keep?" (DESC = latest first)
        ) AS rn
    FROM source_table
)

SELECT * EXCEPT (rn)   -- or SELECT col1, col2... (exclude the rn column)
FROM ranked
WHERE rn = 1;          -- keep only the first row (= latest record) per key

-- ⚠️ ROW_NUMBER always gives unique ranks (no ties)
-- → exactly 1 row per partition will have rn = 1
-- ⚠️ RANK() could give multiple rows with rank=1 if tie on updated_at → unsafe for dedup

WITH latest_record AS (
    SELECT
        customer_id,
        name,
        email,
        address,
        modified_at,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY modified_at DESC    -- latest timestamp first → rn=1 is most recent
        ) AS rn
    FROM customer_cdc
)

SELECT customer_id, name, email, address, modified_at
FROM latest_record
WHERE rn = 1;

-- ⚠️ In production Databricks/Delta Lake:
-- MERGE INTO customer_dim USING (SELECT ... WHERE rn=1) ON customer_id MATCH
-- This is the SCD Type 1 (overwrite) pattern

-- Approach 1: Find the emails that have duplicates
SELECT
    email,
    COUNT(DISTINCT user_id) AS account_count
FROM users
GROUP BY email
HAVING COUNT(DISTINCT user_id) > 1;

-- Approach 2: Return ALL user_ids that share an email with another user
SELECT u.user_id, u.email
FROM users u
WHERE u.email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(DISTINCT user_id) > 1
)
ORDER BY u.email, u.user_id;

-- TEMPLATE: Self-Join (two aliases for same table)

-- Pattern A: Hierarchy (employee → manager)
SELECT
    e.name AS employee_name,
    e.salary AS employee_salary,
    m.name AS manager_name,
    m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id   -- join condition defines relationship
WHERE e.salary > m.salary;                          -- filter condition

-- Pattern B: Finding pairs (avoid duplicates with < operator)
SELECT
    t1.id AS id1,
    t2.id AS id2,
    t1.col AS col1,
    t2.col AS col2
FROM table t1
JOIN table t2
    ON t1.shared_key = t2.shared_key    -- what links them
    AND t1.id < t2.id                   -- prevent (A,B) AND (B,A), prevent self-pairs
WHERE ABS(t1.value - t2.value) <= 5;   -- similarity condition

SELECT
    e.name AS employee_name,
    e.salary AS employee_salary,
    m.name AS manager_name,
    m.salary AS manager_salary
FROM employees e
JOIN employees m
    ON e.manager_id = m.employee_id    -- link employee to their manager
WHERE e.salary > m.salary              -- filter: only employees earning more
ORDER BY e.salary DESC;

-- ⚠️ TRAP: Don't forget to handle NULLs!
--   Top-level managers have manager_id = NULL → JOIN filters them out (correct!)
--   Use INNER JOIN → rows without a manager are excluded automatically

WITH private_rsvp AS (
    -- Step 1: Only private events
    SELECT user_id, event_id
    FROM event_rsvp
    WHERE event_type = 'private'
),

shared_events AS (
    -- Step 2: Self-join to find users who attended same events
    SELECT
        r1.user_id AS user_1,
        r2.user_id AS user_2,
        COUNT(r1.event_id) AS shared_events
    FROM private_rsvp r1
    JOIN private_rsvp r2
        ON r1.event_id = r2.event_id      -- same event
        AND r1.user_id < r2.user_id        -- avoid (A,B) and (B,A), avoid self-pairs
    GROUP BY r1.user_id, r2.user_id
    HAVING COUNT(r1.event_id) >= 2         -- attended 2+ same events
),

existing_friends AS (
    -- Step 3: Normalize friendships table (ensure both directions covered)
    SELECT user_id_1 AS u1, user_id_2 AS u2 FROM friendships
    UNION
    SELECT user_id_2, user_id_1 FROM friendships
)

-- Step 4: Exclude pairs who are already friends
SELECT s.user_1, s.user_2, s.shared_events
FROM shared_events s
LEFT JOIN existing_friends f
    ON s.user_1 = f.u1 AND s.user_2 = f.u2
WHERE f.u1 IS NULL;   -- not already friends

-- TEMPLATE A: Simple combination generation
SELECT
    a.col1,
    b.col2
FROM table_a a
CROSS JOIN table_b b;   -- generates every row of a paired with every row of b

-- TEMPLATE B: Self-cross-join for pairs (e.g., tournament matchups)
SELECT
    t1.team_name AS home_team,
    t2.team_name AS away_team
FROM teams t1
CROSS JOIN teams t2
WHERE t1.team_id < t2.team_id;   -- prevents (A vs B) AND (B vs A), prevents self-matches

-- TEMPLATE C: Fill gaps — generate full grid then LEFT JOIN to actuals
WITH all_dates AS (
    SELECT generate_series(
        '2024-01-01'::date,
        '2024-12-31'::date,
        '1 day'::interval
    )::date AS date_val
    -- Hive: no recursive CTE; use a prebuilt date dimension or generate dates in ETL
    -- Spark 4.1+: recursive CTE is available; `EXPLODE(SEQUENCE(...))` works without recursion
),
all_products AS (SELECT DISTINCT product_id FROM products)

SELECT
    d.date_val,
    p.product_id,
    COALESCE(s.revenue, 0) AS revenue   -- 0 for days with no sales
FROM all_dates d
CROSS JOIN all_products p
LEFT JOIN daily_sales s
    ON d.date_val = s.sale_date
    AND p.product_id = s.product_id;

SELECT
    t1.topping_name AS topping_1,
    t2.topping_name AS topping_2,
    t3.topping_name AS topping_3,
    t1.ingredient_cost + t2.ingredient_cost + t3.ingredient_cost AS total_cost
FROM toppings t1
JOIN toppings t2 ON t1.topping_name < t2.topping_name   -- ensures t1 < t2 < t3 alphabetically
JOIN toppings t3 ON t2.topping_name < t3.topping_name   -- avoids all duplicate combos
ORDER BY total_cost, topping_1, topping_2, topping_3;

-- ⚠️ Using < on the name column (lexicographic order) ensures:
--   (Basil, Mushroom, Peppers) appears but NOT (Mushroom, Basil, Peppers)
--   This gives exactly C(n,3) = n!/3!(n-3)! unique combinations

-- TEMPLATE: Market Basket — find co-occurring item pairs

WITH item_pairs AS (
    SELECT
        o1.product_id AS product_1,
        o2.product_id AS product_2,
        COUNT(DISTINCT o1.order_id) AS co_purchase_count
    FROM order_items o1
    JOIN order_items o2
        ON o1.order_id = o2.order_id        -- same order
        AND o1.product_id < o2.product_id    -- avoid (A,B) AND (B,A), avoid (A,A)
    GROUP BY o1.product_id, o2.product_id
    ORDER BY co_purchase_count DESC
)

SELECT
    p1.product_name AS product_1,
    p2.product_name AS product_2,
    ip.co_purchase_count
FROM item_pairs ip
JOIN products p1 ON ip.product_1 = p1.product_id
JOIN products p2 ON ip.product_2 = p2.product_id
ORDER BY co_purchase_count DESC
LIMIT 5;   -- top 5 pairs

WITH product_pairs AS (
    SELECT
        t1.product_id AS product_1_id,
        t2.product_id AS product_2_id,
        COUNT(DISTINCT t1.order_id) AS times_bought_together
    FROM transactions t1
    JOIN transactions t2
        ON t1.order_id = t2.order_id        -- same order
        AND t1.product_id < t2.product_id    -- canonical pair, no duplicates
    GROUP BY t1.product_id, t2.product_id
)

SELECT
    p1.product_name AS product_1,
    p2.product_name AS product_2,
    pp.times_bought_together
FROM product_pairs pp
JOIN products p1 ON pp.product_1_id = p1.product_id
JOIN products p2 ON pp.product_2_id = p2.product_id
ORDER BY pp.times_bought_together DESC
LIMIT 5;

WITH total_orders AS (
    SELECT COUNT(DISTINCT order_id) AS total FROM orders
),

product_freq AS (
    -- How often does each product appear in ANY order?
    SELECT product_id, COUNT(DISTINCT order_id) AS freq
    FROM orders
    GROUP BY product_id
),

pair_freq AS (
    -- How often do pairs appear in the SAME order?
    SELECT
        o1.product_id AS prod_1,
        o2.product_id AS prod_2,
        COUNT(DISTINCT o1.order_id) AS pair_count
    FROM orders o1
    JOIN orders o2
        ON o1.order_id = o2.order_id
        AND o1.product_id < o2.product_id
    GROUP BY o1.product_id, o2.product_id
    HAVING COUNT(DISTINCT o1.order_id) > 100
)

SELECT
    pf.prod_1,
    pf.prod_2,
    pf.pair_count,
    -- LIFT = (pair_count / total) / ((freq_A / total) × (freq_B / total))
    --      = pair_count × total / (freq_A × freq_B)
    ROUND(
        pf.pair_count * t.total * 1.0 / (f1.freq * f2.freq),
    3) AS lift
FROM pair_freq pf
JOIN product_freq f1 ON pf.prod_1 = f1.product_id
JOIN product_freq f2 ON pf.prod_2 = f2.product_id
CROSS JOIN total_orders t
ORDER BY lift DESC;

-- Lift interpretation:
-- > 1.0: products bought together MORE than expected → strong association
-- = 1.0: products bought together exactly as expected → no association
-- < 1.0: products bought together LESS than expected → negative association

SELF-JOIN TRAPS:

TRAP 1: Forgetting id < id2 → duplicate pairs
  Without: SELECT * FROM t a JOIN t b ON a.shared = b.shared
  → Returns (A,B) AND (B,A) and (A,A) → pairs are doubled + self-pairs!
  With:    AND a.id < b.id → each pair appears exactly ONCE, no self-pairs

TRAP 2: Manager NULL handling
  Top-level employees have manager_id = NULL
  Inner JOIN on manager_id excludes them (good for "emp earns more than manager")
  Use LEFT JOIN if you want to return ALL employees including top-level

CROSS JOIN TRAPS:

TRAP 3: Accidental cross join on large tables
  FROM table1, table2  ← old syntax for CROSS JOIN (comma = cross join!)
  If you meant to write a regular JOIN and forgot the ON clause → massive data explosion
  Always: write CROSS JOIN explicitly when you mean it, or INNER/LEFT JOIN with ON clause

MARKET BASKET TRAPS:

TRAP 4: COUNT(order_id) vs COUNT(DISTINCT order_id)
  If same product appears twice in one order (quantity > 1), order_id appears twice
  Use COUNT(DISTINCT order_id) to count ORDERS (not line items)
  Use COUNT(*) if you want to count total line item co-occurrences

TRAP 5: Performance on large order tables
  Self-join on order_items can explode: 10M rows × 10M rows = 100 trillion pairs!
  Always add PARTITION to narrow the self-join (e.g., same date or same user)
  Or: pre-filter to only frequent products before the self-join

-- TEMPLATE: Conditional Aggregation
SELECT
    grouping_col,                    -- row identifier (date, user, region)

    -- Count rows matching a condition
    COUNT(CASE WHEN category = 'A' THEN 1 END)          AS count_a,
    SUM(CASE WHEN category = 'A' THEN 1 ELSE 0 END)     AS count_a_v2,  -- same result

    -- Sum values matching a condition
    SUM(CASE WHEN category = 'B' THEN amount ELSE 0 END) AS total_b,

    -- Ratio / percentage
    ROUND(
        100.0 * SUM(CASE WHEN event = 'click' THEN 1 ELSE 0 END)
        / NULLIF(SUM(CASE WHEN event = 'impression' THEN 1 ELSE 0 END), 0),
    2) AS ctr_pct,

    -- Average conditional
    AVG(CASE WHEN is_premium = 1 THEN rating END)        AS avg_premium_rating

FROM source_table
GROUP BY grouping_col;

-- ⚠️ NULLIF(denominator, 0) prevents division-by-zero error
-- ⚠️ COUNT(CASE WHEN ...) counts NULL as 0 — CASE WHEN false returns NULL, not counted
--    SUM(CASE WHEN ... THEN 1 ELSE 0) is safer (explicit 0 for false)

WITH daily_downloads AS (
    SELECT
        ua.date,
        SUM(CASE WHEN pu.user_id IS NOT NULL THEN ua.downloads ELSE 0 END)
            AS paying_downloads,
        SUM(CASE WHEN pu.user_id IS NULL THEN ua.downloads ELSE 0 END)
            AS non_paying_downloads
    FROM user_activity ua
    LEFT JOIN paying_users pu ON ua.user_id = pu.user_id
    GROUP BY ua.date
)

SELECT date, paying_downloads, non_paying_downloads
FROM daily_downloads
WHERE non_paying_downloads > paying_downloads
ORDER BY date;

SELECT
    app_id,
    ROUND(
        100.0
        * SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END)
        / NULLIF(SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END), 0),
    2) AS ctr_percentage
FROM events
WHERE EXTRACT(YEAR FROM timestamp) = 2022
GROUP BY app_id
ORDER BY ctr_percentage DESC;

-- TEMPLATE: Cohort Retention
-- Step 1: Find each user's cohort date (first event)
WITH user_cohorts AS (
    SELECT
        user_id,
        MIN(event_date) AS cohort_date,              -- first ever event = cohort date
        DATE_TRUNC('month', MIN(event_date)) AS cohort_month
    FROM events
    GROUP BY user_id
),

-- Step 2: Join all events back to cohort date to compute "days since cohort"
user_activity AS (
    SELECT
        e.user_id,
        uc.cohort_month,
        -- How many months after their cohort month is this event?
        DATEDIFF('month', uc.cohort_date, e.event_date) AS months_since_cohort
        -- Hive: MONTHS_BETWEEN(e.event_date, uc.cohort_date)
        -- Spark: DATEDIFF(e.event_date, uc.cohort_date) / 30 (approximate)
    FROM events e
    JOIN user_cohorts uc ON e.user_id = uc.user_id
),

-- Step 3: Count users active at each cohort offset
retention AS (
    SELECT
        cohort_month,
        months_since_cohort AS period_offset,
        COUNT(DISTINCT user_id) AS active_users
    FROM user_activity
    GROUP BY cohort_month, months_since_cohort
),

-- Step 4: Get cohort sizes (users in month 0 = all users in that cohort)
cohort_sizes AS (
    SELECT cohort_month, active_users AS cohort_size
    FROM retention
    WHERE period_offset = 0
)

-- Step 5: Compute retention rate per cohort per period
SELECT
    r.cohort_month,
    r.period_offset,
    r.active_users,
    cs.cohort_size,
    ROUND(100.0 * r.active_users / cs.cohort_size, 2) AS retention_rate_pct
FROM retention r
JOIN cohort_sizes cs ON r.cohort_month = cs.cohort_month
ORDER BY r.cohort_month, r.period_offset;

WITH june_active AS (
    SELECT DISTINCT user_id
    FROM user_actions
    WHERE event_date >= '2022-06-01'
      AND event_date < '2022-07-01'
),

july_active AS (
    SELECT DISTINCT user_id
    FROM user_actions
    WHERE event_date >= '2022-07-01'
      AND event_date < '2022-08-01'
)

-- Users who were active in BOTH months = intersection
SELECT COUNT(*) AS monthly_active_users
FROM june_active ja
INNER JOIN july_active jul ON ja.user_id = jul.user_id;

-- Alternative with EXISTS:
SELECT COUNT(DISTINCT j.user_id) AS monthly_active_users
FROM july_active j
WHERE EXISTS (
    SELECT 1 FROM june_active ja WHERE ja.user_id = j.user_id
);

WITH jan_signups AS (
    SELECT
        user_id,
        signup_date,
        signup_date + INTERVAL '7 days' AS day_7_date   -- their specific Day 7
        -- Hive/Spark: DATE_ADD(signup_date, 7) AS day_7_date
    FROM signups
    WHERE signup_date >= '2024-01-01'
      AND signup_date < '2024-02-01'
),

day7_active AS (
    -- Check if each user logged in on their Day 7
    SELECT
        js.user_id,
        MAX(CASE WHEN dl.login_date = js.day_7_date THEN 1 ELSE 0 END) AS was_active_day7
    FROM jan_signups js
    LEFT JOIN daily_logins dl
        ON js.user_id = dl.user_id
        AND dl.login_date = js.day_7_date
    GROUP BY js.user_id
)

SELECT
    COUNT(*) AS cohort_size,
    SUM(was_active_day7) AS day7_actives,
    ROUND(100.0 * SUM(was_active_day7) / COUNT(*), 2) AS day7_retention_rate
FROM day7_active;

-- TEMPLATE: Date Arithmetic Reference

-- 1. Difference between two dates (in days)
DATEDIFF(end_date, start_date)            -- Hive/Spark: returns integer days
end_date - start_date                      -- PostgreSQL: returns integer days
DATEDIFF(day, start_date, end_date)       -- SQL Server / Snowflake

-- 2. Add N days to a date
DATE_ADD(date_col, 7)                     -- Hive/Spark: add 7 days
date_col + INTERVAL '7 days'              -- PostgreSQL
DATEADD(day, 7, date_col)                -- SQL Server / Snowflake

-- 3. Truncate to start of period (useful for monthly/weekly grouping)
DATE_TRUNC('month', date_col)             -- PostgreSQL / Spark / Snowflake
TRUNC(date_col, 'MM')                     -- Hive / Oracle: start of month
DATE_FORMAT(date_col, 'yyyy-MM-01')       -- Hive: manual month truncation

-- 4. Extract components
EXTRACT(YEAR FROM date_col)               -- standard SQL
YEAR(date_col)                            -- Hive/Spark/MySQL shorthand
EXTRACT(QUARTER FROM date_col)            -- quarter number (1-4)

-- 5. Current date
CURRENT_DATE                              -- standard SQL
NOW()                                     -- PostgreSQL / MySQL (includes time)
GETDATE()                                 -- SQL Server
CURRENT_DATE()                            -- Hive/Spark

-- 6. Filter "past N days"
WHERE event_date >= CURRENT_DATE - INTERVAL '90 days'   -- PostgreSQL
WHERE event_date >= DATE_SUB(CURRENT_DATE(), 90)        -- Hive/Spark

-- 7. Convert epoch/unix timestamp
FROM_UNIXTIME(unix_ts)                    -- Hive: unix seconds → datetime
TO_TIMESTAMP(unix_ts)                     -- Snowflake/PostgreSQL

SELECT
    user_id,
    MIN(post_date) AS first_post,
    MAX(post_date) AS last_post,
    DATEDIFF(MAX(post_date), MIN(post_date)) AS days_between
    -- PostgreSQL: MAX(post_date) - MIN(post_date) AS days_between
FROM posts
WHERE post_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY user_id
HAVING COUNT(*) >= 2   -- only users with at least 2 posts
ORDER BY days_between DESC;

SELECT
    e.employee_id,
    e.hire_date,
    DATEDIFF(CURRENT_DATE(), e.hire_date) AS days_employed
    -- PostgreSQL: CURRENT_DATE - e.hire_date
FROM employees e
LEFT JOIN promotions p ON e.employee_id = p.employee_id
WHERE
    DATEDIFF(CURRENT_DATE(), e.hire_date) > 5 * 365   -- 5+ years
    -- PostgreSQL: CURRENT_DATE - e.hire_date > 1825
    AND p.employee_id IS NULL;   -- no record in promotions table

ADVANCED PATTERNS = "RMF"
    R — Recursive CTE: anchor + recursive step (trees, hierarchies, date spines)
    M — Median/Percentile: ROW_NUMBER trick OR PERCENTILE_CONT (built-in)
    F — Funnel: COUNT DISTINCT at each stage → conversion rate between stages

RECURSIVE CTE STRUCTURE:
  WITH RECURSIVE cte AS (
      SELECT ...  ← ANCHOR: the starting point (root / first row)
      UNION ALL
      SELECT ... FROM cte JOIN table ...  ← RECURSIVE: add one more level
      WHERE stopping_condition   ← TERMINATION: when to stop
  )
  SELECT * FROM cte;

MEDIAN TRICK (no built-in MEDIAN):
  "The median is the middle value"
  → Count total rows (N)
  → Assign ROW_NUMBER to each row ordered by value
  → Keep row where rn = (N+1)/2   (odd N)
  → Or AVG of rows where rn IN (N/2, N/2+1)  (even N)
  → PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) is simpler if available!

FUNNEL PATTERN:
  Define stages: signup → verify → first_purchase
  For each stage: COUNT(DISTINCT user_id) who reached that stage
  Conversion: next_stage_count / this_stage_count × 100

-- TEMPLATE: Recursive CTE
WITH RECURSIVE cte_name AS (

    -- ANCHOR MEMBER: base case (starting point)
    -- This runs ONCE and returns the initial rows
    SELECT
        id,
        parent_id,
        name,
        1 AS level            -- depth tracker
    FROM hierarchy_table
    WHERE parent_id IS NULL   -- ← starting condition (root nodes, or specific ID)

    UNION ALL

    -- RECURSIVE MEMBER: add one more level
    -- This references cte_name itself!
    SELECT
        h.id,
        h.parent_id,
        h.name,
        cte.level + 1
    FROM hierarchy_table h
    JOIN cte_name cte
        ON h.parent_id = cte.id   -- each iteration: find children of current level
    WHERE cte.level < 10          -- ← TERMINATION CONDITION (prevent infinite loops!)
)

SELECT * FROM cte_name
ORDER BY level, id;

-- ⚠️ ALWAYS add a termination condition (level < N OR a sentinel value)
-- Without it: infinite loop if there's a cycle in the data!

WITH RECURSIVE org_tree AS (

    -- ANCHOR: direct reports of manager 5
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS level_below_manager
    FROM employees
    WHERE manager_id = 5

    UNION ALL

    -- RECURSIVE: find reports of the reports
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        ot.level_below_manager + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
    WHERE ot.level_below_manager < 20   -- safety limit (company depth)
)

SELECT employee_id, name, level_below_manager
FROM org_tree
ORDER BY level_below_manager, name;

-- PostgreSQL: use generate_series (simplest)
SELECT generate_series(
    '2024-01-01'::date,
    '2024-12-31'::date,
    '1 day'::interval
)::date AS date_val;

-- Spark 4.1+: recursive CTE support starts in this release line
WITH RECURSIVE date_spine(date_val) MAX RECURSION LEVEL 366 AS (
    -- ANCHOR: start date
    SELECT DATE '2024-01-01' AS date_val

    UNION ALL

    -- RECURSIVE: add 1 day each iteration
    SELECT DATE_ADD(date_val, 1)
    FROM date_spine
    WHERE date_val < DATE '2024-12-31'   -- TERMINATION: stop at end date
)

SELECT date_val FROM date_spine;

-- Spark 3.x/4.0 alternative for a date spine: no recursion required
SELECT EXPLODE(SEQUENCE(
    TO_DATE('2024-01-01'),
    TO_DATE('2024-12-31'),
    INTERVAL 1 DAY
)) AS date_val;

-- Hive alternative: select from a maintained calendar dimension
SELECT calendar_date AS date_val
FROM dim_calendar
WHERE calendar_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31';

-- Usage: fill gaps in daily sales report
SELECT
    d.date_val,
    p.product_id,
    COALESCE(s.revenue, 0) AS revenue
FROM date_spine d
CROSS JOIN (SELECT DISTINCT product_id FROM products) p
LEFT JOIN daily_sales s
    ON d.date_val = s.sale_date
    AND p.product_id = s.product_id
ORDER BY d.date_val, p.product_id;

-- PERCENTILE_CONT: continuous interpolation (for medians — interpolates between values)
SELECT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) AS p90_salary,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS q1_salary
FROM employees;

-- Availability: PostgreSQL ✓, Snowflake ✓, Spark SQL ✓, Hive (PERCENTILE_APPROX) partial

-- PERCENTILE_DISC: discrete (returns actual value from the dataset, not interpolated)
SELECT
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) AS exact_median
FROM employees;

-- NTILE: divide into N equal buckets
SELECT
    user_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend) AS quartile   -- 1=lowest, 4=highest
FROM user_spending;

-- Hive approximation:
SELECT PERCENTILE_APPROX(salary, 0.5) AS approx_median FROM employees;

-- MANUAL MEDIAN using ROW_NUMBER
-- Works in ALL databases including Hive

WITH ordered AS (
    SELECT
        value_col,
        ROW_NUMBER() OVER (ORDER BY value_col) AS rn,
        COUNT(*) OVER () AS total_count   -- total rows (same for all rows)
    FROM source_table
)

SELECT AVG(value_col) AS median_value
FROM ordered
WHERE
    rn = (total_count + 1) / 2             -- middle row for odd count
    OR rn = (total_count + 2) / 2;         -- handles even count (avg of 2 middle rows)

-- Example:
-- N=5 (odd): middle = row 3. (5+1)/2=3, (5+2)/2=3 → same row → just 1 value
-- N=6 (even): rows 3 and 4. (6+1)/2=3 (floor), (6+2)/2=4 → two rows → AVG

-- KEY INSIGHT: Can't use simple ROW_NUMBER because data is compressed!
-- Must EXPAND the frequency table first OR use cumulative counts

WITH cumulative AS (
    SELECT
        searches,
        num_users,
        -- Cumulative count up to this row
        SUM(num_users) OVER (ORDER BY searches
                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
            AS cum_count,
        -- Cumulative count BEFORE this row
        SUM(num_users) OVER (ORDER BY searches
                              ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
            AS cum_count_before,
        SUM(num_users) OVER () AS total_users
    FROM search_frequency
)

SELECT ROUND(AVG(searches * 1.0), 1) AS median_searches
FROM cumulative
WHERE
    -- Median rows: total/2 falls within [cum_before, cum_count] range
    COALESCE(cum_count_before, 0) < (total_users + 1) / 2.0
    AND cum_count >= (total_users + 1) / 2.0;
    -- For even totals: both (N/2)th and (N/2+1)th values needed → AVG handles it

WITH user_totals AS (
    SELECT
        user_id,
        SUM(amount) AS total_spend
    FROM user_orders
    WHERE YEAR(order_date) = 2023
    GROUP BY user_id
)

SELECT
    user_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend ASC) AS quartile
    -- quartile 1 = lowest spenders, quartile 4 = highest spenders
FROM user_totals
ORDER BY total_spend;

-- TEMPLATE: Funnel Analysis
-- Each user can be in at most one stage at a time (or multiple, depending on definition)

WITH user_stages AS (
    SELECT
        user_id,
        -- For each stage: 1 if user performed this action, else 0
        MAX(CASE WHEN event_type = 'signup' THEN 1 ELSE 0 END)        AS did_signup,
        MAX(CASE WHEN event_type = 'email_verified' THEN 1 ELSE 0 END) AS did_verify,
        MAX(CASE WHEN event_type = 'first_purchase' THEN 1 ELSE 0 END) AS did_purchase
    FROM events
    GROUP BY user_id
),

funnel_counts AS (
    SELECT
        -- COUNT users who reached each stage
        COUNT(*) AS total_users,
        SUM(did_signup) AS signup_count,
        SUM(did_verify) AS verify_count,
        SUM(did_purchase) AS purchase_count
    FROM user_stages
)

SELECT
    signup_count,
    verify_count,
    ROUND(100.0 * verify_count / NULLIF(signup_count, 0), 2)   AS signup_to_verify_pct,
    purchase_count,
    ROUND(100.0 * purchase_count / NULLIF(verify_count, 0), 2) AS verify_to_purchase_pct,
    ROUND(100.0 * purchase_count / NULLIF(signup_count, 0), 2) AS overall_conversion_pct
FROM funnel_counts;

WITH user_funnel AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_type = 'signup' THEN 1 ELSE 0 END)             AS reached_signup,
        MAX(CASE WHEN event_type = 'email_verified' THEN 1 ELSE 0 END)      AS reached_verify,
        MAX(CASE WHEN event_type = 'profile_completed' THEN 1 ELSE 0 END)   AS reached_profile,
        MAX(CASE WHEN event_type = 'first_purchase' THEN 1 ELSE 0 END)      AS reached_purchase
    FROM events
    GROUP BY user_id
),

stage_counts AS (
    SELECT
        SUM(reached_signup)   AS signup_users,
        SUM(reached_verify)   AS verify_users,
        SUM(reached_profile)  AS profile_users,
        SUM(reached_purchase) AS purchase_users
    FROM user_funnel
)

SELECT
    'Stage 1: Signup'        AS stage, signup_users   AS users, 100.0 AS conversion_from_prev FROM stage_counts
UNION ALL
SELECT 'Stage 2: Email Verified', verify_users,
    ROUND(100.0 * verify_users   / NULLIF(signup_users, 0), 2)   FROM stage_counts
UNION ALL
SELECT 'Stage 3: Profile Completed', profile_users,
    ROUND(100.0 * profile_users  / NULLIF(verify_users, 0), 2)   FROM stage_counts
UNION ALL
SELECT 'Stage 4: First Purchase', purchase_users,
    ROUND(100.0 * purchase_users / NULLIF(profile_users, 0), 2)  FROM stage_counts;

SELECT
    ROUND(
        SUM(CASE WHEN action = 'answered' THEN 1.0 ELSE 0 END)
        / COUNT(DISTINCT user_id),
    2) AS activation_rate
FROM emails;

-- More explicit version:
WITH user_status AS (
    SELECT
        user_id,
        MAX(CASE WHEN action = 'answered' THEN 1 ELSE 0 END) AS activated
    FROM emails
    GROUP BY user_id
)
SELECT ROUND(AVG(activated * 1.0), 2) AS activation_rate FROM user_status;

SELECT cust_id FROM orders_2025
UNION       -- removes dupes
SELECT cust_id FROM orders_2026;
-- Result: unique cust_ids who ordered in EITHER year

SELECT cust_id FROM orders_2025
UNION ALL   -- keeps dupes
SELECT cust_id FROM orders_2026;
-- Result: all cust_ids (a customer in both years appears twice)

-- Using DENSE_RANK (treats ties as same rank, returns ONE "3rd highest")
SELECT DISTINCT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
    FROM employees
) t WHERE rk = 3;

-- GROUP BY: collapses
SELECT dept, AVG(salary)
FROM employees
GROUP BY dept;
-- Output: 1 row per dept
-- sales | 55000
-- eng   | 75000

-- Window: keeps all rows
SELECT name, dept, salary, AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;
-- Output: every employee + their dept avg
-- Alice | sales | 50000 | 55000
-- Bob   | sales | 60000 | 55000
-- Carol | eng   | 80000 | 75000
-- Dave  | eng   | 70000 | 75000

DELETE    → removes ROWS (can use WHERE, logged, slow, rollback-able)
TRUNCATE  → removes ALL rows (no WHERE, minimal logging, fast, usually not rollback-able)
DROP      → removes ENTIRE TABLE (schema + data + indexes)

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
--                      ↑ runs once, returns single value

SELECT name, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.dept = e1.dept   -- ← references OUTER query e1
                              -- runs once for EACH row of e1
);

-- Subquery (messy for complex cases)
SELECT name FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept);

-- CTE (clearer)
WITH dept_avg AS (
    SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept
)
SELECT e.name FROM employees e
JOIN dept_avg d ON e.dept = d.dept
WHERE e.salary > d.avg_sal;

-- Window function (cleanest — single pass)
SELECT name FROM (
    SELECT name, salary, AVG(salary) OVER (PARTITION BY dept) AS avg_sal
    FROM employees
) t WHERE salary > avg_sal;

-- SETUP
CREATE TABLE orders (
    cust_id INT,
    amount  DECIMAL,
    status  VARCHAR(20)
);

INSERT INTO orders VALUES
    (1, 100, 'paid'),
    (1, 200, 'paid'),
    (2,  50, 'cancelled'),
    (2, 300, 'paid'),
    (3, 150, 'paid'),
    (3,  80, 'cancelled');

-- STEP 1: just look at raw data
SELECT * FROM orders;
-- ┌─────────┬────────┬───────────┐
-- │ cust_id │ amount │  status   │
-- ├─────────┼────────┼───────────┤
-- │    1    │  100   │  paid     │
-- │    1    │  200   │  paid     │
-- │    2    │   50   │  cancelled│
-- │    2    │  300   │  paid     │
-- │    3    │  150   │  paid     │
-- │    3    │   80   │  cancelled│
-- └─────────┴────────┴───────────┘

-- STEP 2: WHERE filters rows BEFORE GROUP BY
SELECT cust_id, SUM(amount) AS total
FROM orders
WHERE status = 'paid'        -- ← filters row-by-row FIRST
GROUP BY cust_id;
-- ┌─────────┬───────┐
-- │ cust_id │ total │
-- ├─────────┼───────┤
-- │    1    │  300  │    ← 100+200 (only paid rows)
-- │    2    │  300  │    ← 300 only (50 cancelled filtered out by WHERE)
-- │    3    │  150  │    ← 150 only (80 cancelled filtered out)
-- └─────────┴───────┘

-- STEP 3: HAVING filters GROUPS AFTER GROUP BY
SELECT cust_id, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY cust_id
HAVING SUM(amount) > 200;    -- ← filters aggregated result AFTER
-- ┌─────────┬───────┐
-- │ cust_id │ total │
-- ├─────────┼───────┤
-- │    1    │  300  │    ← kept (300 > 200)
-- │    2    │  300  │    ← kept (300 > 200)
-- └─────────┴───────┘       ← cust_id=3 dropped (150 not > 200)

-- STEP 4: What breaks if you try to use aggregate in WHERE?
SELECT cust_id, SUM(amount)
FROM orders
WHERE SUM(amount) > 200       -- ← ❌ ERROR
GROUP BY cust_id;

-- ERROR: aggregate functions are not allowed in WHERE
-- Fix: use HAVING instead

-- SETUP
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS orders;

CREATE TABLE customers (id INT, name VARCHAR(20));
CREATE TABLE orders    (id INT, order_num INT);

INSERT INTO customers VALUES (1,'Alice'),(2,'Bob'),(3,'Carol');
INSERT INTO orders    VALUES (1, 101), (1, 102), (4, 103);

-- Look at both tables
SELECT * FROM customers;
-- id | name
-- 1  | Alice
-- 2  | Bob
-- 3  | Carol
SELECT * FROM orders;
-- id | order_num
-- 1  | 101
-- 1  | 102
-- 4  | 103          ← id=4 is NOT in customers!

-- STEP 1: INNER JOIN — only matches
SELECT c.name, o.order_num
FROM customers c
INNER JOIN orders o ON c.id = o.id;
-- Alice | 101
-- Alice | 102
-- ← Bob, Carol missing (no orders)
-- ← order 103 missing (no matching customer)

-- STEP 2: LEFT JOIN — all customers, orders if present
SELECT c.name, o.order_num
FROM customers c
LEFT JOIN orders o ON c.id = o.id;
-- Alice | 101
-- Alice | 102
-- Bob   | NULL     ← Bob kept, no order
-- Carol | NULL     ← Carol kept, no order
-- ← order 103 still missing (it's RIGHT-side)

-- STEP 3: FULL OUTER JOIN — everything
SELECT c.name, o.order_num
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.id;
-- Alice | 101
-- Alice | 102
-- Bob   | NULL
-- Carol | NULL
-- NULL  | 103      ← the orphan order appears!

-- STEP 4: "Find customers with NO orders" (anti-join pattern)
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.id
WHERE o.id IS NULL;          -- ← the magic: WHERE rhs IS NULL
-- Bob
-- Carol

-- SETUP
DROP TABLE IF EXISTS salaries;
CREATE TABLE salaries (name VARCHAR(20), dept VARCHAR(20), salary INT);
INSERT INTO salaries VALUES
    ('Alice',  'eng',   90000),
    ('Bob',    'eng',   75000),
    ('Carol',  'eng',   75000),
    ('Dave',   'eng',   60000),
    ('Eve',    'sales', 80000),
    ('Frank',  'sales', 70000);

-- STEP 1: See all 3 ranking functions side-by-side
SELECT name, dept, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
       RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rk,
       DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS drk
FROM salaries;
-- ┌───────┬───────┬────────┬────┬────┬─────┐
-- │ name  │ dept  │ salary │ rn │ rk │ drk │
-- ├───────┼───────┼────────┼────┼────┼─────┤
-- │ Alice │ eng   │ 90000  │ 1  │ 1  │  1  │
-- │ Bob   │ eng   │ 75000  │ 2  │ 2  │  2  │  ← tied
-- │ Carol │ eng   │ 75000  │ 3  │ 2  │  2  │  ← tied (RANK same, DENSE same)
-- │ Dave  │ eng   │ 60000  │ 4  │ 4  │  3  │  ← RANK skips to 4, DENSE → 3
-- │ Eve   │ sales │ 80000  │ 1  │ 1  │  1  │
-- │ Frank │ sales │ 70000  │ 2  │ 2  │  2  │
-- └───────┴───────┴────────┴────┴────┴─────┘

-- STEP 2: Running totals with SUM() OVER
SELECT name, dept, salary,
       SUM(salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS running_total
FROM salaries;
-- Alice │ eng   │ 90000 │  90000       ← 90k
-- Bob   │ eng   │ 75000 │ 165000       ← 90+75 = 165k  (same for Carol, tied)
-- Carol │ eng   │ 75000 │ 240000       ← wait, why? See note below
-- Dave  │ eng   │ 60000 │ 300000
--
-- 💡 Why Bob+Carol give DIFFERENT running totals despite tied salary?
--    Default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
--    With ORDER BY salary DESC, ties are "one range" — both get the
--    same running total in SOME databases, different in others.
--    Safe fix: use ROWS instead of RANGE.

-- STEP 3: LAG / LEAD — compare rows to neighbors
SELECT name, salary,
       LAG(salary)  OVER (ORDER BY salary DESC) AS prev_sal,
       LEAD(salary) OVER (ORDER BY salary DESC) AS next_sal,
       salary - LAG(salary) OVER (ORDER BY salary DESC) AS diff_from_prev
FROM salaries;
-- Alice │ 90000 │  NULL │ 80000 │  NULL    ← no previous row
-- Eve   │ 80000 │ 90000 │ 75000 │ -10000   ← 10k less than prev
-- Bob   │ 75000 │ 80000 │ 75000 │  -5000
-- Carol │ 75000 │ 75000 │ 70000 │      0
-- Frank │ 70000 │ 75000 │ 60000 │  -5000
-- Dave  │ 60000 │ 70000 │  NULL │ -10000

You WRITE queries in this order:            But the DB EXECUTES in this order:
──────────────────────────                   ─────────────────────────────────
SELECT        ← 6                            1. FROM / JOIN      ← load tables
FROM          ← 1                            2. WHERE            ← filter rows
JOIN          ← 2                            3. GROUP BY         ← aggregate
WHERE         ← 3                            4. HAVING           ← filter groups
GROUP BY      ← 4                            5. SELECT           ← pick columns
HAVING        ← 5                            6. DISTINCT         ← dedup
ORDER BY      ← 7                            7. ORDER BY         ← sort
LIMIT         ← 8                            8. LIMIT            ← cap rows

THIS IS WHY:
  ❌  SELECT name AS n, ... ORDER BY n        works (ORDER BY runs AFTER SELECT)
  ❌  SELECT ... WHERE n > 5                  fails (WHERE runs BEFORE SELECT alias)
  ❌  SELECT SUM(x) ... WHERE SUM(x) > 10     fails (aggregate not ready in WHERE)
  ✅  SELECT SUM(x) ... HAVING SUM(x) > 10    works (HAVING runs AFTER aggregation)

SELECT c.name, o.order_num
FROM customers c JOIN orders o ON c.id = o.id;

Step-by-step (nested loop):
────────────────────────────

for each row IN customers:         ← outer loop (3 customers)
    for each row IN orders:        ← inner loop (3 orders)
        if c.id == o.id:           ← check match condition
            OUTPUT (c.name, o.order_num)

Iterations:
  (Alice,1) × (1,101) ✅ → EMIT Alice|101
  (Alice,1) × (1,102) ✅ → EMIT Alice|102
  (Alice,1) × (4,103) ❌ → skip
  (Bob,  2) × (1,101) ❌ → skip
  (Bob,  2) × (1,102) ❌ → skip
  (Bob,  2) × (4,103) ❌ → skip
  (Carol,3) × (1,101) ❌ → skip
  (Carol,3) × (1,102) ❌ → skip
  (Carol,3) × (4,103) ❌ → skip

Result: 2 rows emitted.

💡 On large tables, this is O(N × M) — too slow.
   That's why the DB uses HASH JOIN or MERGE JOIN instead:
   - Hash Join:  build a hash table on smaller side (O(N+M))
   - Merge Join: sort both, merge (O(N log N + M log M))

SELECT * FROM t WHERE col = NULL;  -- ← ❌ returns ZERO rows ALWAYS
SELECT * FROM t WHERE col IS NULL; -- ← ✅ correct

SELECT 5 / 2;          -- PostgreSQL: 2 (integer division!)
SELECT 5.0 / 2;        -- 2.5 (one operand is decimal)
SELECT CAST(5 AS DECIMAL) / 2;  -- 2.5

-- ❌ DOESN'T WORK in most databases:
SELECT EXTRACT(YEAR FROM sale_date) AS yr, SUM(amount)
FROM sales
GROUP BY yr;          -- ← can't use alias 'yr' here in standard SQL

-- ✅ WORKS:
SELECT EXTRACT(YEAR FROM sale_date) AS yr, SUM(amount)
FROM sales
GROUP BY EXTRACT(YEAR FROM sale_date);  -- ← repeat the expression
-- OR use column position:
GROUP BY 1;           -- PostgreSQL allows this

WHERE name LIKE 'alice%'   -- case-sensitive (no match if name='Alice')
WHERE name ILIKE 'alice%'  -- case-insensitive (PostgreSQL only)
WHERE LOWER(name) LIKE 'alice%'  -- works everywhere

SELECT COUNT(DISTINCT user_id) FROM events;  -- slow on 1B rows

-- ❌ BAD: runs a subquery PER ROW of employees
SELECT name, (SELECT name FROM dept WHERE dept.id = e.dept_id) AS dept_name
FROM employees e;

-- ✅ GOOD: single JOIN
SELECT e.name, d.name AS dept_name
FROM employees e JOIN dept d ON e.dept_id = d.id;

-- Using DENSE_RANK — handles ties correctly
SELECT DISTINCT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
    FROM employees
) t
WHERE rk = 2;

-- Alternative: LIMIT + OFFSET with DISTINCT
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

SELECT user_id, MIN(activity_date) AS streak_start,
                MAX(activity_date) AS streak_end,
                COUNT(*) AS streak_days
FROM (
    SELECT user_id, activity_date,
           activity_date - INTERVAL '1 day' * ROW_NUMBER()
               OVER (PARTITION BY user_id ORDER BY activity_date) AS grp
    FROM user_activity
) t
GROUP BY user_id, grp
HAVING COUNT(*) >= 2;

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
    FROM users
)
SELECT * FROM ranked WHERE rn = 1;