Classic SQL Interview Problems

CREATE TEMP TABLE logins AS
SELECT * FROM (VALUES (1,'2024-01-01'::date),(1,'2024-01-02'),(1,'2024-01-03'),(1,'2024-01-05'),(1,'2024-01-06'),
                      (2,'2024-01-01'),(2,'2024-01-03'),(2,'2024-01-04'),(2,'2024-01-05'),(3,'2024-01-02')) v(user_id, day);
CREATE TEMP TABLE seq AS SELECT n FROM generate_series(1, 10) n WHERE n NOT IN (4, 7, 8);
CREATE TEMP TABLE dupes (id serial PRIMARY KEY, email text);
INSERT INTO dupes (email) VALUES ('a@x.com'),('b@x.com'),('a@x.com'),('c@x.com'),('b@x.com'),('a@x.com');
CREATE TEMP TABLE quarterly AS
SELECT * FROM (VALUES ('Keyboard',10,20,15),('Mouse',5,8,9)) v(product, q1, q2, q3);
CREATE TEMP TABLE people AS
SELECT * FROM (VALUES (1,'M'),(2,'F'),(3,'F'),(4,'M')) v(id, sex);
CREATE TEMP TABLE salary_history AS
SELECT * FROM (VALUES (1,'2020-01-01'::date,100),(1,'2022-01-01',120),(1,'2024-01-01',150),
                      (2,'2021-06-01',90),(2,'2023-06-01',95),(3,'2022-03-01',80)) v(emp_id, changed_on, salary);
CREATE TEMP TABLE a_t AS SELECT * FROM (VALUES (1,'x'),(2,'y'),(3,'z')) v(id, val);
CREATE TEMP TABLE b_t AS SELECT * FROM (VALUES (1,'x'),(2,'w'),(4,'v')) v(id, val);
CREATE TEMP TABLE bookings AS
SELECT * FROM (VALUES (1,'A','2024-01-01'::date,'2024-01-05'::date),(2,'A','2024-01-04','2024-01-08'),
                      (3,'A','2024-01-09','2024-01-10'),(4,'B','2024-01-01','2024-01-03'),
                      (5,'B','2024-01-03','2024-01-06')) v(id, room, start_date, end_date);

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

SELECT max(salary) AS second_highest
FROM employees
WHERE salary < (SELECT max(salary) FROM employees);

SELECT DISTINCT salary
FROM (SELECT salary, dense_rank() OVER (ORDER BY salary DESC) AS r FROM employees) t
WHERE r = 3;

SELECT dept_id, name, salary
FROM (SELECT dept_id, name, salary,
             row_number() OVER (PARTITION BY dept_id ORDER BY salary DESC, id) AS rn
      FROM employees WHERE dept_id IS NOT NULL) t
WHERE rn <= 2
ORDER BY dept_id, rn;

SELECT email, count(*) AS n FROM dupes GROUP BY email HAVING count(*) > 1 ORDER BY email;
DELETE FROM dupes
WHERE ctid IN (SELECT ctid FROM (SELECT ctid, row_number() OVER (PARTITION BY email ORDER BY id) AS rn
                                 FROM dupes) t
               WHERE rn > 1);
SELECT * FROM dupes ORDER BY id;

SELECT e.name, e.dept_id, e.salary
FROM employees e
WHERE e.salary > (SELECT avg(salary) FROM employees WHERE dept_id = e.dept_id)
ORDER BY e.id;

CREATE TEMP TABLE emp_copy AS SELECT * FROM employees;
UPDATE emp_copy SET salary = 200000 WHERE name = 'Kiran';
SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM emp_copy e JOIN emp_copy m ON m.id = e.manager_id
WHERE e.salary > m.salary;

SELECT 'customer' AS kind, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
UNION ALL
SELECT 'department', d.name
FROM departments d LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.id IS NULL;

SELECT d.name, sum(e.salary) AS payroll,
       round(100.0 * sum(e.salary) / sum(sum(e.salary)) OVER (), 1) AS pct_of_total
FROM employees e JOIN departments d ON d.id = e.dept_id
GROUP BY d.name
ORDER BY payroll DESC
LIMIT 1;

WITH o AS (SELECT oi.order_id, sum(p.price * oi.quantity) AS amt
           FROM order_items oi JOIN products p ON p.id = oi.product_id
           GROUP BY oi.order_id)
SELECT order_id, amt,
       sum(amt) OVER w AS running,
       round(100 * sum(amt) OVER w / sum(amt) OVER (), 1) AS cum_pct
FROM o
WINDOW w AS (ORDER BY order_id)
ORDER BY order_id;

WITH m AS (SELECT date_trunc('month', o.order_date)::date AS month, sum(p.price * oi.quantity) AS rev
           FROM orders o JOIN order_items oi ON oi.order_id = o.id
                         JOIN products p ON p.id = oi.product_id
           WHERE o.status <> 'cancelled'
           GROUP BY 1)
SELECT month, rev,
       rev - lag(rev) OVER (ORDER BY month) AS change,
       round(100 * (rev - lag(rev) OVER (ORDER BY month)) / lag(rev) OVER (ORDER BY month), 1) AS pct_change
FROM m;

SELECT user_id, min(day) AS start_day, max(day) AS end_day, count(*) AS days
FROM (SELECT user_id, day,
             day - (row_number() OVER (PARTITION BY user_id ORDER BY day))::int AS grp
      FROM logins) t
GROUP BY user_id, grp
ORDER BY user_id, start_day;

SELECT g AS missing FROM generate_series(1, 10) g
EXCEPT
SELECT n FROM seq
ORDER BY 1;

SELECT c.name,
       count(*) FILTER (WHERE o.status = 'delivered') AS delivered,
       count(*) FILTER (WHERE o.status = 'shipped')   AS shipped,
       count(*) FILTER (WHERE o.status = 'placed')    AS placed,
       count(*) FILTER (WHERE o.status = 'cancelled') AS cancelled
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY c.name;

SELECT q.product, x.quarter, x.units
FROM quarterly q
CROSS JOIN LATERAL (VALUES ('q1', q.q1), ('q2', q.q2), ('q3', q.q3)) AS x(quarter, units)
ORDER BY q.product, x.quarter;

SELECT dept_id, percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) AS median
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY dept_id;

UPDATE people SET sex = CASE sex WHEN 'M' THEN 'F' ELSE 'M' END;
SELECT * FROM people ORDER BY id;

DELETE FROM salary_history h
WHERE EXISTS (SELECT 1 FROM salary_history n
              WHERE n.emp_id = h.emp_id AND n.changed_on > h.changed_on)
RETURNING *;
SELECT * FROM salary_history ORDER BY emp_id;

SELECT customer_id, min(order_date) AS first_date, max(order_date) AS last_date,
       (array_agg(id ORDER BY order_date, id))[1]           AS first_id,
       (array_agg(id ORDER BY order_date DESC, id DESC))[1] AS last_id
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

SELECT c.name
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN customers c ON c.id = o.customer_id
WHERE oi.product_id IN (1, 2, 4)
GROUP BY c.name
HAVING count(DISTINCT oi.product_id) = 3;

SELECT name, hired_on
FROM employees
WHERE hired_on > DATE '2024-06-01' - INTERVAL '24 months'
ORDER BY hired_on;

SELECT DISTINCT user_id
FROM (SELECT user_id, day, lead(day, 2) OVER (PARTITION BY user_id ORDER BY day) AS day_plus_2
      FROM logins) t
WHERE day_plus_2 = day + 2
ORDER BY user_id;

WITH RECURSIVE t AS (
  SELECT id, name, 0 AS depth FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, t.depth + 1 FROM employees e JOIN t ON e.manager_id = t.id)
SELECT name, depth FROM t ORDER BY depth, id;

WITH f AS (SELECT customer_id, min(order_date) AS d FROM orders GROUP BY customer_id)
SELECT d, count(*) AS new_customers, sum(count(*)) OVER (ORDER BY d) AS total_customers
FROM f
GROUP BY d
ORDER BY d;

WITH m AS (SELECT DISTINCT customer_id, date_trunc('month', order_date)::date AS month
           FROM orders WHERE status <> 'cancelled')
SELECT a.month, count(*) AS buyers, count(b.customer_id) AS retained,
       round(100.0 * count(b.customer_id) / count(*), 1) AS pct
FROM m a
LEFT JOIN m b ON b.customer_id = a.customer_id AND b.month = a.month + INTERVAL '1 month'
GROUP BY a.month
ORDER BY a.month;

SELECT 'only_in_a' AS side, * FROM (SELECT * FROM a_t EXCEPT SELECT * FROM b_t) x
UNION ALL
SELECT 'only_in_b', * FROM (SELECT * FROM b_t EXCEPT SELECT * FROM a_t) y
ORDER BY 1, 2;

SELECT a.id AS booking_a, b.id AS booking_b, a.room
FROM bookings a JOIN bookings b
  ON a.room = b.room AND a.id < b.id
 AND a.start_date <= b.end_date AND b.start_date <= a.end_date
ORDER BY 1, 2;

WITH s AS (SELECT p.category, p.name, sum(oi.quantity) AS units
           FROM order_items oi
           JOIN products p ON p.id = oi.product_id
           JOIN orders o ON o.id = oi.order_id
           WHERE o.status <> 'cancelled'
           GROUP BY p.category, p.name)
SELECT DISTINCT ON (category) category, name, units
FROM s
ORDER BY category, units DESC, name;

SELECT name, salary,
       row_number() OVER w AS row_num,
       rank()       OVER w AS rnk,
       dense_rank() OVER w AS dense
FROM employees
WHERE dept_id = 1
WINDOW w AS (ORDER BY salary DESC)
ORDER BY salary DESC, id;

WITH d AS (SELECT dept_id, count(*) AS staff, sum(salary) AS payroll, round(avg(salary)) AS avg_salary
           FROM employees GROUP BY dept_id),
     top AS (SELECT DISTINCT ON (dept_id) dept_id, name AS top_earner
             FROM employees ORDER BY dept_id, salary DESC, id)
SELECT dep.name AS department, d.staff, d.payroll, d.avg_salary, top.top_earner
FROM departments dep
JOIN d   ON d.dept_id = dep.id
JOIN top ON top.dept_id = dep.id
ORDER BY d.payroll DESC;