Advanced SQL Problems

Problem 1: Session Analysis

Schema:

CREATE TABLE user_events (
    event_id INT PRIMARY KEY,
    user_id INT,
    event_type VARCHAR(50),
    event_time TIMESTAMP,
    page_url VARCHAR(200)
);

INSERT INTO user_events VALUES
(1, 101, 'page_view', '2024-01-15 10:00:00', '/home'),
(2, 101, 'page_view', '2024-01-15 10:05:00', '/products'),
(3, 101, 'click', '2024-01-15 10:10:00', '/products/laptop'),
(4, 101, 'page_view', '2024-01-15 10:35:00', '/cart'),
(5, 101, 'purchase', '2024-01-15 10:40:00', '/checkout'),
(6, 102, 'page_view', '2024-01-15 11:00:00', '/home'),
(7, 102, 'page_view', '2024-01-15 11:02:00', '/products'),
(8, 102, 'page_view', '2024-01-15 11:05:00', '/home');

Question: Define a session as a sequence of events from the same user where no gap exceeds 30 minutes. Find each user's session count, average session duration, and total events per session.

Solution:

WITH events_with_gap AS (
    SELECT 
        user_id,
        event_time,
        event_type,
        TIMESTAMPDIFF(MINUTE, 
            LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time),
            event_time
        ) as gap_minutes
    FROM user_events
),
session_boundaries AS (
    SELECT 
        *,
        SUM(CASE WHEN gap_minutes > 30 OR gap_minutes IS NULL THEN 1 ELSE 0 END) 
            OVER (PARTITION BY user_id ORDER BY event_time) as session_id
    FROM events_with_gap
)
SELECT 
    user_id,
    session_id,
    COUNT(*) as events_in_session,
    MIN(event_time) as session_start,
    MAX(event_time) as session_end,
    TIMESTAMPDIFF(MINUTE, MIN(event_time), MAX(event_time)) as duration_minutes
FROM session_boundaries
GROUP BY user_id, session_id
ORDER BY user_id, session_start;

Output:

user_id | session_id | events_in_session | session_start       | session_end         | duration_minutes
101     | 1          | 5                 | 2024-01-15 10:00:00 | 2024-01-15 10:40:00 | 40
102     | 1          | 3                 | 2024-01-15 11:00:00 | 2024-01-15 11:05:00 | 5

Explanation:

• LAG() computes gap between consecutive events
• SUM() with CASE assigns session IDs (increments when gap > 30 min)
• GROUP BY session_id aggregates session-level metrics
• Sessionization is critical for web analytics

Problem 2: Median Salary by Department

Schema:

CREATE TABLE staff (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

INSERT INTO staff VALUES
(1, 'Alice', 'Engineering', 90000),
(2, 'Bob', 'Engineering', 95000),
(3, 'Charlie', 'Engineering', 100000),
(4, 'Diana', 'Engineering', 105000),
(5, 'Eve', 'Engineering', 110000),
(6, 'Frank', 'Marketing', 60000),
(7, 'Grace', 'Marketing', 65000),
(8, 'Henry', 'Marketing', 70000);

Question: Find the median salary for each department.

Solution:

WITH ranked AS (
    SELECT 
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary) as row_num,
        COUNT(*) OVER (PARTITION BY department) as total_count
    FROM staff
)
SELECT 
    department,
    ROUND(AVG(salary), 2) as median_salary
FROM ranked
WHERE row_num IN (FLOOR((total_count + 1) / 2), CEIL((total_count + 1) / 2))
GROUP BY department;

Output:

department  | median_salary
Engineering | 100000.00
Marketing   | 65000.00

Explanation:

• ROW_NUMBER() assigns position within each department
• For odd counts: median is middle value
• For even counts: median is average of two middle values
• FLOOR/CEIL handle both odd and even cases

Problem 3: Recursive CTE - Organization Hierarchy

Schema:

CREATE TABLE org (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    manager_id INT,
    title VARCHAR(100)
);

INSERT INTO org VALUES
(1, 'CEO', NULL, 'Chief Executive Officer'),
(2, 'VP Engineering', 1, 'Vice President'),
(3, 'VP Sales', 1, 'Vice President'),
(4, 'Engineering Manager', 2, 'Manager'),
(5, 'Senior Engineer', 4, 'Engineer'),
(6, 'Junior Engineer', 4, 'Engineer'),
(7, 'Sales Manager', 3, 'Manager'),
(8, 'Sales Rep', 7, 'Representative');

Question: Write a recursive CTE to display the full hierarchy with indentation showing reporting levels.

Solution:

WITH RECURSIVE org_hierarchy AS (
    -- Base case: CEO (no manager)
    SELECT 
        emp_id,
        emp_name,
        manager_id,
        title,
        0 as level,
        CAST(emp_name AS CHAR(500)) as path
    FROM org
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: employees with managers
    SELECT 
        e.emp_id,
        e.emp_name,
        e.manager_id,
        e.title,
        h.level + 1,
        CONCAT(h.path, ' -> ', e.emp_name)
    FROM org e
    JOIN org_hierarchy h ON e.manager_id = h.emp_id
)
SELECT 
    CONCAT(REPEAT('  ', level), emp_name) as employee,
    title,
    level,
    path
FROM org_hierarchy
ORDER BY path;

Output:

employee           | title                    | level | path
CEO                | Chief Executive Officer  | 0     | CEO
  VP Engineering   | Vice President           | 1     | CEO -> VP Engineering
    Engineering Mgr| Manager                  | 2     | CEO -> VP Engineering -> Engineering Manager
      Senior Eng   | Engineer                 | 3     | CEO -> VP Engineering -> Engineering Manager -> Senior Engineer
      Junior Eng   | Engineer                 | 3     | CEO -> VP Engineering -> Engineering Manager -> Junior Engineer
  VP Sales         | Vice President           | 1     | CEO -> VP Sales
    Sales Manager  | Manager                  | 2     | CEO -> VP Sales -> Sales Manager
      Sales Rep    | Representative           | 3     | CEO -> VP Sales -> Sales Manager -> Sales Rep

Explanation:

• Recursive CTE starts with root (CEO, no manager)
• Each iteration joins org to previous level's results
• level tracks depth; path builds full hierarchy string
• REPEAT adds indentation for visual hierarchy

Problem 4: 7-Day Moving Average

Schema:

CREATE TABLE daily_sales (
    sale_date DATE PRIMARY KEY,
    revenue DECIMAL(10,2)
);

INSERT INTO daily_sales VALUES
('2024-01-01', 1000), ('2024-01-02', 1200), ('2024-01-03', 900),
('2024-01-04', 1100), ('2024-01-05', 1300), ('2024-01-06', 800),
('2024-01-07', 1150), ('2024-01-08', 1250), ('2024-01-09', 1400),
('2024-01-10', 1050);

Question: Calculate the 7-day moving average of revenue for each day.

Solution:

SELECT 
    sale_date,
    revenue,
    ROUND(AVG(revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) as moving_avg_7d,
    COUNT(*) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as days_in_window
FROM daily_sales
ORDER BY sale_date;

Output:

sale_date  | revenue | moving_avg_7d | days_in_window
2024-01-01 | 1000    | 1000.00       | 1
2024-01-02 | 1200    | 1100.00       | 2
2024-01-03 | 900     | 1033.33       | 3
2024-01-04 | 1100    | 1050.00       | 4
2024-01-05 | 1300    | 1100.00       | 5
2024-01-06 | 800     | 1050.00       | 6
2024-01-07 | 1150    | 1064.29       | 7
2024-01-08 | 1250    | 1114.29       | 7
2024-01-09 | 1400    | 1142.86       | 7
2024-01-10 | 1050    | 1135.71       | 7

Explanation:

• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW creates 7-day window
• AVG() computes mean over the window
• COUNT() shows actual window size (grows from 1 to 7)
• Useful for trend analysis, smoothing volatile metrics

Problem 5: Price Change Analysis

Schema:

CREATE TABLE price_history (
    product_id INT,
    price_date DATE,
    price DECIMAL(10,2)
);

INSERT INTO price_history VALUES
(1, '2024-01-01', 100.00),
(1, '2024-01-15', 120.00),
(1, '2024-02-01', 110.00),
(1, '2024-02-15', 130.00),
(2, '2024-01-01', 50.00),
(2, '2024-01-20', 55.00);

Question: For each product, show each price change with the previous price, percentage change, and whether it was an increase or decrease.

Solution:

SELECT 
    product_id,
    price_date,
    price as current_price,
    LAG(price) OVER (PARTITION BY product_id ORDER BY price_date) as previous_price,
    ROUND(
        (price - LAG(price) OVER (PARTITION BY product_id ORDER BY price_date)) 
        / LAG(price) OVER (PARTITION BY product_id ORDER BY price_date) * 100, 
        2
    ) as pct_change,
    CASE 
        WHEN price > LAG(price) OVER (PARTITION BY product_id ORDER BY price_date) THEN 'Increase'
        WHEN price < LAG(price) OVER (PARTITION BY product_id ORDER BY price_date) THEN 'Decrease'
        ELSE 'Same'
    END as change_type
FROM price_history
ORDER BY product_id, price_date;

Output:

product_id | price_date | current_price | previous_price | pct_change | change_type
1          | 2024-01-01 | 100.00        | NULL           | NULL       | NULL
1          | 2024-01-15 | 120.00        | 100.00         | 20.00      | Increase
1          | 2024-02-01 | 110.00        | 120.00         | -8.33      | Decrease
1          | 2024-02-15 | 130.00        | 110.00         | 18.18      | Increase
2          | 2024-01-01 | 50.00         | NULL           | NULL       | NULL
2          | 2024-01-20 | 55.00         | 50.00          | 10.00      | Increase

Explanation:

• LAG(price) gets the previous row's price
• Percentage change formula: (current - previous) / previous × 100
• CASE classifies the change direction
• First row has NULL previous price (no prior data)

Problem 6: Cumulative Distribution

Schema:

CREATE TABLE test_scores (
    student_id INT,
    subject VARCHAR(50),
    score INT
);

INSERT INTO test_scores VALUES
(1, 'Math', 85), (2, 'Math', 90), (3, 'Math', 78),
(4, 'Math', 92), (5, 'Math', 88), (6, 'Math', 76),
(7, 'Math', 95), (8, 'Math', 82), (9, 'Math', 87),
(10, 'Math', 91);

Question: Calculate the percentile rank and cumulative distribution of Math scores. Show student_id, score, percentile rank, and cumulative percentage of students at or below this score.

Solution:

SELECT 
    student_id,
    score,
    ROUND(PERCENT_RANK() OVER (ORDER BY score) * 100, 2) as percentile_rank,
    ROUND(CUME_DIST() OVER (ORDER BY score) * 100, 2) as cumulative_pct,
    NTILE(4) OVER (ORDER BY score) as quartile
FROM test_scores
ORDER BY score;

Output:

student_id | score | percentile_rank | cumulative_pct | quartile
6          | 76    | 0.00            | 10.00          | 1
3          | 78    | 11.11           | 20.00          | 1
8          | 82    | 22.22           | 30.00          | 1
1          | 85    | 33.33           | 40.00          | 2
9          | 87    | 44.44           | 50.00          | 2
5          | 88    | 55.56           | 60.00          | 3
10         | 91    | 66.67           | 70.00          | 3
2          | 90    | 77.78           | 80.00          | 4
4          | 92    | 88.89           | 90.00          | 4
7          | 95    | 100.00          | 100.00         | 4

Explanation:

• PERCENT_RANK: (rank - 1) / (total rows - 1)
• CUME_DIST: cumulative distribution (fraction of values <= current)
• NTILE(4) divides into quartiles (1-4)
• Useful for grade distribution, performance analysis

Problem 7: Monthly Sales by Category

Schema:

CREATE TABLE product_sales (
    sale_id INT PRIMARY KEY,
    category VARCHAR(50),
    sale_month INT,
    amount DECIMAL(10,2)
);

INSERT INTO product_sales VALUES
(1, 'Electronics', 1, 5000),
(2, 'Electronics', 2, 6000),
(3, 'Electronics', 3, 5500),
(4, 'Clothing', 1, 3000),
(5, 'Clothing', 2, 3500),
(6, 'Clothing', 3, 4000),
(7, 'Books', 1, 1500),
(8, 'Books', 2, 1800),
(9, 'Books', 3, 2000);

Question: Create a pivot table showing total sales per category per month, with a total column.

Solution:

SELECT 
    category,
    SUM(CASE WHEN sale_month = 1 THEN amount ELSE 0 END) as jan,
    SUM(CASE WHEN sale_month = 2 THEN amount ELSE 0 END) as feb,
    SUM(CASE WHEN sale_month = 3 THEN amount ELSE 0 END) as mar,
    SUM(amount) as total
FROM product_sales
GROUP BY category
ORDER BY total DESC;

Output:

category    | jan    | feb    | mar    | total
Electronics | 5000   | 6000   | 5500   | 16500
Clothing    | 3000   | 3500   | 4000   | 10500
Books       | 1500   | 1800   | 2000   | 5300

Explanation:

• CASE WHEN creates conditional aggregation (manual PIVOT)
• SUM(CASE ...) zeros out non-matching months
• GROUP BY category creates one row per category
• Total column sums across all months

Problem 8: Optimize Slow Query

Schema:

CREATE TABLE transactions (
    txn_id INT PRIMARY KEY,
    user_id INT,
    txn_type VARCHAR(20),
    amount DECIMAL(10,2),
    txn_date DATE,
    INDEX idx_user (user_id),
    INDEX idx_date (txn_date)
);

Question: The following query is slow on a table with 10 million rows. Optimize it.

-- Slow query
SELECT 
    user_id,
    COUNT(*) as txn_count,
    SUM(amount) as total_amount
FROM transactions
WHERE YEAR(txn_date) = 2024
  AND txn_type = 'purchase'
GROUP BY user_id
HAVING SUM(amount) > 1000
ORDER BY total_amount DESC;

Solution:

-- Optimized query
SELECT 
    user_id,
    COUNT(*) as txn_count,
    SUM(amount) as total_amount
FROM transactions
WHERE txn_date >= '2024-01-01' AND txn_date < '2025-01-01'
  AND txn_type = 'purchase'
GROUP BY user_id
HAVING SUM(amount) > 1000
ORDER BY total_amount DESC;

-- Add composite index
CREATE INDEX idx_date_type_user ON transactions(txn_date, txn_type, user_id);

Optimization Explanation:

Issue	Before	After
Date filter	YEAR(txn_date) = 2024 (function prevents index use)	txn_date >= '2024-01-01' AND txn_date < '2025-01-01' (range scan)
Index usage	Full scan (function on column)	Uses composite index
Coverage	Partial (user_id not in index)	Covering index (all columns in index)

Key Optimizations:

1. Replace YEAR(date) = 2024 with range comparison (index-friendly)
2. Add composite index covering all query columns
3. Composite index order: date (range) → type (equality) → user_id (group by)