3. Advanced SQL

Intermediate · 13 min read

These features turn SQL from "fetch some rows" into a real analysis language — and they are exactly what you need for the LLM log analysis in the next topic.

3.1 Sample data

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT, plan TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount REAL, status TEXT, created_at TEXT);
CREATE TABLE tickets (id INTEGER PRIMARY KEY, customer_id INTEGER, category TEXT, created_at TEXT, meta TEXT);
INSERT INTO customers VALUES
    (1,'Priya','Bengaluru','pro'), (2,'Rahul','Pune','free'), (3,'Ananya','Pune','pro'),
    (4,'Arjun','Delhi','free'), (5,'Meera',NULL,'free');
INSERT INTO orders VALUES
    (101,1,2499,'delivered','2026-09-02'), (102,2,799,'delivered','2026-09-05'),
    (103,1,1299,'shipped','2026-09-20'),   (104,3,5600,'delivered','2026-09-21'),
    (105,4,450,'cancelled','2026-09-22'),  (106,3,999,'shipped','2026-10-01'),
    (107,1,349,'delivered','2026-10-02');
INSERT INTO tickets VALUES
    (1,1,'billing','2026-09-03','{"channel": "chat", "sentiment": -0.6, "tags": ["refund"]}'),
    (2,2,'shipping','2026-09-06','{"channel": "email", "sentiment": -0.2, "tags": ["delay"]}'),
    (3,1,'shipping','2026-09-21','{"channel": "chat", "sentiment": 0.1, "tags": []}'),
    (4,3,'bug','2026-09-25','{"channel": "chat", "sentiment": -0.8, "tags": ["crash", "urgent"]}'),
    (5,5,'billing','2026-09-30','{"channel": "phone", "sentiment": 0.3, "tags": ["invoice"]}');
""")

def show(sql, params=()):
    cur = con.execute(sql, params)
    cols = [d[0] for d in cur.description]
    rows = cur.fetchall()
    widths = [max(len(str(x)) for x in [c, *[r[i] for r in rows]]) for i, c in enumerate(cols)]
    print("  ".join(c.ljust(w) for c, w in zip(cols, widths)).rstrip())
    for r in rows:
        print("  ".join(str(x).ljust(w) for x, w in zip(r, widths)).rstrip())

3.2 CTEs — name each step with WITH

A common table expression is a named, temporary result you can use like a table. It turns a tangle of nested subqueries into readable steps — and fixes the double-counting trap from the previous topic:

show("""
WITH order_stats AS (
    SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS spent
    FROM orders WHERE status != 'cancelled'
    GROUP BY customer_id
),
ticket_stats AS (
    SELECT customer_id, COUNT(*) AS tickets
    FROM tickets
    GROUP BY customer_id
)
SELECT c.name,
       COALESCE(o.orders, 0)  AS orders,
       COALESCE(o.spent, 0)   AS spent,
       COALESCE(t.tickets, 0) AS tickets
FROM customers AS c
LEFT JOIN order_stats  AS o ON o.customer_id = c.id
LEFT JOIN ticket_stats AS t ON t.customer_id = c.id
ORDER BY spent DESC
""")

Output

name    orders  spent   tickets
Ananya  2       6599.0  1
Priya   3       4147.0  2
Rahul   1       799.0   1
Arjun   0       0       0
Meera   0       0       1

Priya now correctly shows 3 orders and 2 tickets. Write CTEs one step at a time and check each one — the same habit makes LLM-generated SQL far easier to review.

3.3 Window functions — calculations across related rows

An aggregate collapses rows into one; a window function computes across a group of rows but keeps every row. The OVER (...) clause defines the window:

show("""
SELECT customer_id, id, amount,
       SUM(amount)  OVER (PARTITION BY customer_id)                       AS customer_total,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at)   AS order_no,
       RANK()       OVER (ORDER BY amount DESC)                           AS value_rank
FROM orders
WHERE status != 'cancelled'
ORDER BY customer_id, order_no
""")

Output

customer_id  id   amount  customer_total  order_no  value_rank
1            101  2499.0  4147.0          1         2
1            103  1299.0  4147.0          2         3
1            107  349.0   4147.0          3         6
2            102  799.0   799.0           1         5
3            104  5600.0  6599.0          1         1
3            106  999.0   6599.0          2         4

• PARTITION BY — restart the calculation for each group (like GROUP BY, without collapsing).
• ORDER BY inside OVER — the order rows are processed in (needed for numbering, running totals, LAG).

Top-N per group

"Each customer's most recent order" — a question that is awkward without windows:

show("""
WITH ranked AS (
    SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
    FROM orders AS o
)
SELECT customer_id, id, amount, created_at FROM ranked WHERE rn = 1
""")

Output

customer_id  id   amount  created_at
1            107  349.0   2026-10-02
2            102  799.0   2026-09-05
3            106  999.0   2026-10-01
4            105  450.0   2026-09-22

Running totals and comparing with the previous row

show("""
SELECT created_at, amount,
       SUM(amount) OVER (ORDER BY created_at)                   AS running_revenue,
       amount - LAG(amount) OVER (ORDER BY created_at)          AS vs_previous
FROM orders
WHERE status != 'cancelled'
""")

Output

created_at  amount  running_revenue  vs_previous
2026-09-02  2499.0  2499.0           None
2026-09-05  799.0   3298.0           -1700.0
2026-09-20  1299.0  4597.0           500.0
2026-09-21  5600.0  10197.0          4301.0
2026-10-01  999.0   11196.0          -4601.0
2026-10-02  349.0   11545.0          -650.0

Function	Gives
ROW_NUMBER()	1, 2, 3 … (no ties)
RANK() / DENSE_RANK()	rank with gaps / without gaps after ties
LAG(x) / LEAD(x)	the value from the previous / next row
SUM/AVG/COUNT(...) OVER (...)	totals, running totals, moving averages
NTILE(4)	split into quartiles

3.4 CASE — if/else inside SQL

show("""
SELECT id, amount,
       CASE WHEN amount >= 2000 THEN 'large'
            WHEN amount >= 800  THEN 'medium'
            ELSE 'small' END AS size
FROM orders
""")
show("""
SELECT COUNT(*) AS orders,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)      AS cancelled,
       ROUND(AVG(CASE WHEN status = 'cancelled' THEN 1.0 ELSE 0 END), 2) AS cancel_rate
FROM orders
""")

Output

id   amount  size
101  2499.0  large
102  799.0   small
103  1299.0  medium
104  5600.0  large
105  450.0   small
106  999.0   medium
107  349.0   small
orders  cancelled  cancel_rate
7       1          0.14

AVG(CASE WHEN … THEN 1.0 ELSE 0 END) is the standard trick for a rate — error rate, pass rate, refusal rate.

3.5 Dates

Date functions differ between databases; here are SQLite's, with the PostgreSQL equivalent:

show("""
SELECT strftime('%Y-%m', created_at)  AS month,          -- PostgreSQL: to_char(created_at, 'YYYY-MM')
       COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY month
""")
show("""
SELECT id, created_at,
       CAST(julianday('2026-10-07') - julianday(created_at) AS INTEGER) AS days_ago   -- PostgreSQL: current_date - created_at
FROM orders
WHERE created_at >= date('2026-10-07', '-7 days')                                      -- PostgreSQL: current_date - interval '7 days'
""")

Output

month    orders  revenue
2026-09  5       10647.0
2026-10  2       1348.0
id   created_at  days_ago
106  2026-10-01  6
107  2026-10-02  5

In real code use date('now') (SQLite) or current_date (PostgreSQL) instead of a fixed date.

3.6 JSON columns

LLM apps produce semi-structured data — metadata, tool arguments, extracted fields. Storing it as JSON and querying inside it is common:

show("""
SELECT id, category,
       meta ->> '$.channel'   AS channel,          -- PostgreSQL (jsonb): meta ->> 'channel'
       meta ->> '$.sentiment' AS sentiment
FROM tickets
WHERE meta ->> '$.sentiment' < 0
ORDER BY sentiment
""")
show("""
SELECT j.value AS tag, COUNT(*) AS n                   -- one row per array element
FROM tickets, json_each(tickets.meta, '$.tags') AS j   -- PostgreSQL: jsonb_array_elements_text(meta->'tags')
GROUP BY tag ORDER BY n DESC, tag
""")

Output

id  category  channel  sentiment
4   bug       chat     -0.8
1   billing   chat     -0.6
2   shipping  email    -0.2
tag      n
crash    1
delay    1
invoice  1
refund   1
urgent   1

JSON or columns?

Put fields you filter, join or aggregate on often in real columns (they're typed and indexable). Keep JSON for flexible, rarely queried extras. PostgreSQL's jsonb can be indexed if you must query it heavily.

3.7 Indexes — make lookups fast

Without an index, finding one customer's orders means scanning every row. An index is a sorted lookup structure, like a book index:

print(con.execute("EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1").fetchall()[0][3])
con.execute("CREATE INDEX idx_orders_customer ON orders(customer_id)")
print(con.execute("EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1").fetchall()[0][3])

Output

SCAN orders
SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

SCAN = read every row; SEARCH … USING INDEX = jump straight to the matches. Index the columns you filter and join on. Indexes cost disk space and slow down writes slightly, so don't index everything.

3.8 SQL meets embeddings: pgvector

pgvector adds a vector column type and similarity search to PostgreSQL — so documents, metadata and embeddings live in one database, and you can combine SQL filters with semantic search in one query:

-- PostgreSQL with the pgvector extension (not SQLite)
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
    id         BIGSERIAL PRIMARY KEY,
    doc_id     TEXT,
    tenant_id  TEXT,
    content    TEXT,
    embedding  vector(1536)
);
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);

-- top 5 chunks for one tenant, by cosine distance (<=>) to the query embedding
SELECT doc_id, content, 1 - (embedding <=> $1) AS similarity
FROM chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 5;

The WHERE tenant_id = … filter is how multi-tenant RAG keeps each customer's documents separate. Vector databases are covered in depth in their own series.

Practice

• Using a CTE and ROW_NUMBER(), find each city's biggest order.
• Compute the share of tickets per channel as a percentage, using meta ->> '$.channel'.

Next: Querying LLM logs & evals — the same tools, applied to your LLM app.