Five SQL Patterns That Replace a Python Loop — And Why They Win

The most expensive Python I review is not the untyped FastAPI handler. It is the for order in orders that exists because nobody wrote a window function. The loop looks honest. It is also how a report that took four seconds becomes forty, then a timeout on Monday after a good weekend of sales.

I already wrote how I pick a database type. This is the next layer: the SQL I actually write when the product question is “last order per customer,” “revenue this month versus last,” or “insert this booking unless that slot is taken.” Postgres can answer those in one round trip. Python can too — after it downloaded the table. The companion piece is how that query should leave Python without pulling the heap back in.

1. The loop that downloads the table

A typical script: SELECT * FROM orders, then group in a dict, then pick the latest created_at per customer_id. On 800 rows it feels fine. On 800,000 it is a support ticket. The database already has the index, the sort, and the memory for this. You paid for that. Using Python as a second query engine is how the cost shows up in latency, not on an invoice you planned.

• If the result is smaller than the source and the rule has a name — last, sum, rank, exists — it is a query.
• If the rule is a business essay — refunds with a human exception, a policy paragraph — it may stay in Python.

2. Last row per group — DISTINCT ON and ROW_NUMBER

Current subscription per workspace. Latest invoice status per order. The driver who last pinged. This is the first loop I delete. In Postgres I write DISTINCT ON (workspace_id) and ORDER BY workspace_id, created_at DESC. The portable cousin is ROW_NUMBER() OVER (PARTITION BY workspace_id ORDER BY created_at DESC), then keep rn = 1.

DISTINCT ON is shorter and I use it when I own Postgres. ROW_NUMBER is what I write when the same query must survive a move, or when I need rank 1 and rank 2 — the previous plan, the runner-up. Do not fetch every subscription and reduce in Python. You will get ties wrong, and you will fetch columns you never render.

3. Running totals and “since last month” — SUM OVER and LAG

Month-over-month is not a join of a table to itself inside a loop. LAG(revenue) OVER (PARTITION BY workspace_id ORDER BY month) puts last month on the same row. SUM(amount) OVER (PARTITION BY customer_id ORDER BY created_at) is a running balance. Window functions keep the row grain and still see the neighborhood. GROUP BY collapses. A Python loop rebuilds that neighborhood in a dict and forgets timezones.

If you need both the detail row and the group total, SUM(amount) OVER (PARTITION BY order_id) beats a second query and beats N+1 from the ORM. That is the whole trick: keep the grain you will render, compute the neighborhood in the engine.

4. Conditional counts — FILTER instead of if

COUNT(*) FILTER (WHERE status = paid) next to COUNT(*) FILTER (WHERE status = refunded) is one scan. The Python version is one pass with two counters — until someone adds a third status in a second loop, or the export grows and you scan twice. FILTER is readable. A CASE inside SUM works on engines that never got FILTER. I prefer FILTER in Postgres because the intent sits on the page: this count has a predicate.

5. Upsert instead of select-then-insert

The race: read “is this email free,” then insert. Two tabs, two workers, one unique constraint you discover in production. INSERT ... ON CONFLICT (email) DO UPDATE — or DO NOTHING — is the transaction the database already knows how to run. Same family: UPDATE ... WHERE version = $version for an optimistic lock. If you check-then-write in Python, you are writing a worse database.

• Booking slots, idempotency keys, “create the customer if missing” — these are upserts, not if-blocks.
• A unique index is part of the product. Without it, ON CONFLICT is theatre.

6. How I check I did not invent a slower query

EXPLAIN (ANALYZE, BUFFERS) on the slow path. If you see a sequential scan on a table that grew up, you needed an index that matches the partition and the order — (workspace_id, created_at DESC) for DISTINCT ON. A window function is not free. It is still cheaper than shipping the heap to the app. If the plan sorts two million rows for a dashboard that shows twenty, add a date bound. SQL that replaces a loop can still be a bad loop inside the engine.

Conclusion: name the result set, write the query

Python is a good place for HTTP, policy, and the sentence you show the user. It is a poor place to rediscover GROUP BY. Learn five shapes — last-per-group, lag, running sum, filtered aggregates, upsert — and most of the “clever” scripts I review become a view or a single query in a repository function. The other article is the Python that refuses to undo that work.