WITH ranked AS (
    SELECT *, DENSE_RANK() OVER (PARTITION BY grp ORDER BY val DESC) AS rnk
    FROM tbl
)
SELECT * FROM ranked WHERE rnk <= N;
-- ROW_NUMBER: unique, no ties | RANK: skips | DENSE_RANK: no skip ← use for Top-N

SUM(val) OVER (PARTITION BY grp ORDER BY date_col
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total

AVG(val) OVER (PARTITION BY grp ORDER BY date_col
               ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7day_avg

LAG(val, 1) OVER (PARTITION BY grp ORDER BY date_col) AS prev_val
LEAD(val, 1) OVER (PARTITION BY grp ORDER BY date_col) AS next_val
-- First row LAG = NULL, Last row LEAD = NULL
-- pct_change = (val - prev_val) / NULLIF(prev_val, 0) × 100

-- TRICK: consecutive dates - sequential row numbers = CONSTANT for each island!
WITH deduped AS (SELECT DISTINCT user_id, activity_date FROM events),
numbered AS (
    SELECT user_id, activity_date,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) AS rn
    FROM deduped
),
islands AS (
    SELECT user_id, activity_date,
           DATE_SUB(activity_date, rn) AS island_key   -- Hive/Spark
           -- PostgreSQL: activity_date - rn AS island_key
    FROM numbered
)
SELECT user_id, MIN(activity_date) AS start, MAX(activity_date) AS end, COUNT(*) AS len
FROM islands
GROUP BY user_id, island_key;

WITH flagged AS (
    SELECT *, LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_t,
           CASE WHEN prev_t IS NULL OR (event_time - prev_t) > INTERVAL '30 min'
                THEN 1 ELSE 0 END AS new_session
    FROM events
)
SELECT *, SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
          AS session_id
FROM flagged;

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY unique_key ORDER BY updated_at DESC) AS rn
    FROM table_with_dupes
)
SELECT * FROM ranked WHERE rn = 1;  -- always use ROW_NUMBER not RANK (RANK can have ties!)

-- Hierarchy:
SELECT e.name, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.id
-- Pairs (no duplicates):
SELECT t1.col, t2.col FROM t t1 JOIN t t2 ON t1.shared = t2.shared AND t1.id < t2.id

-- All combinations:
SELECT a.col, b.col FROM table_a a CROSS JOIN table_b b;
-- Round-robin (no self-pairs):
SELECT t1.name, t2.name FROM teams t1 CROSS JOIN teams t2 WHERE t1.id < t2.id;
-- 3-way (pizza toppings):
SELECT t1.name, t2.name, t3.name FROM t t1 JOIN t t2 ON t1.name < t2.name
       JOIN t t3 ON t2.name < t3.name;

SELECT o1.product_id, o2.product_id, COUNT(DISTINCT o1.order_id) AS co_purchases
FROM order_items o1
JOIN order_items o2 ON o1.order_id = o2.order_id AND o1.product_id < o2.product_id
GROUP BY o1.product_id, o2.product_id
ORDER BY co_purchases DESC LIMIT 5;

SELECT date_col,
    SUM(CASE WHEN category = 'A' THEN 1 ELSE 0 END) AS count_a,
    SUM(CASE WHEN category = 'B' THEN amount ELSE 0 END) AS revenue_b,
    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
FROM events GROUP BY date_col;

WITH cohorts AS (SELECT user_id, MIN(event_date) AS cohort_date FROM events GROUP BY user_id),
activity AS (
    SELECT e.user_id, c.cohort_date,
           DATEDIFF(e.event_date, c.cohort_date) AS days_since_cohort  -- Hive/Spark
    FROM events e JOIN cohorts c ON e.user_id = c.user_id
)
SELECT cohort_date,
    COUNT(DISTINCT CASE WHEN days_since_cohort = 0 THEN user_id END) AS day0,
    COUNT(DISTINCT CASE WHEN days_since_cohort = 7 THEN user_id END) AS day7,
    COUNT(DISTINCT CASE WHEN days_since_cohort = 30 THEN user_id END) AS day30
FROM activity GROUP BY cohort_date;

DATEDIFF(end, start)                    -- Hive/Spark: days between dates
DATE_ADD(date, N)                       -- Hive/Spark: add N days
DATE_TRUNC('month', date)               -- Spark/PG: start of month
TRUNC(date, 'MM')                       -- Hive: start of month
WHERE date >= DATE_SUB(CURRENT_DATE(), 90)  -- past 90 days (Hive/Spark)
WHERE date >= CURRENT_DATE - INTERVAL '90 days'  -- PostgreSQL

WITH RECURSIVE cte AS (
    SELECT id, parent_id, name, 1 AS lvl FROM tree WHERE parent_id IS NULL  -- ANCHOR
    UNION ALL
    SELECT t.id, t.parent_id, t.name, c.lvl + 1
    FROM tree t JOIN cte c ON t.parent_id = c.id WHERE c.lvl < 20           -- RECURSIVE + LIMIT
)
SELECT * FROM cte;
-- ⚠️ Always add level limit! ⚠️ Use UNION ALL not UNION
-- ⚠️ Hive doesn't support WITH RECURSIVE → use Spark SQL

-- Option 1: Built-in (PostgreSQL/Snowflake/Spark)
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val) AS median FROM t;

-- Option 2: Manual (works everywhere including Hive)
WITH ranked AS (
    SELECT val,
           ROW_NUMBER() OVER (ORDER BY val) AS rn,
           COUNT(*) OVER () AS n
    FROM t
)
SELECT AVG(val) AS median FROM ranked WHERE rn IN ((n+1)/2, (n+2)/2);

-- Option 3: Quartiles with NTILE
SELECT user_id, spend, NTILE(4) OVER (ORDER BY spend) AS quartile FROM user_totals;

WITH stages AS (
    SELECT user_id,
        MAX(CASE WHEN event='signup' THEN 1 ELSE 0 END)   AS s1,
        MAX(CASE WHEN event='verify' THEN 1 ELSE 0 END)   AS s2,
        MAX(CASE WHEN event='purchase' THEN 1 ELSE 0 END) AS s3
    FROM events GROUP BY user_id
)
SELECT SUM(s1) AS signup, SUM(s2) AS verify, SUM(s3) AS purchase,
    ROUND(100.0*SUM(s2)/NULLIF(SUM(s1),0),2) AS s1_to_s2,
    ROUND(100.0*SUM(s3)/NULLIF(SUM(s2),0),2) AS s2_to_s3
FROM stages;
-- ⚠️ Use MAX(CASE WHEN) per user — counting unique users, not events!