Advanced

CREATE TABLE employee_salaries (
    id INTEGER,
    name TEXT,
    salary REAL
);

INSERT INTO employee_salaries (id, name, salary)
SELECT
    value,
    'Employee ' || value,
    50000 + (value * 1000) + (value % 10) * 2000
FROM (
    WITH RECURSIVE nums AS (
        SELECT 1 AS value UNION ALL SELECT value + 1 FROM nums WHERE value < 100
    )
    SELECT value FROM nums
);

-- Divide into quartiles using NTILE
SELECT
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employee_salaries
ORDER BY salary;
-- => Q1: Bottom 25%, Q2: 25-50%, Q3: 50-75%, Q4: Top 25%

-- Calculate quartile boundaries
WITH quartiles AS (
    SELECT
        salary,
        NTILE(4) OVER (ORDER BY salary) AS quartile
    FROM employee_salaries
)
SELECT
    quartile,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM quartiles
GROUP BY quartile
ORDER BY quartile;
-- => Shows salary range for each quartile

-- Calculate percentiles (deciles - 10% buckets)
SELECT
    name,
    salary,
    NTILE(10) OVER (ORDER BY salary) AS decile
FROM employee_salaries
ORDER BY salary;
-- => Divides into 10 groups (1st decile = bottom 10%, 10th = top 10%)

-- Find median salary (50th percentile)
WITH ordered AS (
    SELECT
        salary,
        ROW_NUMBER() OVER (ORDER BY salary) AS row_num,
        COUNT(*) OVER () AS total_count
    FROM employee_salaries
)
SELECT AVG(salary) AS median_salary
FROM ordered
WHERE row_num IN (
    (total_count + 1) / 2,
    (total_count + 2) / 2
);
-- => Returns median (middle value or average of two middle values)

-- Calculate 90th percentile salary
WITH ordered AS (
    SELECT
        salary,
        ROW_NUMBER() OVER (ORDER BY salary) AS row_num,
        COUNT(*) OVER () AS total_count
    FROM employee_salaries
)
SELECT MAX(salary) AS percentile_90
FROM ordered
WHERE row_num <= CAST(total_count * 0.90 AS INTEGER);
-- => Returns 90th percentile (salary where 90% earn less)

-- Identify outliers (below 10th or above 90th percentile)
WITH percentiles AS (
    SELECT
        name,
        salary,
        NTILE(10) OVER (ORDER BY salary) AS decile
    FROM employee_salaries
)
SELECT name, salary, decile
FROM percentiles
WHERE decile = 1 OR decile = 10
ORDER BY salary;
-- => Returns outliers in bottom and top 10%

CREATE TABLE users (
    id INTEGER,
    email TEXT,
    signup_date TEXT
);

CREATE TABLE purchases (
    id INTEGER,
    user_id INTEGER,
    purchase_date TEXT,
    amount REAL
);

-- Insert users with staggered signup dates
INSERT INTO users (id, email, signup_date)
SELECT
    value,
    'user' || value || '@example.com',
    date('2025-01-01', '+' || ((value - 1) / 10) || ' days')
FROM (
    WITH RECURSIVE nums AS (
        SELECT 1 AS value UNION ALL SELECT value + 1 FROM nums WHERE value < 100
    )
    SELECT value FROM nums
);

-- Insert purchases (30% of users make purchases)
INSERT INTO purchases (id, user_id, purchase_date, amount)
SELECT
    value,
    value,
    date((SELECT signup_date FROM users WHERE id = value), '+' || (value % 30) || ' days'),
    50 + (value * 10)
FROM (
    WITH RECURSIVE nums AS (
        SELECT 1 AS value UNION ALL SELECT value + 1 FROM nums WHERE value < 30
    )
    SELECT value FROM nums
);

-- Cohort analysis: User retention by signup week
WITH cohorts AS (
    SELECT
        id AS user_id,
        signup_date,
        DATE(signup_date, 'weekday 0', '-6 days') AS cohort_week
    FROM users
),
purchase_weeks AS (
    SELECT
        user_id,
        DATE(purchase_date, 'weekday 0', '-6 days') AS purchase_week,
        CAST((JULIANDAY(purchase_date) - JULIANDAY((SELECT signup_date FROM users WHERE id = user_id))) / 7 AS INTEGER) AS weeks_since_signup
    FROM purchases
)
SELECT
    c.cohort_week,
    COUNT(DISTINCT c.user_id) AS cohort_size,
    COUNT(DISTINCT CASE WHEN pw.weeks_since_signup = 0 THEN c.user_id END) AS week_0_active,
    COUNT(DISTINCT CASE WHEN pw.weeks_since_signup = 1 THEN c.user_id END) AS week_1_active,
    COUNT(DISTINCT CASE WHEN pw.weeks_since_signup = 2 THEN c.user_id END) AS week_2_active,
    COUNT(DISTINCT CASE WHEN pw.weeks_since_signup = 3 THEN c.user_id END) AS week_3_active
FROM cohorts c
LEFT JOIN purchase_weeks pw ON c.user_id = pw.user_id
GROUP BY c.cohort_week
ORDER BY c.cohort_week;
-- => Shows how many users from each cohort made purchases in weeks 0-3

-- Calculate retention percentages
WITH cohorts AS (
    SELECT
        id AS user_id,
        DATE(signup_date, 'weekday 0', '-6 days') AS cohort_week
    FROM users
),
purchase_weeks AS (
    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 (
    SELECT
        c.cohort_week,
        COUNT(DISTINCT c.user_id) AS cohort_size,
        COUNT(DISTINCT CASE WHEN pw.weeks_since_signup = 1 THEN c.user_id END) AS week_1_active
    FROM cohorts c
    LEFT JOIN purchase_weeks pw ON c.user_id = pw.user_id
    GROUP BY c.cohort_week
)
SELECT
    cohort_week,
    cohort_size,
    week_1_active,
    ROUND(100.0 * week_1_active / cohort_size, 2) AS week_1_retention_pct
FROM cohort_stats
ORDER BY cohort_week;
-- => Shows week-1 retention percentage for each cohort

CREATE TABLE user_events (
    id INTEGER,
    user_id INTEGER,
    event_type TEXT,
    event_timestamp TEXT
);

-- Simulate user journey: visit -> signup -> purchase
INSERT INTO user_events (id, user_id, event_type, event_timestamp)
SELECT
    value,
    (value + 2) / 3,  -- Groups of 3 events per user
    CASE value % 3
        WHEN 1 THEN 'visit'
        WHEN 2 THEN 'signup'
        WHEN 0 THEN 'purchase'
    END,
    datetime('2025-01-01', '+' || value || ' minutes')
FROM (
    WITH RECURSIVE nums AS (
        SELECT 1 AS value UNION ALL SELECT value + 1 FROM nums WHERE value < 300
    )
    SELECT value FROM nums
);

-- Basic funnel: Count users at each stage
SELECT
    event_type AS stage,
    COUNT(DISTINCT user_id) AS users,
    RANK() OVER (ORDER BY
        CASE event_type
            WHEN 'visit' THEN 1
            WHEN 'signup' THEN 2
            WHEN 'purchase' THEN 3
        END
    ) AS stage_order
FROM user_events
GROUP BY event_type
ORDER BY stage_order;
-- => visit: 100, signup: 100, purchase: 100 (or fewer if some don't complete)

-- Funnel with conversion rates
WITH funnel_stages AS (
    SELECT
        event_type AS stage,
        COUNT(DISTINCT user_id) AS users,
        CASE event_type
            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 (
    SELECT users AS visit_count
    FROM funnel_stages
    WHERE stage = 'visit'
)
SELECT
    fs.stage,
    fs.users,
    tv.visit_count,
    ROUND(100.0 * fs.users / tv.visit_count, 2) AS conversion_pct,
    LAG(fs.users) OVER (ORDER BY fs.stage_order) AS prev_stage_users,
    ROUND(100.0 * fs.users / NULLIF(LAG(fs.users) OVER (ORDER BY fs.stage_order), 0), 2) AS stage_conversion_pct
FROM funnel_stages fs, total_visits tv
ORDER BY fs.stage_order;
-- => Shows overall conversion from visit and stage-to-stage conversion

-- Time-to-conversion analysis
WITH user_journey AS (
    SELECT
        user_id,
        MIN(CASE WHEN event_type = 'visit' THEN event_timestamp END) AS visit_time,
        MIN(CASE WHEN event_type = 'signup' THEN event_timestamp END) AS signup_time,
        MIN(CASE WHEN event_type = 'purchase' THEN event_timestamp END) AS purchase_time
    FROM user_events
    GROUP BY user_id
)
SELECT
    user_id,
    CAST((JULIANDAY(signup_time) - JULIANDAY(visit_time)) * 24 * 60 AS INTEGER) AS visit_to_signup_minutes,
    CAST((JULIANDAY(purchase_time) - JULIANDAY(signup_time)) * 24 * 60 AS INTEGER) AS signup_to_purchase_minutes
FROM user_journey
WHERE purchase_time IS NOT NULL
ORDER BY user_id
LIMIT 10;
-- => Shows time spent at each funnel stage

-- Drop-off analysis: Users who didn't complete
WITH user_max_stage AS (
    SELECT
        user_id,
        MAX(CASE event_type
            WHEN 'visit' THEN 1
            WHEN 'signup' THEN 2
            WHEN 'purchase' THEN 3
        END) AS max_stage
    FROM user_events
    GROUP BY user_id
)
SELECT
    CASE max_stage
        WHEN 1 THEN 'Dropped at Visit'
        WHEN 2 THEN 'Dropped at Signup'
        WHEN 3 THEN 'Completed Purchase'
    END AS outcome,
    COUNT(*) AS user_count
FROM user_max_stage
GROUP BY max_stage
ORDER BY max_stage;
-- => Shows where users dropped off

CREATE TABLE page_views (
    id INTEGER,
    user_id INTEGER,
    page TEXT,
    view_timestamp TEXT
);

-- Insert page views with varying gaps
INSERT INTO page_views (id, user_id, page, view_timestamp)
SELECT
    value,
    (value - 1) / 15 + 1,  -- 15 views per user
    'page_' || (value % 5 + 1),
    datetime('2025-01-01', '+' || (value * 2 + (value % 5) * 20) || ' minutes')
FROM (
    WITH RECURSIVE nums AS (
        SELECT 1 AS value UNION ALL SELECT value + 1 FROM nums WHERE value < 60
    )
    SELECT value FROM nums
);

-- Define session: Views within 30 minutes of previous view
WITH view_gaps AS (
    SELECT
        user_id,
        page,
        view_timestamp,
        LAG(view_timestamp) OVER (PARTITION BY user_id ORDER BY view_timestamp) AS prev_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
            ELSE 0
        END AS new_session
    FROM page_views
),
session_starts AS (
    SELECT
        user_id,
        view_timestamp,
        SUM(new_session) OVER (PARTITION BY user_id ORDER BY view_timestamp) AS session_id
    FROM view_gaps
)
SELECT
    user_id,
    session_id,
    COUNT(*) AS page_views_in_session,
    MIN(view_timestamp) AS session_start,
    MAX(view_timestamp) AS session_end,
    ROUND((JULIANDAY(MAX(view_timestamp)) - JULIANDAY(MIN(view_timestamp))) * 24 * 60, 2) AS session_duration_minutes
FROM session_starts
GROUP BY user_id, session_id
ORDER BY user_id, session_id;
-- => Shows sessions with start, end, duration, and page view count

-- Average session metrics
WITH view_gaps AS (
    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
            ELSE 0
        END AS new_session
    FROM page_views
),
sessions AS (
    SELECT
        user_id,
        view_timestamp,
        SUM(new_session) OVER (PARTITION BY user_id ORDER BY view_timestamp) AS session_id
    FROM view_gaps
),
session_metrics AS (
    SELECT
        user_id,
        session_id,
        COUNT(*) AS views_per_session,
        ROUND((JULIANDAY(MAX(view_timestamp)) - JULIANDAY(MIN(view_timestamp))) * 24 * 60, 2) AS duration_minutes
    FROM sessions
    GROUP BY user_id, session_id
)
SELECT
    ROUND(AVG(views_per_session), 2) AS avg_views_per_session,
    ROUND(AVG(duration_minutes), 2) AS avg_duration_minutes,
    COUNT(DISTINCT user_id || '-' || session_id) AS total_sessions
FROM session_metrics;
-- => Shows average session length and duration

CREATE TABLE customers (
    id INTEGER,
    signup_date TEXT,
    churn_date TEXT  -- NULL if still active
);

-- Insert customers with varying lifespans
INSERT INTO customers (id, signup_date, churn_date)
SELECT
    value,
    date('2024-01-01', '+' || ((value - 1) / 10) || ' days'),
    CASE
        WHEN value % 3 = 0 THEN date('2024-01-01', '+' || ((value - 1) / 10 + 30 + value % 60) || ' days')
        ELSE NULL
    END
FROM (
    WITH RECURSIVE nums AS (
        SELECT 1 AS value UNION ALL SELECT value + 1 FROM nums WHERE value < 100
    )
    SELECT value FROM nums
);

-- Calculate customer lifetime in days
WITH customer_lifetime AS (
    SELECT
        id,
        signup_date,
        COALESCE(churn_date, date('now')) AS end_date,
        CAST(JULIANDAY(COALESCE(churn_date, date('now'))) - JULIANDAY(signup_date) AS INTEGER) AS lifetime_days,
        CASE WHEN churn_date IS NULL THEN 1 ELSE 0 END AS is_active
    FROM customers
)
SELECT
    id,
    signup_date,
    lifetime_days,
    CASE WHEN is_active = 1 THEN 'Active' ELSE 'Churned' END AS status
FROM customer_lifetime
ORDER BY lifetime_days DESC
LIMIT 10;
-- => Shows top 10 customers by lifetime

-- Survival curve: Percentage surviving at each time point
WITH customer_lifetime AS (
    SELECT
        id,
        signup_date,
        CAST(JULIANDAY(COALESCE(churn_date, date('now'))) - JULIANDAY(signup_date) AS INTEGER) AS lifetime_days
    FROM customers
),
lifetime_buckets AS (
    SELECT
        (lifetime_days / 30) * 30 AS days_bucket,
        COUNT(*) AS customers_at_risk
    FROM customer_lifetime
    GROUP BY days_bucket
),
total_customers AS (
    SELECT COUNT(*) AS total FROM customers
)
SELECT
    lb.days_bucket,
    lb.customers_at_risk,
    tc.total,
    ROUND(100.0 * SUM(lb.customers_at_risk) OVER (ORDER BY lb.days_bucket DESC) / tc.total, 2) AS survival_pct
FROM lifetime_buckets lb, total_customers tc
ORDER BY lb.days_bucket;
-- => Shows percentage of customers surviving to each time bucket

-- Cohort-based churn analysis
WITH cohorts AS (
    SELECT
        id,
        DATE(signup_date, 'start of month') AS cohort_month,
        churn_date
    FROM customers
),
cohort_stats AS (
    SELECT
        cohort_month,
        COUNT(*) AS cohort_size,
        COUNT(churn_date) AS churned_count
    FROM cohorts
    GROUP BY cohort_month
)
SELECT
    cohort_month,
    cohort_size,
    churned_count,
    cohort_size - churned_count AS still_active,
    ROUND(100.0 * churned_count / cohort_size, 2) AS churn_rate_pct
FROM cohort_stats
ORDER BY cohort_month;
-- => Shows churn rate by signup month

-- Enable foreign keys
PRAGMA foreign_keys = ON;

-- Parent table: Authors
CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
);

-- Child table: Books (many books per author)
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER NOT NULL,
    published_year INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (id, name, email)
VALUES
    (1, 'Alice Author', 'alice@books.com'),
    (2, 'Bob Writer', 'bob@books.com');

INSERT INTO books (id, title, author_id, published_year)
VALUES
    (1, 'SQL Mastery', 1, 2020),
    (2, 'Database Design', 1, 2021),
    (3, 'Query Optimization', 1, 2022),
    (4, 'Data Modeling', 2, 2021);

-- Query with JOIN to show relationship
SELECT
    a.name AS author,
    b.title AS book,
    b.published_year
FROM authors a
INNER JOIN books b ON a.id = b.author_id
ORDER BY a.name, b.published_year;
-- => Alice Author: SQL Mastery (2020), Database Design (2021), Query Optimization (2022)
-- => Bob Writer: Data Modeling (2021)

-- Count books per author
SELECT
    a.name AS author,
    COUNT(b.id) AS num_books
FROM authors a
LEFT JOIN books b ON a.id = b.author_id
GROUP BY a.id, a.name
ORDER BY num_books DESC;
-- => Alice Author: 3 books, Bob Writer: 1 book

-- CASCADE delete: Remove author and all their books
DELETE FROM authors WHERE id = 1;
-- => Deletes Alice and all 3 of her books (CASCADE)

SELECT * FROM books;
-- => Only Bob's book remains (id=4)

PRAGMA foreign_keys = ON;

-- First entity: Students
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
);

-- Second entity: Courses
CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    credits INTEGER
);

-- Junction table: Links students and courses
CREATE TABLE enrollments (
    id INTEGER PRIMARY KEY,
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrollment_date TEXT,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    UNIQUE(student_id, course_id)  -- Prevent duplicate enrollments
);

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

INSERT INTO courses (id, title, credits)
VALUES
    (1, 'Database Systems', 3),
    (2, 'Web Development', 3),
    (3, 'Machine Learning', 4);

INSERT INTO enrollments (student_id, course_id, enrollment_date)
VALUES
    (1, 1, '2025-01-01'),  -- Alice -> Database Systems
    (1, 2, '2025-01-01'),  -- Alice -> Web Development
    (2, 1, '2025-01-02'),  -- Bob -> Database Systems
    (2, 3, '2025-01-02'),  -- Bob -> Machine Learning
    (3, 2, '2025-01-03'),  -- Charlie -> Web Development
    (3, 3, '2025-01-03');  -- Charlie -> Machine Learning

-- Find all courses for a student
SELECT
    s.name AS student,
    c.title AS course,
    c.credits
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id
WHERE s.name = 'Alice';
-- => Returns: Database Systems (3), Web Development (3)

-- Find all students in a course
SELECT
    c.title AS course,
    s.name AS student,
    e.enrollment_date
FROM courses c
INNER JOIN enrollments e ON c.id = e.course_id
INNER JOIN students s ON e.student_id = s.id
WHERE c.title = 'Database Systems'
ORDER BY e.enrollment_date;
-- => Returns: Alice (2025-01-01), Bob (2025-01-02)

-- Count enrollments
SELECT
    s.name AS student,
    COUNT(e.id) AS num_courses,
    SUM(c.credits) AS total_credits
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
LEFT JOIN courses c ON e.course_id = c.id
GROUP BY s.id, s.name;
-- => Alice: 2 courses (6 credits), Bob: 2 courses (7 credits), Charlie: 2 courses (7 credits)

-- Find students enrolled in same courses as Alice
WITH alice_courses AS (
    SELECT course_id FROM enrollments WHERE student_id = 1
)
SELECT DISTINCT s.name
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
WHERE e.course_id IN (SELECT course_id FROM alice_courses)
  AND s.id != 1;
-- => Returns: Bob, Charlie (share at least one course with Alice)

PRAGMA foreign_keys = ON;

CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    parent_id INTEGER,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE CASCADE
);

-- Insert hierarchical categories
INSERT INTO categories (id, name, parent_id)
VALUES
    (1, 'Electronics', NULL),          -- Top level
    (2, 'Computers', 1),                -- Electronics > Computers
    (3, 'Laptops', 2),                  -- Electronics > Computers > Laptops
    (4, 'Desktops', 2),                 -- Electronics > Computers > Desktops
    (5, 'Phones', 1),                   -- Electronics > Phones
    (6, 'Smartphones', 5),              -- Electronics > Phones > Smartphones
    (7, 'Feature Phones', 5),           -- Electronics > Phones > Feature Phones
    (8, 'Home & Garden', NULL),         -- Top level
    (9, 'Furniture', 8);                -- Home & Garden > Furniture

-- Find all direct children of a category
SELECT
    c.name AS category,
    p.name AS parent
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id
WHERE c.parent_id = 1
ORDER BY c.name;
-- => Returns: Computers, Phones (direct children of Electronics)

-- Find all top-level categories (no parent)
SELECT name
FROM categories
WHERE parent_id IS NULL;
-- => Returns: Electronics, Home & Garden

-- Find full path to category (using recursive CTE)
WITH RECURSIVE category_path AS (
    -- Base case: Start with Laptops
    SELECT id, name, parent_id, name AS path
    FROM categories
    WHERE id = 3

    UNION ALL

    -- Recursive case: Add parent to path
    SELECT c.id, c.name, c.parent_id, c.name || ' > ' || cp.path
    FROM categories c
    INNER JOIN category_path cp ON c.id = cp.parent_id
)
SELECT path
FROM category_path
WHERE parent_id IS NULL;
-- => Returns: Electronics > Computers > Laptops

-- Count descendants at all levels
WITH RECURSIVE descendants AS (
    -- Base case: Electronics and all descendants
    SELECT id, name, 0 AS level
    FROM categories
    WHERE id = 1

    UNION ALL

    SELECT c.id, c.name, d.level + 1
    FROM categories c
    INNER JOIN descendants d ON c.parent_id = d.id
)
SELECT COUNT(*) - 1 AS num_descendants  -- Exclude root
FROM descendants;
-- => Returns: 5 (Computers, Laptops, Desktops, Phones, Smartphones, Feature Phones)

-- Parent table 1: Posts
CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT,
    content TEXT
);

-- Parent table 2: Photos
CREATE TABLE photos (
    id INTEGER PRIMARY KEY,
    url TEXT,
    caption TEXT
);

-- Polymorphic child: Comments can belong to posts OR photos
CREATE TABLE comments (
    id INTEGER PRIMARY KEY,
    content TEXT,
    commentable_type TEXT,  -- 'post' or 'photo'
    commentable_id INTEGER, -- References posts.id or photos.id
    created_at TEXT
);

INSERT INTO posts (id, title, content)
VALUES
    (1, 'SQL Tutorial', 'Learn SQL basics'),
    (2, 'Advanced Queries', 'Master complex queries');

INSERT INTO photos (id, url, caption)
VALUES
    (1, 'https://example.com/photo1.jpg', 'Sunset'),
    (2, 'https://example.com/photo2.jpg', 'Mountain');

INSERT INTO comments (id, content, commentable_type, commentable_id, created_at)
VALUES
    (1, 'Great post!', 'post', 1, '2025-01-15 10:00:00'),
    (2, 'Very helpful', 'post', 1, '2025-01-15 11:00:00'),
    (3, 'Beautiful photo', 'photo', 1, '2025-01-15 12:00:00'),
    (4, 'Amazing view', 'photo', 2, '2025-01-15 13:00:00');

-- Find all comments on a specific post
SELECT
    c.content AS comment,
    c.created_at
FROM comments c
WHERE c.commentable_type = 'post'
  AND c.commentable_id = 1
ORDER BY c.created_at;
-- => Returns: 'Great post!', 'Very helpful'

-- Find all comments on a specific photo
SELECT
    c.content AS comment,
    c.created_at
FROM comments c
WHERE c.commentable_type = 'photo'
  AND c.commentable_id = 1;
-- => Returns: 'Beautiful photo'

-- Join comments with their parent entities (UNION pattern)
SELECT
    'post' AS type,
    p.title AS title,
    c.content AS comment
FROM posts p
INNER JOIN comments c ON c.commentable_type = 'post' AND c.commentable_id = p.id

UNION ALL

SELECT
    'photo' AS type,
    ph.caption AS title,
    c.content AS comment
FROM photos ph
INNER JOIN comments c ON c.commentable_type = 'photo' AND c.commentable_id = ph.id

ORDER BY type, title;
-- => Returns all comments with their parent entity

-- Count comments by type
SELECT
    commentable_type AS type,
    COUNT(*) AS num_comments
FROM comments
GROUP BY commentable_type;
-- => post: 2, photo: 2