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