SQL: JOIN, GROUP BY и агрегаты

# shop.sql is the script from the "SQL from scratch" lesson
docker run -d --rm --name shop-db -e POSTGRES_PASSWORD=test postgres:17-alpine
docker exec -i shop-db psql -U postgres < shop.sql
docker exec -it shop-db psql -U postgres -P null=NULL

SELECT o.id, o.ordered_on, o.status, c.name
FROM orders AS o
JOIN clients AS c ON c.id = o.client_id
ORDER BY o.id;

SELECT o.id AS order_id, c.name AS client, p.name AS product,
       oi.quantity, oi.price, oi.quantity * oi.price AS line_total
FROM orders AS o
JOIN clients AS c      ON c.id = o.client_id
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p     ON p.id = oi.product_id
WHERE o.id IN (1, 5)
ORDER BY o.id, p.name;

SELECT c.id, c.name, o.id AS order_id, o.status
FROM clients AS c
JOIN orders AS o ON o.client_id = c.id
ORDER BY c.id, o.id;

SELECT c.id, c.name, o.id AS order_id, o.status
FROM clients AS c
LEFT JOIN orders AS o ON o.client_id = c.id
ORDER BY c.id, o.id;

SELECT c.id, c.name, c.created_on
FROM clients AS c
LEFT JOIN orders AS o ON o.client_id = c.id
WHERE o.id IS NULL
ORDER BY c.id;

SELECT p.id, p.name
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;

-- Intent: every client plus their paid orders
SELECT c.name, o.id AS order_id, o.status
FROM clients AS c
LEFT JOIN orders AS o ON o.client_id = c.id
WHERE o.status = 'paid'
ORDER BY c.id, o.id;

SELECT c.name, o.id AS order_id, o.status
FROM clients AS c
LEFT JOIN orders AS o ON o.client_id = c.id AND o.status = 'paid'
ORDER BY c.id, o.id;

SELECT count(*)        AS products,
       count(weight_g) AS with_weight,
       min(price)      AS cheapest,
       max(price)      AS most_expensive,
       round(avg(price), 2) AS avg_price,
       avg(weight_g)   AS avg_weight
FROM products;

SELECT count(*) AS orders, count(promo_code) AS with_promo,
       count(DISTINCT promo_code) AS distinct_promos
FROM orders;

--  orders | with_promo | distinct_promos
-- --------+------------+-----------------
--      10 |          3 |               2
-- (1 row)

SELECT sum(quantity * price) AS revenue
FROM order_items
WHERE order_id = 10;
SELECT coalesce(sum(quantity * price), 0) AS revenue
FROM order_items
WHERE order_id = 10;
SELECT count(*) AS lines
FROM order_items
WHERE order_id = 10;

--  revenue
-- ---------
--     NULL
-- (1 row)
--
--  revenue
-- ---------
--        0
-- (1 row)
--
--  lines
-- -------
--      0
-- (1 row)

SELECT count(*) AS orders,
       count(*) FILTER (WHERE status = 'cancelled') AS cancelled,
       count(*) FILTER (WHERE promo_code IS NOT NULL) AS with_promo
FROM orders;

SELECT p.category,
       sum(oi.quantity)            AS units,
       sum(oi.quantity * oi.price) AS revenue
FROM order_items AS oi
JOIN orders AS o   ON o.id = oi.order_id
JOIN products AS p ON p.id = oi.product_id
WHERE o.status IN ('paid', 'shipped')
GROUP BY p.category
ORDER BY revenue DESC;

SELECT p.category, p.name, sum(oi.quantity) AS units
FROM order_items AS oi
JOIN products AS p ON p.id = oi.product_id
GROUP BY p.category;

-- ERROR:  column "p.name" must appear in the GROUP BY clause or be used in an aggregate function
-- LINE 1: SELECT p.category, p.name, sum(oi.quantity) AS units
--                            ^

SELECT c.name, count(*) AS orders
FROM clients AS c
JOIN orders AS o ON o.client_id = c.id
WHERE o.status <> 'cancelled'
GROUP BY c.id, c.name
HAVING count(*) >= 2
ORDER BY orders DESC, c.name;

SELECT client_id, count(*)
FROM orders
WHERE count(*) >= 2
GROUP BY client_id;

-- ERROR:  aggregate functions are not allowed in WHERE
-- LINE 3: WHERE count(*) >= 2
--               ^

SELECT city, count(*) AS clients
FROM clients
GROUP BY city
ORDER BY clients DESC, city;

SELECT name, price
FROM products
WHERE price > (SELECT avg(price) FROM products)
ORDER BY price DESC;

SELECT date_trunc('month', o.ordered_on)::date AS month,
       count(DISTINCT o.id)                    AS orders,
       sum(oi.quantity * oi.price)             AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.status IN ('paid', 'shipped')
GROUP BY month
ORDER BY month;

WITH order_totals AS (
    SELECT o.id, o.client_id, sum(oi.quantity * oi.price) AS total
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.id
    WHERE o.status IN ('paid', 'shipped')
    GROUP BY o.id, o.client_id
)
SELECT c.name,
       count(*)              AS orders,
       sum(t.total)          AS revenue,
       round(avg(t.total))   AS avg_check
FROM order_totals AS t
JOIN clients AS c ON c.id = t.client_id
GROUP BY c.id, c.name
ORDER BY revenue DESC;

SELECT name FROM products
WHERE id NOT IN (SELECT product_id FROM order_items);
SELECT name FROM products
WHERE id NOT IN (1, 2, 3, 4, 5, 6, 7, 8, NULL);
SELECT p.name FROM products AS p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items AS oi WHERE oi.product_id = p.id
);

--    name
-- ----------
--  Gift Box
-- (1 row)
--
--  name
-- ------
-- (0 rows)
--
--    name
-- ----------
--  Gift Box
-- (1 row)

-- Wrong: counts order lines, not orders
SELECT c.name, count(*) AS orders
FROM clients AS c
JOIN orders AS o       ON o.client_id = c.id
JOIN order_items AS oi ON oi.order_id = o.id
GROUP BY c.id, c.name
ORDER BY c.id;

SELECT c.name,
       count(DISTINCT o.id)        AS orders,
       sum(oi.quantity * oi.price) AS total
FROM clients AS c
JOIN orders AS o       ON o.client_id = c.id
JOIN order_items AS oi ON oi.order_id = o.id
GROUP BY c.id, c.name
ORDER BY c.id;

SELECT c.name, count(o.id) AS orders, count(*) AS rows_in_group
FROM clients AS c
LEFT JOIN orders AS o ON o.client_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;