Advanced Practice: Subqueries, CTEs and Window Functions

Subqueries

1. Employees earning more than the company average.

SELECT first_name, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

2. Employees earning more than their own department average.

SELECT e.first_name, e.dept_id, e.salary
FROM   employees e
WHERE  e.salary > (SELECT AVG(e2.salary)
                   FROM   employees e2
                   WHERE  e2.dept_id = e.dept_id)
ORDER BY e.dept_id, e.salary DESC;

Correlated: the threshold differs per row, so the inner query must see e.dept_id from the outer one.

3. Departments with no employees, three different ways.

-- NOT EXISTS: NULL safe, the one to prefer
SELECT d.name FROM departments d
WHERE  NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

-- LEFT JOIN ... IS NULL: test the right table's KEY
SELECT d.name FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE  e.id IS NULL;

-- NOT IN: only correct if the NULLs are excluded
SELECT d.name FROM departments d
WHERE  d.id NOT IN (SELECT dept_id FROM employees WHERE dept_id IS NOT NULL);

4. The customer with the highest lifetime value.

WITH totals AS (
    SELECT customer_id, SUM(total) AS lifetime_value
    FROM   orders WHERE status <> 'cancelled'
    GROUP BY customer_id
)
SELECT c.name, t.lifetime_value
FROM   totals t
JOIN   customers c ON c.id = t.customer_id
WHERE  t.lifetime_value = (SELECT MAX(lifetime_value) FROM totals);

CTEs

5. Each customer with their order count, lifetime value and average order value.

WITH stats AS (
    SELECT customer_id,
           COUNT(*)   AS order_count,
           SUM(total) AS lifetime_value
    FROM   orders
    WHERE  status <> 'cancelled'
    GROUP BY customer_id
)
SELECT c.name,
       COALESCE(s.order_count, 0)    AS orders,
       COALESCE(s.lifetime_value, 0) AS lifetime_value,
       ROUND(COALESCE(s.lifetime_value, 0) / NULLIF(s.order_count, 0), 2) AS avg_order
FROM   customers c
LEFT JOIN stats s ON s.customer_id = c.id
ORDER BY lifetime_value DESC;

6. The full management chain below Asha, with depth.

WITH RECURSIVE chain AS (
    SELECT id, first_name, manager_id, 1 AS depth,
           CAST(first_name AS CHAR(200)) AS path
    FROM   employees WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.first_name, e.manager_id, c.depth + 1,
           CONCAT(c.path, ' > ', e.first_name)
    FROM   employees e
    JOIN   chain c ON c.id = e.manager_id
    WHERE  c.depth < 50
)
SELECT depth, first_name, path FROM chain ORDER BY path;

7. Every month of 2024, including months with no orders.

WITH RECURSIVE months AS (
    SELECT DATE('2024-01-01') AS month_start
    UNION ALL
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH) FROM months
    WHERE  month_start < '2024-12-01'
)
SELECT m.month_start,
       COUNT(o.id)               AS orders,
       COALESCE(SUM(o.total), 0) AS revenue
FROM   months m
LEFT JOIN orders o
       ON o.order_date >= m.month_start
      AND o.order_date <  DATE_ADD(m.month_start, INTERVAL 1 MONTH)
GROUP BY m.month_start
ORDER BY m.month_start;

Window functions

8. Each employee's salary alongside their department average and the difference.

SELECT first_name, dept_id, salary,
       ROUND(AVG(salary) OVER (PARTITION BY dept_id), 2)          AS dept_avg,
       ROUND(salary - AVG(salary) OVER (PARTITION BY dept_id), 2) AS difference
FROM   employees
ORDER BY dept_id, salary DESC;

9. The highest paid employee in each department.

WITH ranked AS (
    SELECT first_name, dept_id, salary,
           ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, id) AS rn
    FROM   employees
    WHERE  dept_id IS NOT NULL
)
SELECT first_name, dept_id, salary FROM ranked WHERE rn = 1 ORDER BY dept_id;

10. The third highest distinct salary.

WITH ranked AS (
    SELECT DISTINCT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM   employees
)
SELECT salary FROM ranked WHERE rnk = 3;

DENSE_RANK over distinct salaries. ROW_NUMBER would count two people on the same salary as two different levels.

11. Running revenue total by order date.

SELECT order_date, total,
       SUM(total) OVER (ORDER BY order_date, id
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM   orders
ORDER BY order_date, id;

12. Days between each customer's consecutive orders.

SELECT customer_id, order_date, total,
       LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order,
       DATEDIFF(order_date,
                LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date))
                                                                            AS days_between
FROM   orders
ORDER BY customer_id, order_date;

Check your understanding

1. In question 3, why does the NOT IN version need WHERE dept_id IS NOT NULL?
2. In question 9, what does the , id in the window ORDER BY guarantee?
3. In question 11, what changes if you drop the explicit ROWS frame?