Advanced

-- Create table for salary analysis
CREATE TABLE employee_salaries (  -- => Stores employee compensation data
    id INTEGER,                    -- => Unique employee identifier
    name TEXT,                     -- => Employee name for reporting
    salary REAL                    -- => Annual salary in dollars
);

-- Generate 100 sample employees with varied salaries
INSERT INTO employee_salaries (id, name, salary)
SELECT
    value,                                              -- => Employee ID from 1-100
    'Employee ' || value,                               -- => Name: "Employee 1", "Employee 2", etc.
    50000 + (value * 1000) + (value % 10) * 2000        -- => Base $50k + progression + variance
                                                         -- => Creates realistic salary distribution ($51k-$168k range)
FROM (
    WITH RECURSIVE nums AS (                            -- => Recursive CTE generates number sequence
        SELECT 1 AS value                               -- => Start at 1
        UNION ALL                                       -- => Combine with next iteration
        SELECT value + 1 FROM nums WHERE value < 100    -- => Increment until 100
    )
    SELECT value FROM nums                              -- => Return sequence 1-100
);

-- Example A: Divide into quartiles using NTILE
SELECT
    name,                                               -- => Employee identifier
    salary,                                             -- => Actual salary value
    NTILE(4) OVER (ORDER BY salary) AS quartile         -- => Assigns quartile group (1-4) based on salary rank
                                                         -- => Divides 100 employees into 4 groups of 25 each
FROM employee_salaries
ORDER BY salary;                                        -- => Sort results low to high for readability
-- => Output shows each employee with their quartile assignment
-- => Q1: Bottom 25% (lowest earners), Q2: 25-50%, Q3: 50-75%, Q4: Top 25% (highest earners)

-- Example B: Calculate quartile boundaries
WITH quartiles AS (                                     -- => CTE assigns quartile to each salary
    SELECT
        salary,                                         -- => Salary value
        NTILE(4) OVER (ORDER BY salary) AS quartile     -- => Quartile assignment (1-4)
    FROM employee_salaries
)
SELECT
    quartile,                                           -- => Quartile number (1, 2, 3, 4)
    MIN(salary) AS min_salary,                          -- => Lowest salary in this quartile
    MAX(salary) AS max_salary                           -- => Highest salary in this quartile
FROM quartiles
GROUP BY quartile                                       -- => Aggregate by quartile group
ORDER BY quartile;                                      -- => Display Q1 through Q4
-- => Output: Salary ranges for each quartile
-- => Example: Q1: $51k-$75k, Q2: $76k-$100k, Q3: $101k-$125k, Q4: $126k-$168k

-- Example C: Calculate percentiles (deciles - 10% buckets)
SELECT
    name,                                               -- => Employee identifier
    salary,                                             -- => Salary value
    NTILE(10) OVER (ORDER BY salary) AS decile          -- => Assigns decile group (1-10)
                                                         -- => Each decile represents 10% of population
FROM employee_salaries
ORDER BY salary;                                        -- => Sort for readability
-- => Divides into 10 groups: Decile 1 = bottom 10%, Decile 10 = top 10%
-- => Useful for finer-grained analysis than quartiles

-- Example D: Find median salary (50th percentile)
WITH ordered AS (                                       -- => CTE with row numbering
    SELECT
        salary,                                         -- => Salary value
        ROW_NUMBER() OVER (ORDER BY salary) AS row_num, -- => Position in sorted list (1-100)
        COUNT(*) OVER () AS total_count                 -- => Total number of salaries (100)
    FROM employee_salaries
)
SELECT AVG(salary) AS median_salary                     -- => Average handles both odd and even counts
FROM ordered
WHERE row_num IN (                                      -- => Select middle position(s)
    (total_count + 1) / 2,                              -- => For 100: position 50 (middle-low)
    (total_count + 2) / 2                               -- => For 100: position 51 (middle-high)
);                                                      -- => Averages two middle values for even count
-- => Returns median (middle value or average of two middle values)
-- => For 100 employees, median is average of salaries at positions 50 and 51

-- Example E: Calculate 90th percentile salary
WITH ordered AS (                                       -- => CTE with row numbering
    SELECT
        salary,                                         -- => Salary value
        ROW_NUMBER() OVER (ORDER BY salary) AS row_num, -- => Position in sorted list
        COUNT(*) OVER () AS total_count                 -- => Total count for percentage calculation
    FROM employee_salaries
)
SELECT MAX(salary) AS percentile_90                     -- => Highest salary in bottom 90%
FROM ordered
WHERE row_num <= CAST(total_count * 0.90 AS INTEGER);   -- => First 90 employees (90% of 100)
-- => Returns 90th percentile (salary where 90% of employees earn less)
-- => Example: If 90th percentile is $150k, only 10% earn more

-- Example F: Identify outliers (below 10th or above 90th percentile)
WITH percentiles AS (                                   -- => CTE assigns deciles
    SELECT
        name,                                           -- => Employee identifier
        salary,                                         -- => Salary value
        NTILE(10) OVER (ORDER BY salary) AS decile      -- => Decile assignment (1-10)
    FROM employee_salaries
)
SELECT name, salary, decile                             -- => Return outlier details
FROM percentiles
WHERE decile = 1 OR decile = 10                         -- => Filter for extreme deciles only
ORDER BY salary;                                        -- => Sort low to high
-- => Returns outliers: Bottom 10% (decile 1) and top 10% (decile 10)
-- => Useful for identifying compensation anomalies or exceptional performers

-- Create users table for cohort tracking
CREATE TABLE users (              -- => Stores user signup information
    id INTEGER,                   -- => Unique user identifier
    email TEXT,                   -- => User email address
    signup_date TEXT              -- => Date user signed up (ISO format)
);

-- Create purchases table for behavior tracking
CREATE TABLE purchases (          -- => Tracks user purchase activity
    id INTEGER,                   -- => Unique purchase identifier
    user_id INTEGER,              -- => Foreign key to users table
    purchase_date TEXT,           -- => Date of purchase (ISO format)
    amount REAL                   -- => Purchase amount in dollars
);

-- Generate 100 users with staggered signup dates
INSERT INTO users (id, email, signup_date)
SELECT
    value,                                              -- => User ID from 1-100
    'user' || value || '@example.com',                  -- => Email: "user1@example.com", etc.
    date('2025-01-01', '+' || ((value - 1) / 10) || ' days')  -- => Signup dates spread over 10 days
                                                         -- => IDs 1-10 signup Jan 1, 11-20 signup Jan 2, etc.
FROM (
    WITH RECURSIVE nums AS (                            -- => Generate number sequence
        SELECT 1 AS value                               -- => Start at 1
        UNION ALL                                       -- => Combine iterations
        SELECT value + 1 FROM nums WHERE value < 100    -- => Increment to 100
    )
    SELECT value FROM nums                              -- => Return 1-100
);

-- Generate purchases for 30% of users
INSERT INTO purchases (id, user_id, purchase_date, amount)
SELECT
    value,                                              -- => Purchase ID from 1-30
    value,                                              -- => User ID (first 30 users make purchases)
    date((SELECT signup_date FROM users WHERE id = value), '+' || (value % 30) || ' days'),
                                                         -- => Purchase 0-29 days after signup
                                                         -- => Simulates varied purchase timing
    50 + (value * 10)                                   -- => Amount from $60-$350
FROM (
    WITH RECURSIVE nums AS (                            -- => Generate 30 purchases
        SELECT 1 AS value
        UNION ALL
        SELECT value + 1 FROM nums WHERE value < 30
    )
    SELECT value FROM nums
);

-- Example A: Cohort retention table by signup week
WITH cohorts AS (                                       -- => CTE assigns each user to cohort
    SELECT
        id AS user_id,                                  -- => User identifier
        signup_date,                                    -- => Original signup date
        DATE(signup_date, 'weekday 0', '-6 days') AS cohort_week
                                                         -- => Round to start of week (Sunday)
                                                         -- => All users signing up same week grouped together
    FROM users
),
purchase_weeks AS (                                     -- => CTE calculates weeks since signup
    SELECT
        user_id,                                        -- => User who made purchase
        DATE(purchase_date, 'weekday 0', '-6 days') AS purchase_week,
                                                         -- => Round purchase to week start
        CAST((JULIANDAY(purchase_date) - JULIANDAY((SELECT signup_date FROM users WHERE id = user_id))) / 7 AS INTEGER) AS weeks_since_signup
                                                         -- => Calculate whole weeks between signup and purchase
                                                         -- => 0 = same week, 1 = next week, etc.
    FROM purchases
)
SELECT
    c.cohort_week,                                      -- => Week cohort signed up
    COUNT(DISTINCT c.user_id) AS cohort_size,           -- => Total users in this cohort
    COUNT(DISTINCT CASE WHEN pw.weeks_since_signup = 0 THEN c.user_id END) AS week_0_active,
                                                         -- => Users who purchased in signup week
    COUNT(DISTINCT CASE WHEN pw.weeks_since_signup = 1 THEN c.user_id END) AS week_1_active,
                                                         -- => Users who purchased in week 1 after signup
    COUNT(DISTINCT CASE WHEN pw.weeks_since_signup = 2 THEN c.user_id END) AS week_2_active,
                                                         -- => Week 2 active users
    COUNT(DISTINCT CASE WHEN pw.weeks_since_signup = 3 THEN c.user_id END) AS week_3_active
                                                         -- => Week 3 active users
FROM cohorts c
LEFT JOIN purchase_weeks pw ON c.user_id = pw.user_id  -- => Include users with no purchases (LEFT JOIN)
GROUP BY c.cohort_week                                  -- => Aggregate by cohort
ORDER BY c.cohort_week;                                 -- => Chronological order
-- => Output: Retention table showing active users per week for each cohort
-- => Example row: 2025-01-01 | 70 | 10 | 8 | 6 | 5

-- Example B: Calculate retention percentages
WITH cohorts AS (                                       -- => Assign users to cohorts
    SELECT
        id AS user_id,
        DATE(signup_date, 'weekday 0', '-6 days') AS cohort_week
    FROM users
),
purchase_weeks AS (                                     -- => Calculate weeks since signup
    SELECT
        user_id,
        CAST((JULIANDAY(purchase_date) - JULIANDAY((SELECT signup_date FROM users WHERE id = user_id))) / 7 AS INTEGER) AS weeks_since_signup
    FROM purchases
),
cohort_stats AS (                                       -- => Aggregate cohort metrics
    SELECT
        c.cohort_week,                                  -- => Cohort identifier
        COUNT(DISTINCT c.user_id) AS cohort_size,       -- => Total cohort size (denominator)
        COUNT(DISTINCT CASE WHEN pw.weeks_since_signup = 1 THEN c.user_id END) AS week_1_active
                                                         -- => Week 1 active count (numerator)
    FROM cohorts c
    LEFT JOIN purchase_weeks pw ON c.user_id = pw.user_id
    GROUP BY c.cohort_week
)
SELECT
    cohort_week,                                        -- => Cohort identifier
    cohort_size,                                        -- => Total users in cohort
    week_1_active,                                      -- => Users active in week 1
    ROUND(100.0 * week_1_active / cohort_size, 2) AS week_1_retention_pct
                                                         -- => Percentage retained: (active / total) * 100
                                                         -- => Rounded to 2 decimal places
FROM cohort_stats
ORDER BY cohort_week;                                   -- => Chronological order
-- => Output: Retention percentage for each cohort
-- => Example: 2025-01-01 | 70 | 8 | 11.43 (11.43% retained after 1 week)

-- Create events table for funnel tracking
CREATE TABLE user_events (         -- => Tracks user actions through conversion funnel
    id INTEGER,                    -- => Unique event identifier
    user_id INTEGER,               -- => User who triggered event
    event_type TEXT,               -- => Event type: 'visit', 'signup', 'purchase'
    event_timestamp TEXT           -- => When event occurred (ISO format)
);

-- Generate 300 events simulating user journeys
INSERT INTO user_events (id, user_id, event_type, event_timestamp)
SELECT
    value,                                              -- => Event ID from 1-300
    (value + 2) / 3,                                    -- => Groups events in threes (user 1: events 1-3, user 2: events 4-6)
                                                         -- => Creates 100 users
    CASE value % 3                                      -- => Rotate through event types
        WHEN 1 THEN 'visit'                             -- => First event in group: visit
        WHEN 2 THEN 'signup'                            -- => Second event: signup
        WHEN 0 THEN 'purchase'                          -- => Third event: purchase
    END,
    datetime('2025-01-01', '+' || value || ' minutes')  -- => Events spaced 1 minute apart
FROM (
    WITH RECURSIVE nums AS (                            -- => Generate 300 events
        SELECT 1 AS value
        UNION ALL
        SELECT value + 1 FROM nums WHERE value < 300
    )
    SELECT value FROM nums
);

-- Example A: Basic funnel counts
SELECT
    event_type AS stage,                                -- => Funnel stage name
    COUNT(DISTINCT user_id) AS users,                   -- => Unique users at this stage
    RANK() OVER (ORDER BY                               -- => Assign rank for ordering
        CASE event_type                                 -- => Map events to numeric order
            WHEN 'visit' THEN 1                         -- => Visit is first
            WHEN 'signup' THEN 2                        -- => Signup is second
            WHEN 'purchase' THEN 3                      -- => Purchase is third
        END
    ) AS stage_order                                    -- => Funnel stage sequence
FROM user_events
GROUP BY event_type                                     -- => Aggregate by stage
ORDER BY stage_order;                                   -- => Display in funnel order
-- => Output: visit | 100 | 1, signup | 100 | 2, purchase | 100 | 3
-- => Shows user count at each funnel stage

-- Example B: Funnel with conversion rates
WITH funnel_stages AS (                                 -- => CTE calculates stage metrics
    SELECT
        event_type AS stage,                            -- => Stage name
        COUNT(DISTINCT user_id) AS users,               -- => Users at this stage
        CASE event_type                                 -- => Stage ordering
            WHEN 'visit' THEN 1
            WHEN 'signup' THEN 2
            WHEN 'purchase' THEN 3
        END AS stage_order
    FROM user_events
    GROUP BY event_type
),
total_visits AS (                                       -- => CTE extracts total visits (funnel top)
    SELECT users AS visit_count                         -- => Total users who started funnel
    FROM funnel_stages
    WHERE stage = 'visit'                               -- => First stage only
)
SELECT
    fs.stage,                                           -- => Stage name
    fs.users,                                           -- => Users at this stage
    tv.visit_count,                                     -- => Total funnel entries (denominator)
    ROUND(100.0 * fs.users / tv.visit_count, 2) AS conversion_pct,
                                                         -- => Overall conversion from top: (stage / total) * 100
    LAG(fs.users) OVER (ORDER BY fs.stage_order) AS prev_stage_users,
                                                         -- => Users in previous stage
    ROUND(100.0 * fs.users / NULLIF(LAG(fs.users) OVER (ORDER BY fs.stage_order), 0), 2) AS stage_conversion_pct
                                                         -- => Stage-to-stage conversion: (current / previous) * 100
                                                         -- => NULLIF prevents division by zero
FROM funnel_stages fs, total_visits tv
ORDER BY fs.stage_order;                                -- => Display in funnel sequence
-- => Output shows both overall and step-wise conversion rates
-- => Example: signup | 80 | 100 | 80.00 | 100 | 80.00 (80% of visits converted to signup)

-- Example C: Time-to-conversion analysis
WITH user_journey AS (                                  -- => CTE pivots events to columns
    SELECT
        user_id,                                        -- => User identifier
        MIN(CASE WHEN event_type = 'visit' THEN event_timestamp END) AS visit_time,
                                                         -- => First visit timestamp for this user
        MIN(CASE WHEN event_type = 'signup' THEN event_timestamp END) AS signup_time,
                                                         -- => First signup timestamp
        MIN(CASE WHEN event_type = 'purchase' THEN event_timestamp END) AS purchase_time
                                                         -- => First purchase timestamp
    FROM user_events
    GROUP BY user_id                                    -- => One row per user
)
SELECT
    user_id,                                            -- => User identifier
    CAST((JULIANDAY(signup_time) - JULIANDAY(visit_time)) * 24 * 60 AS INTEGER) AS visit_to_signup_minutes,
                                                         -- => Minutes between visit and signup
                                                         -- => JULIANDAY converts to days, multiply by 1440 for minutes
    CAST((JULIANDAY(purchase_time) - JULIANDAY(signup_time)) * 24 * 60 AS INTEGER) AS signup_to_purchase_minutes
                                                         -- => Minutes between signup and purchase
FROM user_journey
WHERE purchase_time IS NOT NULL                         -- => Only users who completed purchase
ORDER BY user_id
LIMIT 10;                                               -- => Show first 10 for readability
-- => Output: Time spent at each funnel stage in minutes
-- => Example: user 1 | 1 | 1 (1 minute visit→signup, 1 minute signup→purchase)

-- Example D: Drop-off analysis
WITH user_max_stage AS (                                -- => CTE finds furthest stage reached
    SELECT
        user_id,                                        -- => User identifier
        MAX(CASE event_type                             -- => Highest stage number reached
            WHEN 'visit' THEN 1                         -- => Visit = stage 1
            WHEN 'signup' THEN 2                        -- => Signup = stage 2
            WHEN 'purchase' THEN 3                      -- => Purchase = stage 3
        END) AS max_stage                               -- => Maximum stage this user reached
    FROM user_events
    GROUP BY user_id                                    -- => One row per user
)
SELECT
    CASE max_stage                                      -- => Convert stage number to label
        WHEN 1 THEN 'Dropped at Visit'                  -- => Never signed up
        WHEN 2 THEN 'Dropped at Signup'                 -- => Signed up but never purchased
        WHEN 3 THEN 'Completed Purchase'                -- => Full funnel completion
    END AS outcome,
    COUNT(*) AS user_count                              -- => Users in each category
FROM user_max_stage
GROUP BY max_stage                                      -- => Aggregate by max stage
ORDER BY max_stage;                                     -- => Order by funnel progression
-- => Output: Drop-off distribution
-- => Example: Dropped at Visit | 20, Dropped at Signup | 30, Completed | 50

-- Create page views table for session tracking
CREATE TABLE page_views (          -- => Stores user browsing activity
    id INTEGER,                    -- => Unique page view identifier
    user_id INTEGER,               -- => User who viewed the page
    page TEXT,                     -- => Page identifier
    view_timestamp TEXT            -- => When page was viewed (ISO format)
);

-- Generate 60 page views across 4 users
INSERT INTO page_views (id, user_id, page, view_timestamp)
SELECT
    value,                                              -- => View ID from 1-60
    (value - 1) / 15 + 1,                               -- => User ID: 15 views per user (1-4)
                                                         -- => Views 1-15 = user 1, 16-30 = user 2, etc.
    'page_' || (value % 5 + 1),                         -- => Page name: page_1 through page_5 (cyclic)
    datetime('2025-01-01', '+' || (value * 2 + (value % 5) * 20) || ' minutes')
                                                         -- => Variable time gaps (2 min base + 0-80 min variance)
                                                         -- => Creates some gaps > 30min (new session)
FROM (
    WITH RECURSIVE nums AS (                            -- => Generate 60 events
        SELECT 1 AS value
        UNION ALL
        SELECT value + 1 FROM nums WHERE value < 60
    )
    SELECT value FROM nums
);

-- Example A: Sessionize views (30-minute timeout)
WITH view_gaps AS (                                     -- => CTE identifies session boundaries
    SELECT
        user_id,                                        -- => User identifier
        page,                                           -- => Page viewed
        view_timestamp,                                 -- => Current view time
        LAG(view_timestamp) OVER (PARTITION BY user_id ORDER BY view_timestamp) AS prev_timestamp,
                                                         -- => Previous view time for this user
                                                         -- => NULL for first view
        CASE
            WHEN (JULIANDAY(view_timestamp) - JULIANDAY(LAG(view_timestamp) OVER (PARTITION BY user_id ORDER BY view_timestamp))) * 24 * 60 > 30
                                                         -- => Gap > 30 minutes from previous view
                OR LAG(view_timestamp) OVER (PARTITION BY user_id ORDER BY view_timestamp) IS NULL
                                                         -- => OR first view for user
            THEN 1                                       -- => Mark as new session start
            ELSE 0                                       -- => Same session continues
        END AS new_session                               -- => Binary flag for session boundary
    FROM page_views
),
session_starts AS (                                     -- => CTE assigns session IDs
    SELECT
        user_id,                                        -- => User identifier
        view_timestamp,                                 -- => View timestamp
        SUM(new_session) OVER (PARTITION BY user_id ORDER BY view_timestamp) AS session_id
                                                         -- => Running sum creates unique session IDs
                                                         -- => Example: 0, 0, 0, 1, 1, 2 (3 sessions)
    FROM view_gaps
)
SELECT
    user_id,                                            -- => User identifier
    session_id,                                         -- => Session number for this user
    COUNT(*) AS page_views_in_session,                  -- => Number of pages viewed in session
    MIN(view_timestamp) AS session_start,               -- => First view in session
    MAX(view_timestamp) AS session_end,                 -- => Last view in session
    ROUND((JULIANDAY(MAX(view_timestamp)) - JULIANDAY(MIN(view_timestamp))) * 24 * 60, 2) AS session_duration_minutes
                                                         -- => Duration from first to last view in minutes
FROM session_starts
GROUP BY user_id, session_id                            -- => Aggregate by session
ORDER BY user_id, session_id;                           -- => Display chronologically
-- => Output: One row per session with metrics
-- => Example: user 1 | session 0 | 5 views | 2025-01-01 00:02 | 2025-01-01 00:42 | 40.0 min

-- Example B: Average session metrics across all users
WITH view_gaps AS (                                     -- => Identify session boundaries
    SELECT
        user_id,
        view_timestamp,
        CASE
            WHEN (JULIANDAY(view_timestamp) - JULIANDAY(LAG(view_timestamp) OVER (PARTITION BY user_id ORDER BY view_timestamp))) * 24 * 60 > 30
                OR LAG(view_timestamp) OVER (PARTITION BY user_id ORDER BY view_timestamp) IS NULL
            THEN 1                                       -- => New session marker
            ELSE 0                                       -- => Continuing session
        END AS new_session
    FROM page_views
),
sessions AS (                                           -- => Assign session IDs
    SELECT
        user_id,
        view_timestamp,
        SUM(new_session) OVER (PARTITION BY user_id ORDER BY view_timestamp) AS session_id
                                                         -- => Session identifier per user
    FROM view_gaps
),
session_metrics AS (                                    -- => Calculate per-session metrics
    SELECT
        user_id,
        session_id,
        COUNT(*) AS views_per_session,                  -- => Page views in this session
        ROUND((JULIANDAY(MAX(view_timestamp)) - JULIANDAY(MIN(view_timestamp))) * 24 * 60, 2) AS duration_minutes
                                                         -- => Session duration in minutes
    FROM sessions
    GROUP BY user_id, session_id                        -- => One row per session
)
SELECT
    ROUND(AVG(views_per_session), 2) AS avg_views_per_session,
                                                         -- => Mean page views across all sessions
    ROUND(AVG(duration_minutes), 2) AS avg_duration_minutes,
                                                         -- => Mean session duration in minutes
    COUNT(DISTINCT user_id || '-' || session_id) AS total_sessions
                                                         -- => Total number of sessions created
FROM session_metrics;                                   -- => Aggregate across all sessions
-- => Output: Summary statistics for all sessions
-- => Example: 4.5 views/session | 12.3 min/session | 20 total sessions

-- Create customers table for survival tracking
CREATE TABLE customers (                    -- => Tracks customer lifecycle
    id INTEGER,                             -- => Unique customer identifier
    signup_date TEXT,                       -- => When customer signed up (ISO format)
    churn_date TEXT                         -- => When customer churned (NULL if still active)
);

-- Generate 100 customers with varied lifespans
INSERT INTO customers (id, signup_date, churn_date)
SELECT
    value,                                              -- => Customer ID from 1-100
    date('2024-01-01', '+' || ((value - 1) / 10) || ' days'),
                                                         -- => Signup dates spread over 10 days
                                                         -- => IDs 1-10 signup Jan 1, 11-20 Jan 2, etc.
    CASE
        WHEN value % 3 = 0                              -- => Every 3rd customer churns (33%)
        THEN date('2024-01-01', '+' || ((value - 1) / 10 + 30 + value % 60) || ' days')
                                                         -- => Churn 30-90 days after signup
        ELSE NULL                                        -- => 67% remain active (NULL churn date)
    END
FROM (
    WITH RECURSIVE nums AS (                            -- => Generate customer IDs
        SELECT 1 AS value
        UNION ALL
        SELECT value + 1 FROM nums WHERE value < 100
    )
    SELECT value FROM nums
);

-- Example A: Calculate customer lifetime in days
WITH customer_lifetime AS (                             -- => CTE computes lifetime metrics
    SELECT
        id,                                             -- => Customer identifier
        signup_date,                                    -- => Original signup date
        COALESCE(churn_date, date('now')) AS end_date,  -- => Use current date if still active
                                                         -- => COALESCE returns first non-NULL value
        CAST(JULIANDAY(COALESCE(churn_date, date('now'))) - JULIANDAY(signup_date) AS INTEGER) AS lifetime_days,
                                                         -- => Days from signup to churn (or now)
                                                         -- => JULIANDAY converts to numeric days
        CASE WHEN churn_date IS NULL THEN 1 ELSE 0 END AS is_active
                                                         -- => Binary flag: 1 = active, 0 = churned
    FROM customers
)
SELECT
    id,                                                 -- => Customer identifier
    signup_date,                                        -- => When they joined
    lifetime_days,                                      -- => How long they've been/were active
    CASE WHEN is_active = 1 THEN 'Active' ELSE 'Churned' END AS status
                                                         -- => Human-readable status
FROM customer_lifetime
ORDER BY lifetime_days DESC                             -- => Sort by longest lifetime first
LIMIT 10;                                               -- => Show top 10 customers
-- => Output: Top 10 customers by lifetime with current status
-- => Example: 42 | 2024-01-05 | 397 | Active

-- Example B: Survival curve (percentage surviving over time)
WITH customer_lifetime AS (                             -- => Calculate lifetime for each customer
    SELECT
        id,
        signup_date,
        CAST(JULIANDAY(COALESCE(churn_date, date('now'))) - JULIANDAY(signup_date) AS INTEGER) AS lifetime_days
                                                         -- => Days active (current or until churn)
    FROM customers
),
lifetime_buckets AS (                                   -- => Group customers into 30-day buckets
    SELECT
        (lifetime_days / 30) * 30 AS days_bucket,       -- => Round down to 30-day intervals (0, 30, 60, 90...)
                                                         -- => Example: 45 days → 30-day bucket, 92 days → 90-day bucket
        COUNT(*) AS customers_at_risk                   -- => Customers in this bucket
    FROM customer_lifetime
    GROUP BY days_bucket                                -- => Aggregate by time bucket
),
total_customers AS (                                    -- => Get total customer count for percentages
    SELECT COUNT(*) AS total FROM customers
)
SELECT
    lb.days_bucket,                                     -- => Time interval (days)
    lb.customers_at_risk,                               -- => Customers in this bucket
    tc.total,                                           -- => Total customers (denominator)
    ROUND(100.0 * SUM(lb.customers_at_risk) OVER (ORDER BY lb.days_bucket DESC) / tc.total, 2) AS survival_pct
                                                         -- => Running sum from highest to lowest bucket
                                                         -- => Shows % surviving AT LEAST this long
                                                         -- => Example: 90-day bucket shows % surviving 90+ days
FROM lifetime_buckets lb, total_customers tc
ORDER BY lb.days_bucket;                                -- => Display chronologically
-- => Output: Survival curve showing % surviving at each time point
-- => Example: 30 | 20 | 100 | 80.00 (80% survive past 30 days)

-- Example C: Cohort-based churn analysis
WITH cohorts AS (                                       -- => Assign customers to monthly cohorts
    SELECT
        id,                                             -- => Customer identifier
        DATE(signup_date, 'start of month') AS cohort_month,
                                                         -- => Round signup to month start (2024-01-01)
        churn_date                                      -- => Churn date (NULL if active)
    FROM customers
),
cohort_stats AS (                                       -- => Calculate metrics per cohort
    SELECT
        cohort_month,                                   -- => Cohort identifier (month)
        COUNT(*) AS cohort_size,                        -- => Total customers in cohort
        COUNT(churn_date) AS churned_count              -- => Churned customers (COUNT ignores NULL)
                                                         -- => Active customers have NULL churn_date
    FROM cohorts
    GROUP BY cohort_month                               -- => Aggregate by cohort
)
SELECT
    cohort_month,                                       -- => Month cohort signed up
    cohort_size,                                        -- => Total in cohort
    churned_count,                                      -- => How many churned
    cohort_size - churned_count AS still_active,        -- => How many still active
    ROUND(100.0 * churned_count / cohort_size, 2) AS churn_rate_pct
                                                         -- => Churn percentage for this cohort
FROM cohort_stats
ORDER BY cohort_month;                                  -- => Chronological order
-- => Output: Churn rates by signup month
-- => Example: 2024-01-01 | 100 | 33 | 67 | 33.00 (33% churn rate for January cohort)

-- Enable foreign key constraints (SQLite specific)
PRAGMA foreign_keys = ON;                              -- => Enables referential integrity enforcement
                                                         -- => Without this, foreign keys are ignored

-- Parent table: Authors (the "one" side)
CREATE TABLE authors (                                  -- => Stores author information
    id INTEGER PRIMARY KEY,                             -- => Unique author identifier
    name TEXT NOT NULL,                                 -- => Author name (required)
    email TEXT UNIQUE                                   -- => Email address (must be unique across authors)
);

-- Child table: Books (the "many" side)
CREATE TABLE books (                                    -- => Stores book information
    id INTEGER PRIMARY KEY,                             -- => Unique book identifier
    title TEXT NOT NULL,                                -- => Book title (required)
    author_id INTEGER NOT NULL,                         -- => Foreign key to authors table
                                                         -- => Links each book to exactly one author
    published_year INTEGER,                             -- => Year published (optional)
    FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE
                                                         -- => Enforces referential integrity
                                                         -- => CASCADE: Deleting author deletes all their books
);

-- Insert authors (parent records)
INSERT INTO authors (id, name, email)
VALUES
    (1, 'Alice Author', 'alice@books.com'),             -- => Author with ID 1
    (2, 'Bob Writer', 'bob@books.com');                 -- => Author with ID 2

-- Insert books (child records linked to authors)
INSERT INTO books (id, title, author_id, published_year)
VALUES
    (1, 'SQL Mastery', 1, 2020),                        -- => Alice's first book
    (2, 'Database Design', 1, 2021),                    -- => Alice's second book
    (3, 'Query Optimization', 1, 2022),                 -- => Alice's third book
    (4, 'Data Modeling', 2, 2021);                      -- => Bob's only book
-- => Alice (ID 1) has 3 books, Bob (ID 2) has 1 book

-- Example A: Query with INNER JOIN to show relationships
SELECT
    a.name AS author,                                   -- => Author name from parent table
    b.title AS book,                                    -- => Book title from child table
    b.published_year                                    -- => Publication year
FROM authors a
INNER JOIN books b ON a.id = b.author_id                -- => Match books to their authors
                                                         -- => INNER JOIN excludes authors without books
ORDER BY a.name, b.published_year;                      -- => Sort by author, then chronologically
-- => Output: Alice Author | SQL Mastery | 2020
-- =>         Alice Author | Database Design | 2021
-- =>         Alice Author | Query Optimization | 2022
-- =>         Bob Writer | Data Modeling | 2021

-- Example B: Count books per author (including authors with zero books)
SELECT
    a.name AS author,                                   -- => Author name
    COUNT(b.id) AS num_books                            -- => Number of books by this author
                                                         -- => COUNT(column) returns 0 for NULL (no books)
FROM authors a
LEFT JOIN books b ON a.id = b.author_id                 -- => Include ALL authors (even without books)
                                                         -- => LEFT JOIN keeps authors with NULL book matches
GROUP BY a.id, a.name                                   -- => Aggregate by author
ORDER BY num_books DESC;                                -- => Most prolific authors first
-- => Output: Alice Author | 3, Bob Writer | 1
-- => If Alice had no books, still shows: Alice Author | 0

-- Example C: CASCADE delete demonstration
DELETE FROM authors WHERE id = 1;                       -- => Delete Alice Author
-- => CASCADE effect: Automatically deletes all books with author_id = 1
-- => Books 1, 2, 3 are deleted because they reference deleted author

SELECT * FROM books;                                    -- => Query remaining books
-- => Output: Only Bob's book remains (id=4, title='Data Modeling')
-- => Alice's 3 books were cascaded deleted

-- Enable foreign key enforcement
PRAGMA foreign_keys = ON;                              -- => Required for referential integrity

-- First entity: Students
CREATE TABLE students (                                 -- => Stores student information
    id INTEGER PRIMARY KEY,                             -- => Unique student identifier
    name TEXT NOT NULL,                                 -- => Student name (required)
    email TEXT UNIQUE                                   -- => Email address (must be unique)
);

-- Second entity: Courses
CREATE TABLE courses (                                  -- => Stores course information
    id INTEGER PRIMARY KEY,                             -- => Unique course identifier
    title TEXT NOT NULL,                                -- => Course title (required)
    credits INTEGER                                     -- => Credit hours for this course
);

-- Junction table: Enables many-to-many relationship
CREATE TABLE enrollments (                              -- => Links students to courses
    id INTEGER PRIMARY KEY,                             -- => Unique enrollment identifier
    student_id INTEGER NOT NULL,                        -- => Foreign key to students
    course_id INTEGER NOT NULL,                         -- => Foreign key to courses
    enrollment_date TEXT,                               -- => When student enrolled (optional metadata)
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
                                                         -- => Deleting student removes their enrollments
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
                                                         -- => Deleting course removes all enrollments
    UNIQUE(student_id, course_id)                       -- => Prevent duplicate enrollments
                                                         -- => Student can't enroll in same course twice
);

-- Insert students
INSERT INTO students (id, name, email)
VALUES
    (1, 'Alice', 'alice@university.edu'),               -- => Student 1
    (2, 'Bob', 'bob@university.edu'),                   -- => Student 2
    (3, 'Charlie', 'charlie@university.edu');           -- => Student 3

-- Insert courses
INSERT INTO courses (id, title, credits)
VALUES
    (1, 'Database Systems', 3),                         -- => 3-credit course
    (2, 'Web Development', 3),                          -- => 3-credit course
    (3, 'Machine Learning', 4);                         -- => 4-credit course

-- Insert enrollments (many-to-many links)
INSERT INTO enrollments (student_id, course_id, enrollment_date)
VALUES
    (1, 1, '2025-01-01'),                               -- => Alice enrolled in Database Systems
    (1, 2, '2025-01-01'),                               -- => Alice enrolled in Web Development
    (2, 1, '2025-01-02'),                               -- => Bob enrolled in Database Systems
    (2, 3, '2025-01-02'),                               -- => Bob enrolled in Machine Learning
    (3, 2, '2025-01-03'),                               -- => Charlie enrolled in Web Development
    (3, 3, '2025-01-03');                               -- => Charlie enrolled in Machine Learning
-- => Alice: 2 courses, Bob: 2 courses, Charlie: 2 courses
-- => Database Systems: 2 students, Web Development: 2 students, Machine Learning: 2 students

-- Example A: Find all courses for a specific student
SELECT
    s.name AS student,                                  -- => Student name
    c.title AS course,                                  -- => Course title
    c.credits                                           -- => Credit hours
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id         -- => Join student to enrollments
INNER JOIN courses c ON e.course_id = c.id              -- => Join enrollments to courses
                                                         -- => Two JOINs traverse the many-to-many
WHERE s.name = 'Alice';                                 -- => Filter for specific student
-- => Output: Database Systems | 3, Web Development | 3
-- => Shows Alice's enrolled courses with credit hours

-- Example B: Find all students in a specific course
SELECT
    c.title AS course,                                  -- => Course title
    s.name AS student,                                  -- => Student name
    e.enrollment_date                                   -- => When they enrolled
FROM courses c
INNER JOIN enrollments e ON c.id = e.course_id          -- => Join course to enrollments
INNER JOIN students s ON e.student_id = s.id            -- => Join enrollments to students
WHERE c.title = 'Database Systems'                      -- => Filter for specific course
ORDER BY e.enrollment_date;                             -- => Chronological enrollment order
-- => Output: Database Systems | Alice | 2025-01-01
-- =>         Database Systems | Bob | 2025-01-02

-- Example C: Count courses and total credits per student
SELECT
    s.name AS student,                                  -- => Student name
    COUNT(e.id) AS num_courses,                         -- => Number of enrolled courses
    SUM(c.credits) AS total_credits                     -- => Sum of all enrolled course credits
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id          -- => Include students with no enrollments
LEFT JOIN courses c ON e.course_id = c.id               -- => Get course details
GROUP BY s.id, s.name;                                  -- => Aggregate by student
-- => Output: Alice | 2 | 6 (Database 3 + Web 3)
-- =>         Bob | 2 | 7 (Database 3 + ML 4)
-- =>         Charlie | 2 | 7 (Web 3 + ML 4)

-- Example D: Find students sharing courses with Alice
WITH alice_courses AS (                                 -- => CTE gets Alice's course IDs
    SELECT course_id FROM enrollments WHERE student_id = 1
                                                         -- => Returns course IDs 1 and 2
)
SELECT DISTINCT s.name                                  -- => Distinct student names
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id         -- => Get all enrollments
WHERE e.course_id IN (SELECT course_id FROM alice_courses)
                                                         -- => Filter for Alice's courses
  AND s.id != 1;                                        -- => Exclude Alice herself
-- => Output: Bob (shares Database Systems), Charlie (shares Web Development)
-- => Shows students with at least one common course

-- Enable foreign key enforcement
PRAGMA foreign_keys = ON;                              -- => Required for cascade operations

-- Self-referencing table for hierarchical data
CREATE TABLE categories (                               -- => Stores category hierarchy
    id INTEGER PRIMARY KEY,                             -- => Unique category identifier
    name TEXT NOT NULL,                                 -- => Category name
    parent_id INTEGER,                                  -- => Foreign key to parent category (same table)
                                                         -- => NULL for top-level categories
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE CASCADE
                                                         -- => Self-reference: points to another row in same table
                                                         -- => CASCADE: Deleting parent deletes all children
);

-- Insert hierarchical category data
INSERT INTO categories (id, name, parent_id)
VALUES
    (1, 'Electronics', NULL),                           -- => Top level (no parent)
    (2, 'Computers', 1),                                -- => Child of Electronics
    (3, 'Laptops', 2),                                  -- => Child of Computers (grandchild of Electronics)
    (4, 'Desktops', 2),                                 -- => Child of Computers
    (5, 'Phones', 1),                                   -- => Child of Electronics
    (6, 'Smartphones', 5),                              -- => Child of Phones
    (7, 'Feature Phones', 5),                           -- => Child of Phones
    (8, 'Home & Garden', NULL),                         -- => Top level (separate tree)
    (9, 'Furniture', 8);                                -- => Child of Home & Garden
-- => Creates two tree structures: Electronics (7 nodes) and Home & Garden (2 nodes)

-- Example A: Find direct children of a specific category
SELECT
    c.name AS category,                                 -- => Child category name
    p.name AS parent                                    -- => Parent category name
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id           -- => Self-join: join table to itself
                                                         -- => Alias 'c' for child, 'p' for parent
WHERE c.parent_id = 1                                   -- => Filter for Electronics' children only
ORDER BY c.name;                                        -- => Alphabetical sort
-- => Output: Computers | Electronics, Phones | Electronics
-- => Shows immediate children, not all descendants

-- Example B: Find all top-level categories (roots of hierarchy trees)
SELECT name                                             -- => Category name
FROM categories
WHERE parent_id IS NULL;                                -- => No parent means top level
-- => Output: Electronics, Home & Garden
-- => These are the roots of two separate hierarchy trees

-- Example C: Build full path to category using recursive CTE
WITH RECURSIVE category_path AS (                       -- => Recursive CTE builds path upward
    -- Base case: Start with target category (Laptops)
    SELECT id, name, parent_id, name AS path            -- => Initial path is just the category name
    FROM categories
    WHERE id = 3                                        -- => Starting point: Laptops (ID 3)

    UNION ALL                                           -- => Combine base case with recursive case

    -- Recursive case: Prepend parent to path
    SELECT c.id, c.name, c.parent_id, c.name || ' > ' || cp.path
                                                         -- => Build path: "Parent > Child"
                                                         -- => Example: "Computers > Laptops"
    FROM categories c
    INNER JOIN category_path cp ON c.id = cp.parent_id  -- => Join current path to its parent
                                                         -- => Walks up the tree one level per iteration
)
SELECT path                                             -- => Final path string
FROM category_path
WHERE parent_id IS NULL;                                -- => Stop at root (no parent)
-- => Output: Electronics > Computers > Laptops
-- => Shows complete ancestry from root to target

-- Example D: Count all descendants of a category
WITH RECURSIVE descendants AS (                         -- => Recursive CTE finds all descendants
    -- Base case: Start with target category (Electronics)
    SELECT id, name, 0 AS level                         -- => Root starts at level 0
    FROM categories
    WHERE id = 1                                        -- => Electronics (ID 1)

    UNION ALL                                           -- => Combine with recursive results

    -- Recursive case: Find children and increment level
    SELECT c.id, c.name, d.level + 1                    -- => Increment depth level
    FROM categories c
    INNER JOIN descendants d ON c.parent_id = d.id      -- => Find children of current level
                                                         -- => Walks down the tree, level by level
)
SELECT COUNT(*) - 1 AS num_descendants                  -- => Count all nodes
FROM descendants;                                       -- => Subtract 1 to exclude root itself
-- => Output: 5 (Computers, Laptops, Desktops, Phones, Smartphones, Feature Phones)
-- => Excludes Electronics itself from count