SQL Joins and Aggregation

# SQL Joins and Aggregation

Zerethon Official Sample `sql-aggregation`, version 1.0.0, SQL Playground (SQLite 3).

- `setup/schema.sql` — customers, products, orders and order items (made-up data); runs first on every run
- `data/products.csv` — the products, imported into `products` after `setup/` (the first row names the columns)
- `main.sql` — entry file: LEFT JOIN, GROUP BY with HAVING, a CTE, window functions (running total, rank)
- `test_aggregation.sql` — checks; set it as the entry file to run them (or Run ▾ → Run tests)

Every run starts from a new, empty in-memory database (Run is also Reset). There is no server and
no real data: nothing you run here reaches any other database.

This project is your own copy. Changing it never changes the official sample.

name,category,price
Notebook,Paper,2.5
Pen,Paper,1.0
Backpack,Bags,30.0
Lunch box,Kitchen,12.0
Bottle,Kitchen,8.0

-- SQL Joins and Aggregation 1.0.0 (Zerethon Official Sample). Each statement's result
-- appears as its own table. Every run starts again from setup/schema.sql.

-- 1. LEFT JOIN keeps customers without orders (Dung and Em have none)
SELECT c.name, count(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY orders DESC, c.name;

-- 2. Revenue per category, only categories above 30 (HAVING filters groups, WHERE filters rows)
SELECT p.category, sum(i.quantity * p.price) AS revenue
FROM order_items i
JOIN products p ON p.id = i.product_id
GROUP BY p.category
HAVING revenue > 30
ORDER BY revenue DESC;

-- 3. A CTE names a step: order totals, then the average order per city
WITH order_totals AS (
    SELECT o.id, o.customer_id, sum(i.quantity * p.price) AS total
    FROM orders o
    JOIN order_items i ON i.order_id = o.id
    JOIN products p ON p.id = i.product_id
    GROUP BY o.id
)
SELECT c.city, count(*) AS orders, round(avg(t.total), 2) AS average_order
FROM order_totals t
JOIN customers c ON c.id = t.customer_id
GROUP BY c.city
ORDER BY average_order DESC;

-- 4. Window functions: a running total by date, and each order's rank by size
WITH order_totals AS (
    SELECT o.id, o.ordered_on, sum(i.quantity * p.price) AS total
    FROM orders o
    JOIN order_items i ON i.order_id = o.id
    JOIN products p ON p.id = i.product_id
    GROUP BY o.id
)
SELECT ordered_on, total,
       sum(total) OVER (ORDER BY ordered_on) AS running_total,
       rank() OVER (ORDER BY total DESC) AS size_rank
FROM order_totals
ORDER BY ordered_on;

-- SQL Joins and Aggregation 1.0.0 (Zerethon Official Sample): schema and made-up data.
-- Runs before the entry file on every run, in a new in-memory SQLite database; then
-- data/*.csv is imported into the table of the same name.
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT NOT NULL
);

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    category TEXT NOT NULL,
    price REAL NOT NULL CHECK (price > 0)
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers (id),
    ordered_on TEXT NOT NULL -- ISO date
);

CREATE TABLE order_items (
    order_id INTEGER NOT NULL REFERENCES orders (id),
    product_id INTEGER NOT NULL REFERENCES products (id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, product_id)
);

INSERT INTO customers (name, city) VALUES
    ('An', 'Hanoi'), ('Binh', 'Da Nang'), ('Cuc', 'Hanoi'), ('Dung', 'Hue'), ('Em', 'Da Nang');

-- products: rows come from data/products.csv, imported after this file (id = row order).

INSERT INTO orders (customer_id, ordered_on) VALUES
    (1, '2026-09-01'), (1, '2026-09-15'), (2, '2026-09-03'), (3, '2026-09-20'), (2, '2026-10-02');

INSERT INTO order_items (order_id, product_id, quantity) VALUES
    (1, 1, 4), (1, 2, 10), (2, 3, 1), (3, 4, 2), (3, 5, 1),
    (4, 1, 2), (4, 5, 2), (5, 3, 1), (5, 2, 3);

-- Checks for the sample data and queries. To run them, set this file as the entry file
-- (the ▶ button next to it in Files) and press Run, or Run ▾ → Run tests. A failed check
-- stops the run: the CHECK constraint on `ok` rejects a 0.
CREATE TEMP TABLE checks (name TEXT NOT NULL, ok INTEGER NOT NULL CHECK (ok = 1));

INSERT INTO checks VALUES ('LEFT JOIN keeps all 5 customers',
    (SELECT count(*) = 5 FROM (SELECT c.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id)));
INSERT INTO checks VALUES ('Dung has no orders',
    (SELECT count(o.id) = 0 FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE c.name = 'Dung'));
INSERT INTO checks VALUES ('Paper revenue is 28',
    (SELECT sum(i.quantity * p.price) = 28 FROM order_items i JOIN products p ON p.id = i.product_id WHERE p.category = 'Paper'));
INSERT INTO checks VALUES ('HAVING drops Paper',
    (SELECT count(*) = 2 FROM (SELECT p.category FROM order_items i JOIN products p ON p.id = i.product_id
                               GROUP BY p.category HAVING sum(i.quantity * p.price) > 30)));
INSERT INTO checks VALUES ('running total ends at the overall revenue',
    (SELECT max(running) = (SELECT sum(i.quantity * p.price) FROM order_items i JOIN products p ON p.id = i.product_id)
     FROM (SELECT sum(t.total) OVER (ORDER BY t.ordered_on) AS running
           FROM (SELECT o.ordered_on, sum(i.quantity * p.price) AS total FROM orders o
                 JOIN order_items i ON i.order_id = o.id JOIN products p ON p.id = i.product_id GROUP BY o.id) t)));

SELECT 'All ' || count(*) || ' checks passed.' AS result FROM checks;