Intermediate SQL Problems

Problem 1: Top Earners by Department

Schema:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10,2),
    hire_date DATE
);

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100),
    location VARCHAR(50)
);

INSERT INTO departments VALUES
(1, 'Engineering', 'Seattle'),
(2, 'Marketing', 'New York'),
(3, 'HR', 'Chicago'),
(4, 'Finance', 'Boston');

INSERT INTO employees VALUES
(1, 'Alice', 1, 95000, '2020-01-15'),
(2, 'Bob', 2, 65000, '2019-03-20'),
(3, 'Charlie', 1, 105000, '2018-07-10'),
(4, 'Diana', 3, 72000, '2021-06-01'),
(5, 'Eve', 1, 110000, '2017-11-25'),
(6, 'Frank', 2, 58000, '2022-02-14'),
(7, 'Grace', 3, 68000, '2020-09-30'),
(8, 'Henry', 4, 92000, '2019-08-12'),
(9, 'Ivy', 4, 88000, '2021-03-15');

Question: Find the highest-paid employee in each department, showing department name, employee name, and salary.

Solution:

-- Method 1: Window function
SELECT dept_name, emp_name, salary
FROM (
    SELECT 
        d.dept_name,
        e.emp_name,
        e.salary,
        RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) as rank
    FROM employees e
    JOIN departments d ON e.department_id = d.dept_id
) ranked
WHERE rank = 1;

-- Method 2: Correlated subquery
SELECT d.dept_name, e.emp_name, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
WHERE e.salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department_id = e.department_id
);

Output:

department  | emp_name | salary
Engineering | Eve      | 110000
Marketing   | Bob      | 65000
HR          | Diana    | 72000
Finance     | Henry    | 92000

Explanation:

• RANK() assigns rank 1 to highest salary per department
• PARTITION BY department_id groups the ranking
• Filter rank = 1 to get top earner per department

Problem 2: Consecutive Days Login

Schema:

CREATE TABLE user_logins (
    user_id INT,
    login_date DATE,
    PRIMARY KEY (user_id, login_date)
);

INSERT INTO user_logins VALUES
(1, '2024-01-01'), (1, '2024-01-02'), (1, '2024-01-03'), (1, '2024-01-05'),
(2, '2024-01-01'), (2, '2024-01-03'), (2, '2024-01-05'),
(3, '2024-01-01'), (3, '2024-01-02'), (3, '2024-01-03'), (3, '2024-01-04'), (3, '2024-01-05');

Question: Find users who logged in for 3 or more consecutive days. Show user_id and the consecutive streak length.

Solution:

WITH consecutive AS (
    SELECT 
        user_id,
        login_date,
        login_date - INTERVAL ROW_NUMBER() OVER (
            PARTITION BY user_id ORDER BY login_date
        ) DAY as grp
    FROM user_logins
)
SELECT 
    user_id,
    MIN(login_date) as streak_start,
    MAX(login_date) as streak_end,
    COUNT(*) as streak_length
FROM consecutive
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY user_id;

Output:

user_id | streak_start | streak_end | streak_length
1       | 2024-01-01   | 2024-01-03 | 3
3       | 2024-01-01   | 2024-01-05 | 5

Explanation:

• ROW_NUMBER() assigns sequential numbers to each user's login dates
• Subtracting row number from date creates a group identifier for consecutive sequences
• GROUP BY the group identifier finds consecutive streaks
• HAVING COUNT(*) >= 3 filters for 3+ day streaks

Problem 3: Year-Over-Year Growth

Schema:

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
);

INSERT INTO sales VALUES
(1, 101, '2023-01-15', 5000),
(2, 101, '2023-02-20', 6000),
(3, 101, '2023-03-10', 4500),
(4, 101, '2024-01-12', 5500),
(5, 101, '2024-02-18', 7200),
(6, 101, '2024-03-22', 5800),
(7, 102, '2023-01-20', 3000),
(8, 102, '2024-01-25', 3500);

Question: Calculate the year-over-year growth rate for each product, showing the current year total, previous year total, and growth percentage.

Solution:

WITH yearly_sales AS (
    SELECT 
        product_id,
        YEAR(sale_date) as sale_year,
        SUM(amount) as total_sales
    FROM sales
    GROUP BY product_id, YEAR(sale_date)
)
SELECT 
    curr.product_id,
    curr.sale_year as current_year,
    curr.total_sales as current_sales,
    prev.total_sales as previous_sales,
    ROUND((curr.total_sales - prev.total_sales) / prev.total_sales * 100, 2) as growth_pct
FROM yearly_sales curr
LEFT JOIN yearly_sales prev 
    ON curr.product_id = prev.product_id 
    AND curr.sale_year = prev.sale_year + 1
WHERE prev.total_sales IS NOT NULL
ORDER BY curr.product_id, curr.sale_year;

Output:

product_id | current_year | current_sales | previous_sales | growth_pct
101        | 2024         | 18500.00      | 15500.00       | 19.35
102        | 2024         | 3500.00       | 3000.00        | 16.67

Explanation:

• CTE aggregates sales by product and year
• Self-join matches each year with the previous year
• Growth formula: (current - previous) / previous × 100

Problem 4: Running Total

Schema: (same sales table)

Question: Calculate the running total of sales for each product, ordered by date.

Solution:

SELECT 
    product_id,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY product_id 
        ORDER BY sale_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total
FROM sales
ORDER BY product_id, sale_date;

Output:

product_id | sale_date  | amount | running_total
101        | 2023-01-15 | 5000   | 5000
101        | 2023-02-20 | 6000   | 11000
101        | 2023-03-10 | 4500   | 15500
101        | 2024-01-12 | 5500   | 21000
101        | 2024-02-18 | 7200   | 28200
101        | 2024-03-22 | 5800   | 34000
102        | 2023-01-20 | 3000   | 3000
102        | 2024-01-25 | 3500   | 6500

Explanation:

• SUM() OVER (ORDER BY) creates running total
• PARTITION BY resets total per product
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all rows up to current
• Useful for cumulative metrics, account balances, inventory levels

Problem 5: Customer Cohort Analysis

Schema:

CREATE TABLE customer_orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2)
);

INSERT INTO customer_orders VALUES
(1, 101, '2023-01-15', 100),
(2, 101, '2023-02-20', 150),
(3, 101, '2023-06-10', 200),
(4, 102, '2023-01-20', 80),
(5, 102, '2023-03-15', 120),
(6, 103, '2023-02-01', 90),
(7, 103, '2023-04-20', 110),
(8, 103, '2023-07-15', 130),
(9, 104, '2023-03-10', 200);

Question: For each customer, find their first order date (cohort month) and count how many months they were active (had at least one order).

Solution:

WITH customer_cohort AS (
    SELECT 
        customer_id,
        MIN(order_date) as first_order_date,
        DATE_FORMAT(MIN(order_date), '%Y-%m') as cohort_month
    FROM customer_orders
    GROUP BY customer_id
),
active_months AS (
    SELECT 
        co.customer_id,
        co.cohort_month,
        TIMESTAMPDIFF(MONTH, co.first_order_date, MAX(co2.order_date)) as active_months,
        COUNT(DISTINCT DATE_FORMAT(co2.order_date, '%Y-%m')) as months_with_orders
    FROM customer_cohort co
    JOIN customer_orders co2 ON co.customer_id = co2.customer_id
    GROUP BY co.customer_id, co.cohort_month, co.first_order_date
)
SELECT 
    customer_id,
    cohort_month,
    active_months,
    months_with_orders
FROM active_months
ORDER BY cohort_month, customer_id;

Output:

customer_id | cohort_month | active_months | months_with_orders
101         | 2023-01      | 5             | 3
102         | 2023-01      | 2             | 2
103         | 2023-02      | 5             | 3
104         | 2023-03      | 0             | 1

Explanation:

• First CTE finds each customer's first order (cohort)
• Second CTE calculates months active and months with orders
• Cohort analysis helps understand customer retention patterns

Problem 6: Find Managers with Most Reports

Schema:

CREATE TABLE staff (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    manager_id INT,
    department VARCHAR(50)
);

INSERT INTO staff VALUES
(1, 'Alice', NULL, 'Executive'),
(2, 'Bob', 1, 'Engineering'),
(3, 'Charlie', 1, 'Engineering'),
(4, 'Diana', 2, 'Engineering'),
(5, 'Eve', 2, 'Engineering'),
(6, 'Frank', 2, 'Engineering'),
(7, 'Grace', 3, 'Marketing'),
(8, 'Henry', 3, 'Marketing'),
(9, 'Ivy', NULL, 'Executive');

Question: Find all managers and count their direct reports. Show only managers with 2 or more direct reports, sorted by report count descending.

Solution:

SELECT 
    m.emp_name as manager_name,
    m.department,
    COUNT(s.emp_id) as direct_reports
FROM staff s
JOIN staff m ON s.manager_id = m.emp_id
GROUP BY m.emp_id, m.emp_name, m.department
HAVING COUNT(s.emp_id) >= 2
ORDER BY direct_reports DESC;

Output:

manager_name | department  | direct_reports
Bob          | Engineering | 3
Alice        | Executive   | 2
Charlie      | Engineering | 2

Explanation:

• Self-join: staff s (reports) joins to staff m (managers)
• GROUP BY manager counts reports per manager
• HAVING filters for 2+ reports
• Useful for org chart analysis

Problem 7: Products Never Ordered

Schema:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2)
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT
);

INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 999.99),
(2, 'Mouse', 'Electronics', 29.99),
(3, 'Keyboard', 'Electronics', 79.99),
(4, 'Desk', 'Furniture', 299.99),
(5, 'Chair', 'Furniture', 199.99),
(6, 'Monitor', 'Electronics', 449.99);

INSERT INTO order_items VALUES
(1, 101, 1, 2),
(2, 101, 2, 5),
(3, 102, 3, 1),
(4, 103, 4, 3);

Question: Find all products that have never been ordered. Show product name and category.

Solution:

-- Method 1: LEFT JOIN
SELECT p.product_name, p.category
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.item_id IS NULL;

-- Method 2: NOT IN
SELECT product_name, category
FROM products
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM order_items);

-- Method 3: NOT EXISTS
SELECT p.product_name, p.category
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id);

Output:

product_name | category
Chair        | Furniture
Monitor      | Electronics

Explanation:

• Products 5 (Chair) and 6 (Monitor) have no entries in order_items
• LEFT JOIN returns NULL for non-matching rows
• NOT IN and NOT EXISTS achieve the same result

Problem 8: Percentile Calculation

Schema: (same employees table from Problem 1)

Question: Calculate the salary percentile for each employee within their department. Show employee name, department, salary, and percentile rank.

Solution:

SELECT 
    emp_name,
    department,
    salary,
    ROUND(PERCENT_RANK() OVER (
        PARTITION BY department 
        ORDER BY salary
    ) * 100, 2) as percentile
FROM employees
ORDER BY department, salary;

Output:

emp_name | department  | salary | percentile
Henry    | Finance     | 92000  | 0.00
Alice    | Engineering | 95000  | 0.00
Henry    | Engineering | 98000  | 33.33
Charlie  | Engineering | 105000 | 66.67
Eve      | Engineering | 110000 | 100.00
Diana    | HR          | 72000  | 0.00
Grace    | HR          | 68000  | 100.00
Frank    | Marketing   | 58000  | 0.00
Bob      | Marketing   | 65000  | 100.00

Explanation:

• PERCENT_RANK() returns value between 0 and 1
• Multiply by 100 for percentage
• 0 = lowest in group, 100 = highest in group
• Useful for salary benchmarking, performance reviews

Problem 9: Gaps and Islands

Schema:

CREATE TABLE server_uptime (
    server_id INT,
    check_date DATE,
    status VARCHAR(10),  -- 'up' or 'down'
    PRIMARY KEY (server_id, check_date)
);

INSERT INTO server_uptime VALUES
(1, '2024-01-01', 'up'),
(1, '2024-01-02', 'up'),
(1, '2024-01-03', 'down'),
(1, '2024-01-04', 'down'),
(1, '2024-01-05', 'up'),
(1, '2024-01-06', 'up'),
(1, '2024-01-07', 'up'),
(2, '2024-01-01', 'up'),
(2, '2024-01-02', 'down'),
(2, '2024-01-03', 'up');

Question: Find the longest consecutive uptime streak for each server. Show server_id, streak start, streak end, and length.

Solution:

WITH streaks AS (
    SELECT 
        server_id,
        check_date,
        status,
        check_date - INTERVAL ROW_NUMBER() OVER (
            PARTITION BY server_id, status ORDER BY check_date
        ) DAY as streak_group
    FROM server_uptime
    WHERE status = 'up'
)
SELECT 
    server_id,
    MIN(check_date) as streak_start,
    MAX(check_date) as streak_end,
    COUNT(*) as streak_length
FROM streaks
GROUP BY server_id, streak_group
ORDER BY server_id, streak_length DESC;

Output:

server_id | streak_start | streak_end | streak_length
1         | 2024-01-05   | 2024-01-07 | 3
1         | 2024-01-01   | 2024-01-02 | 2
2         | 2024-01-01   | 2024-01-01 | 1
2         | 2024-01-03   | 2024-01-03 | 1

Explanation:

• Gaps and Islands technique identifies consecutive sequences
• ROW_NUMBER() creates group identifier for consecutive dates
• GROUP BY streak_group finds each streak's boundaries
• Useful for uptime analysis, attendance tracking, session analysis

Problem 10: Pivot Table

Schema:

CREATE TABLE survey_responses (
    response_id INT PRIMARY KEY,
    employee_id INT,
    question VARCHAR(100),
    rating INT  -- 1-5
);

INSERT INTO survey_responses VALUES
(1, 101, 'Work-Life Balance', 4),
(2, 101, 'Compensation', 3),
(3, 101, 'Management', 5),
(4, 102, 'Work-Life Balance', 2),
(5, 102, 'Compensation', 4),
(6, 102, 'Management', 3),
(7, 103, 'Work-Life Balance', 5),
(8, 103, 'Compensation', 5),
(9, 103, 'Management', 4);

Question: Create a pivot table showing each employee's ratings across all questions.

Solution:

SELECT 
    employee_id,
    MAX(CASE WHEN question = 'Work-Life Balance' THEN rating END) as work_life_balance,
    MAX(CASE WHEN question = 'Compensation' THEN rating END) as compensation,
    MAX(CASE WHEN question = 'Management' THEN rating END) as management
FROM survey_responses
GROUP BY employee_id
ORDER BY employee_id;

Output:

employee_id | work_life_balance | compensation | management
101         | 4                 | 3            | 5
102         | 2                 | 4            | 3
103         | 5                 | 5            | 4

Explanation:

• CASE WHEN creates conditional columns
• MAX() aggregates the pivoted values
• GROUP BY employee_id creates one row per employee
• This manual pivot works in all SQL databases
• PostgreSQL has CROSSTAB(), MySQL has PIVOT in some versions