-- 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
        id::TEXT AS path      -- path tracker (optional)
    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,
        cte.path || ' > ' || h.name
    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 path;

-- ⚠️ 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;

-- Hive/Spark: use recursive CTE
WITH RECURSIVE date_spine 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;

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