A grab-bag of questions that test whether you think in sets: relational division, conditional counts, comparisons against aggregates, and self-referencing updates.

Customers who bought every product in a category (relational division)

SQL

-- customers who bought ALL Furniture products (Desk and Chair)
SELECT c.name
FROM customers c
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
JOIN products p     ON p.product_id  = oi.product_id
WHERE p.category = 'Furniture'
GROUP BY c.customer_id, c.name
HAVING COUNT(DISTINCT p.product_id) = (SELECT COUNT(*) FROM products WHERE category = 'Furniture')
ORDER BY c.name;

Output

name
-----
Sneha

Customers who ordered in every month of Q1

SQL

SELECT c.name
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01' AND o.order_date < '2024-04-01'
GROUP BY c.customer_id, c.name
HAVING COUNT(DISTINCT strftime('%m', o.order_date)) = 3;

Output

name
------
Ananya

Products never ordered together with a Laptop

SQL

SELECT p.name FROM products p
WHERE p.name <> 'Laptop'
  AND NOT EXISTS (
      SELECT 1
      FROM order_items a
      JOIN order_items b ON b.order_id = a.order_id
      JOIN products l    ON l.product_id = b.product_id AND l.name = 'Laptop'
      WHERE a.product_id = p.product_id
  )
ORDER BY p.name;

Output

name
--------
Chair
Desk
Monitor
Notebook
Pen

Share of total per row

SQL

SELECT category,
       SUM(price) AS catalogue_value,
       ROUND(100.0 * SUM(price) / (SELECT SUM(price) FROM products), 1) AS pct
FROM products
GROUP BY category
ORDER BY pct DESC;

Output

category    | catalogue_value | pct
------------+-----------------+-----
Electronics | 69500           | 84.7
Furniture   | 12500           | 15.2
Stationery  | 80              | 0.1

Swap two values in one UPDATE

SQL

CREATE TABLE people (id INTEGER PRIMARY KEY, name TEXT, gender TEXT);
INSERT INTO people VALUES (1, 'A', 'm'), (2, 'B', 'f'), (3, 'C', 'm');

UPDATE people SET gender = CASE gender WHEN 'm' THEN 'f' ELSE 'm' END;
SELECT * FROM people ORDER BY id;

Output

id | name | gender
---+------+-------
1  | A    | f
2  | B    | m
3  | C    | f

Year-over-year style comparison with a self join

SQL

WITH monthly AS (
    SELECT CAST(strftime('%m', o.order_date) AS INTEGER) AS m, COUNT(*) AS orders
    FROM orders o GROUP BY m
)
SELECT cur.m AS month, cur.orders, prev.orders AS prev_month_orders
FROM monthly cur
LEFT JOIN monthly prev ON prev.m = cur.m - 1
ORDER BY cur.m;

Output

month | orders | prev_month_orders
------+--------+------------------
1     | 2      | NULL
2     | 3      | 2
3     | 3      | 3

Second most recent order of each customer

SQL

SELECT customer_id, order_id, order_date
FROM (
    SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders o
)
WHERE rn = 2
ORDER BY customer_id;

Output

customer_id | order_id | order_date
------------+----------+-----------
1           | 103      | 2024-02-03
2           | 102      | 2024-01-12
3           | 104      | 2024-02-14

How do you find customers who bought all products (relational division)?