The previous lesson closed module 10 with a sentence: "what's missing isn't more functions, but context". This module is that context, and it starts with the most concrete thing there is: the problems you'll be asked to solve. Because the toolbox is full now —JOINs, aggregates, subqueries, CTEs, window functions, JSON— but on a real project nobody asks you for "a LEFT JOIN": they ask you for "a product listing with filters the user picks", "the management dashboard", "the customers who've stopped buying" or "export this for the accountant".

The good news is that those requests repeat. The business changes, the table names change, and the pattern is the same. This lesson is the catalogue of those patterns: eleven use cases, each with its business framing, its solution on GreenStore and a note on which tool from the course solves it. There's no new syntax. There's pattern recognition, which is a different thing and a far more useful one.

1. Paginated listing with optional filters
2. Search: prefix, substring and full-text
3. A KPI report in a single row
4. Top N and ranking
5. Finding gaps: the anti-join
6. Time series with no gaps
7. Customer cohorts
8. Detecting and cleaning up duplicates
9. Auditing: who changed what and when
10. Exporting to another system
11. Loading from a file with a staging table
12. Summary table: use case → tool → lesson
13. Common Mistakes and Tips
14. Exercises
15. Conclusion

The request. "The catalogue screen has four filters —category, maximum price, text and in-stock only— and the user can fill in whichever they like. It also has pagination." It's the number-one use case in the world, and the one most often written badly. The problem isn't the WHERE: it's that you don't know what the WHERE will be until the user hits "search". There are two approaches, and it's worth knowing both.

A single query, with one parameter per filter that neutralizes itself when it arrives null:

With all four parameters at NULL/false (the catalogue's first page, unfiltered):

(First 5 of 19 rows: the active products; number 20, discontinued, doesn't appear.) With :category_id = 2 and :max_price = 15.00 the same query returns 3 rows —the body oil (€14.25), the solid shampoo (€8.40) and the lip balm (€4.60)— and with :in_stock_only = true the wax candles, the only ones with stock 0, would disappear. Its advantages are real: a single query to maintain, zero injection risk (11-03) and a cacheable plan. And so is its cost: (:p IS NULL OR col = :p) is non-sargable (08-04) —the engine doesn't know in advance whether the column will be filtered— so the plan has to serve all sixteen possible cases and isn't optimal for any of them. With 19 products it doesn't matter; with 19 million and a very selective filter, it's a Seq Scan where there was an Index Scan.

Mitigation in PostgreSQL: a prepared statement switches to a generic plan from the sixth execution on. With SET plan_cache_mode = force_custom_plan the engine replans with the actual values and discards the neutralized branches. It's the way out when the pattern works fine except for one specific filter.

The other approach is to compose the WHERE in code, adding only the conditions the user has filled in:

The red line is on that last line: you build the text of the conditions, never the values, which still travel as parameters. Concatenating f"AND p.price <= {max_price}" is exactly 11-03's vulnerability, and it doesn't stop being one just because the data "looks like" a number.

The course's criterion: start with the single-query pattern; move to composition only when you measure (08-05) that the generic plan is costing you. And for pagination, 08-04's rule: LIMIT/OFFSET for small numbered paginators, keyset for infinite scroll and APIs.

The request. "Make the shop's search box find the product even if the customer types half a word." There are three levels, in order of increasing cost, and the decision is choosing the cheapest one that solves the problem:

There's the whole problem in a nutshell: searching for "oil" by prefix finds neither the olive oil nor the body oil, because the word sits in the middle of the name. Searching by substring does find them, but a LIKE '%…%' can't use a B-tree index (08-04): the only way to speed it up is a GIN index with pg_trgm, from 08-03. Level 3 appears when the user types phrases, expects "infusions" to find "infusion", or wants results sorted by relevance: that's no longer LIKE, it's full-text search with to_tsvector/to_tsquery and a GIN index over the vector.

The criterion: don't set up full-text for 19 products, and don't solve a million-item search box with ILIKE '%…%'. And when you do use ILIKE, always escape % and _ in the user's input: if somebody searches for 100%, that % is a wildcard.

The request. "Management wants a strip of numbers at the top of the dashboard: this month's sales, this month's orders, orders awaiting shipment, total revenue and average order value." The temptation is to fire off five queries; the solution is one, with 04-04's FILTER clause, which applies a different condition to each aggregate:

One query, one row, six indicators, and they're all canonical figures from the course: the 20 orders, the €727.95 of product revenue, the €49.33 of February 2026 and the average order value of €36.40. The 3 "to ship" are orders 18 and 19 (paid) and 20 (pending).

The COUNT(DISTINCT o.id) is compulsory: the JOIN with order_lines multiplies each order by its lines, and a COUNT(*) would return 47. And FILTER is the SQL standard for this; the portable equivalent is 06-05's SUM(CASE WHEN … THEN … END), more verbose and with the trap that COUNT(CASE …) also counts NULLs if you don't write it carefully.

The request. "The five highest-billing products, with their position." It's 10-03's pattern in its simplest form, ROW_NUMBER computed in a CTE and filtered outside:

Remember 10-03's three decisions, because in production they matter: ROW_NUMBER to cut off at exactly N rows, RANK if everyone tied in the last position should come out, and an explicit tie-breaker in the window's ORDER BY —here product_id— so the result is reproducible. If you only need the top 5 and not the position column, an ORDER BY … LIMIT 5 over an index is cheaper (08-04's rule 4).

The request. "Which customers have never bought? Which products don't sell? How many orders come in without a sales rep?" Every business question starting with "which ones don't" is the same pattern: an anti-join, which in PostgreSQL you write with NOT EXISTS (07-03). GreenStore's three deliberate gaps, in one query:

The names behind the numbers: Núria, Hugo and Inés have never bought; the wax candles (stock 0), the deodorant and the spirulina capsules (discontinued) have never been sold; and the five employees with no orders are the whole of management, logistics, warehouse and analysis. One variant that gets asked for constantly: the inactive customer, who isn't the one who never bought but the one who stopped buying — an anti-join with a time window:

Two customers have gone more than six months without buying as of 1 March 2026. Notice the business difference: Núria, Hugo and Inés (who never bought) are an activation problem; Marta and Tiago are a retention problem. Same table, two different campaigns.

Careful with NOT IN. WHERE id NOT IN (SELECT employee_id FROM orders) returns zero rows here, because employee_id has nulls and NOT IN with a NULL in the list is never true (04-03, 07-03). It's the classic mistake in this family of queries: use NOT EXISTS.

The request. "The monthly sales chart skips the months with no orders and the axis comes out crooked." A GROUP BY only produces rows for the data that exists. If a month had no orders, that month doesn't appear — and a chart that joins the previous point straight to the next one lies. The solution is to generate the calendar and join the data against it with generate_series (06-03) and a LEFT JOIN:

(6 of 14 rows; between April 2025 and January 2026 the series is the course's canonical one: 58.85 · 95.48 · 44.60 · 48.27 · 32.76 · 97.20 · 31.70 · 64.58.) January and February 2025 show up with €0.00 even though not a single order exists: the shop already had a catalogue and customers, but its first order is dated 4 March. Without the calendar, the series would start in March and nobody would see the two months with no sales.

The two compulsory pieces are the LEFT JOIN in that direction (calendar on the left, data on the right) and the COALESCE, because the LEFT JOIN produces NULL, not zero — and a NULL in a chart is a hole, not a low value.

The request. "Do the customers we acquire in spring buy more than the autumn ones?" A cohort is a group of customers sharing the moment they came in; the analysis consists of following each group over time. The minimal form —how many from each signup month went on to buy— is an aggregated LEFT JOIN:

Eight cohorts, 15 customers, 20 orders and €727.95: the columns tally with the course's total, which is the first check you should always run. And the reading is the expected one: the older cohorts have more orders per customer (the January-to-March 2025 ones run between 2 and 2.5 orders per person) and the recent ones haven't bought yet — simply because they've had less time. That bias is why cohorts are always compared at the same age: "orders within 90 days of signing up", not "total orders". 11-04 picks this up again with the honest warning about sample size.

The request. "Marketing says there are duplicate customers." In GreenStore there aren't any, and the reason is in 01-06's CREATE TABLE: customers.email is UNIQUE. That's the first lesson of this use case — a duplicate is prevented with a constraint, not cleaned up with a query:

Duplicates show up when there's no constraint: in an import table, in an unvalidated form, or when the natural key is "the same customer" and not "the same email" —Lucía signed up twice with two different addresses. For that, two techniques: the normalized key, GROUP BY lower(unaccent(trim(name || ' ' || last_name))) with its HAVING COUNT(*) > 1, and similarity instead of equality, with pg_trgm's similarity() (08-03) over a self-join a.id < b.id and a starting threshold of 0.6. Both return 0 rows over the course's 15 customers, which is what should happen in a healthy table.

For the cleanup, the canonical pattern is ROW_NUMBER (10-03): number the rows within each duplicate group by some "which one is the good one" criterion —the oldest, the one with orders— and delete the rest. Run the SELECT first, review the rows and only then turn it into a DELETE (05-04):

Before deleting, you have to repoint the references: if the duplicate customer has orders, you have to move them to the survivor with an UPDATE, or the ON DELETE RESTRICT FK will stop you — and thank goodness.

The request. "The oil's price changed on Tuesday and nobody knows who touched it." A database, by default, doesn't remember: an UPDATE replaces the previous value and leaves no trace. Recording changes is an explicit design decision, and there are three ways:

10-05's is the first one, with price_audit capturing the old value, the new one, current_user and now(). Two warnings you only learn in production. current_user is the database user, not the application's: if the website connects with a single gs_app role (11-03), every row will say gs_app; to know which person it was you have to propagate the user with SET LOCAL app.username = '...' when opening the transaction and read it with current_setting('app.username', true). And an audit table grows without end: plan its purge or its archiving from day one.

The request. "Send me the year's sales in a CSV for the accountant."

The 16 orders from 2025. And now the traps, all of them from the real world and none of them about SQL:

• Decimal and field separators. PostgreSQL writes 109.53 with a point; a Spanish-locale Excel expects a comma and will read it as a hundred and nine thousand five hundred and fifty-three. You fix it at the destination, or at source with replace(amount::text, '.', ',') — and then the DELIMITER can't be a comma. Hence the ';', which is what Excel expects in continental Europe.
• Encoding. UTF8 is the right choice, but Excel for Windows may need the BOM or even WIN1252 so it doesn't mangle the accents in "Castellón". Test it with a row that has accents, never with clean data.
• Date format and nulls. 2025-03-04 is ISO 8601 and it's the right choice between systems; if the destination demands 04/03/2025, convert it with to_char(date, 'DD/MM/YYYY') and don't rely on DateStyle. A NULL comes out as an empty field: if the destination doesn't distinguish empty from null, use NULL 'NULL_' or COALESCE.
• Personal data. A CSV with names and email addresses is no longer protected by the database's permissions. Export the bare minimum and read 11-03 before sending anything with customer data in it.

COPY ... TO (without the backslash) runs on the server and requires the pg_write_server_files role; \copy is psql's, the one you'll use almost every time. And for consumption by another program, returning JSON from the database (10-06) is usually better than a CSV.

The request. "They've sent us a CSV with 600 customers from a trade fair. Load it." Never load straight into the good table: load into a staging table with no constraints, validate there and move only what passes.

Over a six-row test file —two of them the same Lucía written two different ways, a customer who already exists, an email with no @, a country not in the catalogue and one good signup—:

Out of six rows, one gets loaded. That report is the deliverable: whoever sent you the file needs to know what didn't get in and why, and a load that fails with ERROR: duplicate key value on row 400 tells nobody anything. The final insertion is an INSERT ... SELECT with WHERE rejection_reason IS NULL, or an INSERT ... ON CONFLICT DO NOTHING from 05-05 if you'd rather have the UNIQUE act as a last safety net — all inside a transaction (09-03) so the load is all or nothing.

• Using NOT IN with a subquery that can return NULL. It returns zero rows and looks like "there are no gaps": use NOT EXISTS. And forgetting COUNT(DISTINCT …) in a report that JOINs to the lines: the JOIN multiplies, 20 orders become 47 rows and the average order value ends up divided by 2.35.
• Plotting a time series with no calendar. Months with no data aren't skipped: they're worth zero, and a chart that leaves them out draws a trend that doesn't exist. And comparing cohorts of different ages: January's has been buying for a year and December's for a month.
• Loading a CSV straight into the production table. One bad row aborts the whole load or, worse, gets halfway in. Staging, validation and a rejection report. And concatenating user values when building a dynamic WHERE: compose conditions if you must, the values are always parameters (11-03).
• Tip: check every report's totals against a figure you already know. The eight cohorts in this text add up to €727.95 because they have to; if they don't tally, the bug is in the JOIN, not in the data. And keep every report query in the repository with a comment saying which question it answers. That's the seed of 11-04's semantic layer. And when a request sounds new, look it up in section 12's table: it's nearly always one of these twelve under another name.

The order administration screen needs a listing with three optional filters —status, customer's country and date range— sorted by date descending and paginated 10 at a time. (1) Write it with the single-query pattern. (2) How many rows does it return with no filters and how many with status = 'delivered'? (3) Rewrite the pagination in keyset mode and explain which column you need for it to be stable.

Management wants a catalogue alert: the active products that have never been sold or that have no reviews, with a column saying which of the two problems they have (or both). (1) Write it. (2) How many rows come out? (3) Why does the result change if you use an INNER JOIN instead of NOT EXISTS?

You're given a supplier signup file with these columns: name, country, email. (1) Design the staging table and say why it must have no constraints. (2) Write the four validations you'd apply before inserting into suppliers. (3) What would you do with a row whose name already exists but with a different email?

Solution 1

2. With no filters, 10 rows (the first page of the 20 orders); with status = 'delivered', also 10, because there are 14 delivered. The total without the LIMIT would be 20 and 14. Notice < :to and not <=: 08-04's range pattern. 3. Keyset: WHERE (o.order_date, o.id) < (:last_date, :last_id) ORDER BY o.order_date DESC, o.id DESC LIMIT 10. You need o.id as a tie-breaker, because order_date isn't unique: without it, two orders from the same day could repeat or get lost between pages. That's why a keyset's ORDER BY must always end in a unique column.

Solution 2

2. Ten rows. The active products with no review number 10 (the 11 with no review minus the spirulina capsules, which are discontinued and fall out via WHERE p.active), and the two active never-sold ones —the wax candles (id 13) and the deodorant (id 19)— are among those ten, because they have no review either. That's why the answer isn't 12: it's the union of the two sets, not their sum. 3. An INNER JOIN with order_lines or with reviews answers the opposite question: it returns the products that do have sales or reviews. And a LEFT JOIN … WHERE ol.id IS NULL would work, but it produces intermediate rows you then have to discard and forces a DISTINCT; NOT EXISTS expresses the question directly and the engine resolves it as an anti-join (07-03, 07-05).

Solution 3

1. CREATE TEMP TABLE stg_suppliers (name TEXT, country TEXT, email TEXT); — all text and no constraints, no NOT NULL, no UNIQUE, no CHECK. The reason: if the staging table rejects rows, the load fails and you lose exactly the information you need (which rows came in wrong and why). Staging accepts the junk so it can be inventoried; the good table is the one that rejects it. 2. (a) Required fields: name and country not null and not empty after a trim. (b) Format: email LIKE '%_@_%.%' or NULL —in suppliers the email is optional. (c) Catalogue: country IN ('Spain','Portugal','France','Germany'), or better against a countries table. (d) Duplicates: within the file with COUNT(*) OVER (PARTITION BY lower(trim(name))), and against the real table with EXISTS. 3. It's a business decision, not a technical one: it could be the same supplier who changed address (→ 05-05's upsert) or two different companies with similar names (→ INSERT). What the program must not do is choose silently: flag the row as "manual review" and let a person decide. And if the name really must be unique, that rule goes in a UNIQUE on suppliers, not in the loading script.

This was the catalogue of requests, and now you have it in full:

• The listing with optional filters is solved with (:param IS NULL OR column = :param) at the cost of a generic plan, or by building the SQL in the application composing conditions, never values. Search has three levels: prefix with a B-tree, substring with pg_trgm and GIN, and full-text when there are phrases and relevance — searching for "oil" by prefix finds neither the olive oil nor the body oil.
• The dashboard fits in one row and one query with FILTER: 20 orders, €727.95, €36.40 average order value and 3 orders to ship; top N is ROW_NUMBER in a CTE filtered outside. Gaps are always a NOT EXISTS —3 customers with no purchase, 3 products never sold, 11 with no review, 10 web orders— and never a NOT IN if nulls are possible. Time series are generated with generate_series and joined with LEFT JOIN + COALESCE, so January 2025 shows up with €0.00 instead of vanishing. Cohorts group by signup month and are only compared at the same age.
• Duplicates are prevented with UNIQUE and detected with GROUP BY … HAVING; auditing lives in a trigger, with the warning that current_user isn't your application's user. Exporting fails on the decimal separator, the encoding and the date format, not on the SQL; and loading always goes through a staging table that accepts the junk so it can be inventoried: of six example rows, only one was loadable.

All these queries work. The question that comes next is a different one: will somebody else be able to maintain them a year from now? In the next lesson, Best practices, we deal with the craft: naming tables, columns, keys and indexes, and why what matters is being consistent; the formatting that makes a forty-line query readable; the design decisions —normalizing, restrictive types, NOT NULL by default, named constraints— that separate a database you can work with from one you're scared to touch; the process, with migrations, code reviews and tested backups; and the catalogue of antipatterns, from SELECT * in production to "I'll just fix it directly in production".