Subqueries and CTEs

A SaaS dashboard asks two kinds of questions your query tools can’t yet answer. “Which organizations have at least one overdue invoice?” wants a yes/no per org, not a count and not a list of invoices. “What are each organization’s top three tags by usage?” keeps three rows per org, exactly what groupBy collapses away.

Both depend on a set of rows computed first. Every query in the last six lessons made a single pass: select, filter, group, return. These need two: compute an intermediate result, then ask the real question against it.

The pieces are familiar, db.select from CRUD basics, the joins from Joining tables, the aggregates from Aggregations & grouping. The new move is composing one query out of another, and the only real decision is where the intermediate result lives: in an inline subquery, in a named CTE, or in app code as two separate queries.

Where the intermediate result lives

Say you want every invoice belonging to an organization on a paid plan. One filter can’t do it: the thing you’re filtering by, whether the org is on a paid plan, isn’t a column on invoices. It lives on organizations. So the work splits in two: first compute the set of paid-plan org ids, then return the invoices whose organizationId is in that set.

That set of paid-plan org ids is the intermediate result . It’s required; the only open question is where you put it. The same set can live in three homes and return identical rows from all three, as the figure shows.

App code

query 1 select id from organizations …

paid-plan org ids

org_3org_7org_9

query 2 select * from invoices …

Two round-trips. Reads straight.

Inline subquery

one statement select * from invoices where organization_id in (

paid-plan org ids

org_3org_7org_9

)

One statement. Used once.

CTE

with paid_orgs as (

paid-plan org ids

org_3org_7org_9

)

select * from invoices join paid_orgs …

Named. Reusable.

One intermediate result, paid-plan org ids, in three homes. All three return the same rows.

Each panel has a name the rest of the lesson uses precisely. A subquery is a SELECT written inside another statement (panel two). A common table expression (CTE) is that subquery named with WITH and referenced like a table (panel three). A subquery in the FROM clause that the main query selects from is a derived table .

For a result used once, an inline subquery and a CTE compile to the same plan: Postgres folds a single-use CTE back into the main query. So this isn’t a performance choice; the database does the same work either way, and only the reading changes.

That makes it a readability call, ranked by how often you’ll reach for each. Used once and clearer inline, inline it. Used more than once, or when naming untangles an inside-out query, name it with a CTE. Small enough that two short reads beat one composed one, write two queries in app code. Speed matters in only two narrow spots, flagged when we reach them.

Note

This lesson is about writing layered queries that are correct and readable, not fast. Whether a query is fast, whether a per-row subquery hurts, whether a column wants an index, is a measurement question for the performance chapter ahead. When a shape has a known cost trap, this lesson names it.

Inline subqueries in where

The simplest shape drops a subquery straight into a where, used once. In the first lesson you passed inArray a list of literal values; here you pass it a query instead. To get invoices belonging to orgs created this year, feed inArray the ids those orgs return.

const recentOrgInvoices = await db
  .select()
  .from(invoices)
  .where(
    inArray(
      invoices.organizationId,
      db
        .select({ id: organizations.id })
        .from(organizations)
        .where(gte(organizations.createdAt, startOfYear)),
    ),
  );

const recentOrgInvoices = await db
  .select()
  .from(invoices)
  .where(
    inArray(
      invoices.organizationId,
      db
        .select({ id: organizations.id })
        .from(organizations)
        .where(gte(organizations.createdAt, startOfYear)),
    ),
  );

The outer query is an ordinary read: every invoice, filtered by a where. Only what goes inside the where is new.

const recentOrgInvoices = await db
  .select()
  .from(invoices)
  .where(
    inArray(
      invoices.organizationId,
      db
        .select({ id: organizations.id })
        .from(organizations)
        .where(gte(organizations.createdAt, startOfYear)),
    ),
  );

Run alone, this query returns a column of org ids. Handed to the outer query as a value, it becomes a subquery.

const recentOrgInvoices = await db
  .select()
  .from(invoices)
  .where(
    inArray(
      invoices.organizationId,
      db
        .select({ id: organizations.id })
        .from(organizations)
        .where(gte(organizations.createdAt, startOfYear)),
    ),
  );

inArray reads as a sentence: keep invoices whose organizationId is in that set. You read one expression; Postgres plans one statement.

const recentOrgInvoices = await db
  .select()
  .from(invoices)
  .where(
    inArray(
      invoices.organizationId,
      db
        .select({ id: organizations.id })
        .from(organizations)
        .where(gte(organizations.createdAt, startOfYear)),
    ),
  );

The projection is the contract: inArray compares against one column, so the subquery must select exactly one. Select two and the types stop lining up.

← Previous 1 / 1 Next →

The second shape returns a single value rather than a set. A subquery selecting one column and returning one row is a scalar, a plain value you can compare against, as in “invoices above this org’s average amount”:

const aboveAverage = await db
  .select()
  .from(invoices)
  .where(
    and(
      eq(invoices.organizationId, orgId),
      gt(
        invoices.amountDue,
        db
          .select({ avg: avg(invoices.amountDue) })
          .from(invoices)
          .where(eq(invoices.organizationId, orgId)),
      ),
    ),
  );

The inner query collapses to one number, the org’s average, and gt compares each invoice’s amountDue against it.

Nesting a select opens no security hole: every value inside it, startOfYear and orgId, is still a placeholder bound by the driver.

Reach for an inline subquery when it’s used once and the query still reads cleanly with the set written in place; the moment you read the subquery twice, give it a name, as a later section does.

One cost to watch: inArray over thousands of ids is slower than the equivalent join, since Postgres must materialize the list and check membership where a join would stream the match. The performance chapter pins down where that line sits.

Asking whether a related row exists

The intro’s first problem was “organizations that have at least one overdue invoice.” With only earlier lessons’ tools, you’d reach for a join plus a group plus a having:

await db
  .select({ id: organizations.id, name: organizations.name })
  .from(organizations)
  .innerJoin(invoices, eq(invoices.organizationId, organizations.id))
  .where(and(lt(invoices.dueDate, today), ne(invoices.status, 'paid')))
  .groupBy(organizations.id, organizations.name)
  .having(gt(count(invoices.id), 0));

That query is correct, but it pairs every org with each matching invoice and collapses the pairs with a group just to answer a yes/no. Postgres has a shape built for the existence question: exists. In Drizzle, exists and notExists import from drizzle-orm and wrap a subquery:

const overdueOrgs = await db
  .select({ id: organizations.id, name: organizations.name })
  .from(organizations)
  .where(
    exists(
      db
        .select()
        .from(invoices)
        .where(
          and(
            eq(invoices.organizationId, organizations.id),
            lt(invoices.dueDate, today),
            ne(invoices.status, 'paid'),
          ),
        ),
    ),
  );

The key is eq(invoices.organizationId, organizations.id) inside the subquery: it references organizations.id, a column from the outer query. A subquery that references an outer column like this is a correlated subquery . For each org, Postgres runs the subquery and stops at the first overdue invoice, returning true without counting or collecting. That short-circuit is why exists is the fastest way to ask an existence question.

notExists answers the opposite. “Organizations with no invoices at all” is the same correlated subquery, negated:

const emptyOrgs = await db
  .select({ id: organizations.id, name: organizations.name })
  .from(organizations)
  .where(
    notExists(
      db
        .select()
        .from(invoices)
        .where(eq(invoices.organizationId, organizations.id)),
    ),
  );

Here are the two shapes side by side.

Join + group + having

await db
  .select({ id: organizations.id, name: organizations.name })
  .from(organizations)
  .innerJoin(invoices, eq(invoices.organizationId, organizations.id))
  .where(and(lt(invoices.dueDate, today), ne(invoices.status, 'paid')))
  .groupBy(organizations.id, organizations.name)
  .having(gt(count(invoices.id), 0));

Correct, but builds every matched org-invoice pair and groups them to answer a yes/no. Two hundred overdue invoices on one org means two hundred pairs assembled, grouped, and counted, just to learn the count cleared zero.

exists

await db
  .select({ id: organizations.id, name: organizations.name })
  .from(organizations)
  .where(
    exists(
      db
        .select()
        .from(invoices)
        .where(
          and(
            eq(invoices.organizationId, organizations.id),
            lt(invoices.dueDate, today),
            ne(invoices.status, 'paid'),
          ),
        ),
    ),
  );

Asks the question directly and stops at the first matching invoice per org. Nothing is paired or collapsed; the correlated subquery short-circuits on the first hit. This is the default for an existence yes/no.

exists is excellent on a small or medium outer set but can get expensive when the outer set is very large, since the subquery conceptually re-runs per outer row; finding exactly where that hurts is an EXPLAIN exercise for the performance chapter. So exists and notExists are the default for an existence yes/no; reach for the join-plus-group only when you also need the matched rows or a real count.

You’ve seen this idea before. In Nested reads (RQB), the relational query builder filtered parents by a predicate on their children; db.select(...).where(exists(...)) is the SQL-builder form, for when the predicate spans several tables or sits alongside other raw where logic.

Subqueries in from: the derived table

So far the intermediate has been a set of ids (inArray), a single value (a scalar comparison), or a yes/no (exists). Sometimes it’s a set of rows, usually an aggregate, that the main query must join against.

In the aggregations lesson you computed per-org invoice totals. Now suppose you want each org’s name beside its total, showing only orgs that owe more than some threshold. A plain where can’t filter on sum(...): the aggregate doesn’t exist until rows collapse, and where runs before the collapse. So you compute the aggregate first as its own little table, then select from it and join against it. That table is a derived table: a subquery in the FROM clause, named with .as(...).

const orgTotals = db
  .select({
    orgId: invoices.organizationId,
    total: sum(invoices.amountDue).as('total'),
  })
  .from(invoices)
  .groupBy(invoices.organizationId)
  .as('org_totals');

const bigDebtors = await db
  .select({ name: organizations.name, total: orgTotals.total })
  .from(orgTotals)
  .innerJoin(organizations, eq(orgTotals.orgId, organizations.id))
  .orderBy(desc(orgTotals.total));

orgTotals is an ordinary db.select, and the trailing .as('org_totals') turns it into a named table you can put in from. You then reference its columns through the alias (orgTotals.total, orgTotals.orgId) and otherwise treat it like any table: join it, order by its columns, select from it.

One money detail: sum(invoices.amountDue) returns a string, because numeric arrives in TypeScript as a string to keep every cent exact. Format total for display, but never parseFloat it for math.

Reach for a derived table when an aggregated or windowed sub-result must take part in a join or an outer filter, the one thing a where subquery can’t express. Used once, a derived table and a CTE produce the same plan; promote it to a named CTE once the same set is needed twice or the from clause starts to read inside-out.

Naming the result with a CTE

You’ve named the intermediate inside where, from, exists, and as a scalar. A CTE names it up front, so the query reads top-to-bottom (name it, then use it) instead of inside-out.

The Drizzle shape has two halves: $with(name).as(query) defines the CTE, and db.with(cte) makes it available to the statement that follows. You then reference it by its variable, like a table:

const recentInvoices = db.$with('recent_invoices').as(
  db
    .select({
      id: invoices.id,
      organizationId: invoices.organizationId,
      amountDue: invoices.amountDue,
    })
    .from(invoices)
    .where(gte(invoices.createdAt, startOfMonth)),
);

const result = await db
  .with(recentInvoices)
  .select({ name: organizations.name, amountDue: recentInvoices.amountDue })
  .from(recentInvoices)
  .innerJoin(organizations, eq(recentInvoices.organizationId, organizations.id));

The WITH clause defines recent_invoices; the main select then uses it as if it were a table, joining it to organizations.

A query can name as many intermediates as it needs. Multiple CTEs chain through one db.with(...), and each can reference the ones named before it:

db.with(cteA, cteB).select(/* ... */).from(cteB);

Here cteB can be defined in terms of cteA.

Reach for a CTE when the sub-result is used more than once, or when naming turns a nested, inside-out query into a top-to-bottom read. Otherwise the name is noise: a single-use CTE named for its own sake reads worse than the inline subquery, adding a name and a forward reference for no gain.

A sub-result used twice shows the rule clearest:

Inline subquery

// orgs whose outstanding total is above the average org total
const perOrgTotal = db
  .select({ orgId: invoices.organizationId, total: sum(invoices.amountDue).as('total') })
  .from(invoices)
  .groupBy(invoices.organizationId)
  .as('per_org_total');

const aboveAverage = await db
  .select({ orgId: perOrgTotal.orgId, total: perOrgTotal.total })
  .from(perOrgTotal)
  .where(
    gt(
      perOrgTotal.total,
      db
        .select({ avg: avg(sql<string>`t.total`) })
        .from(
          db
            .select({ total: sum(invoices.amountDue).as('total') })
            .from(invoices)
            .groupBy(invoices.organizationId)
            .as('t'),
        ),
    ),
  );

The per-org total is written out twice. You now have to keep the two copies in sync forever: the day one drifts, the query is silently wrong.

CTE

// orgs whose outstanding total is above the average org total
const orgTotals = db.$with('org_totals').as(
  db
    .select({ orgId: invoices.organizationId, total: sum(invoices.amountDue).as('total') })
    .from(invoices)
    .groupBy(invoices.organizationId),
);

const aboveAverage = await db
  .with(orgTotals)
  .select({ orgId: orgTotals.orgId, total: orgTotals.total })
  .from(orgTotals)
  .where(
    gt(
      orgTotals.total,
      db.select({ avg: avg(orgTotals.total) }).from(orgTotals),
    ),
  );

Named once, the duplication is gone: define org_totals, then reference it in from and again inside the average. Nothing to keep in sync, and Postgres computes the grouped result once instead of twice.

Postgres inlines a CTE that is non-recursive, side-effect-free, and used exactly once, so a single-use CTE costs the same as writing it inline. A CTE used more than once is computed once and reused, which is why naming a reused sub-result is a real win, not just cosmetic.

Watch video

Maven Analytics’ Alice Zhao (SQL Pocket Guide author) builds the same CTE-vs-subquery decision in 13 minutes, including multiple chained CTEs and a quick look at WITH RECURSIVE.

You may also see WITH ... AS MATERIALIZED, which forces Postgres to materialize a single-use CTE; reach for it only when a measurement shows the planner’s default is wrong.

Window functions: ranking within groups

The intro’s second problem, “top three tags per organization,” needs one SQL feature with no Drizzle builder, so you write it inside a sql template.

groupBy collapses: feed it three invoices and a status and it returns one row per group. That answers “how many invoices per status” but not “the top three tags,” because the collapse destroys exactly the rows you wanted to keep.

A window function annotates each row instead, computing a value while still seeing the rows around it. The one this lesson needs is row_number(), which numbers rows:

sql<number>`row_number() over (partition by ${tagCounts.orgId} order by ${tagCounts.count} desc)`;

Two clauses inside over (...) do the work:

• partition by ${tagCounts.orgId} restarts the numbering for each org: org 3’s tags get 1, 2, 3, then the counter resets for org 7.
• order by ${tagCounts.count} desc decides what “first” means: the highest count gets number 1.

Together this reads as “within each org, number the tags from most-used to least-used.” Keeping rows 1 through 3 gives the top three, which the capstone builds next.

Parameterization still holds inside a sql template, and sql<number> is a claim you make to TypeScript, not a check Drizzle can verify. row_number() belongs to a family (rank(), dense_rank(), lag(), lead(), sum() over (...)); this lesson teaches the shape so you recognize the rest elsewhere.

Watch video

Alice Zhao breaks over (partition by ... order by ...) into its two pieces in 8 minutes, then ends on the exact “top three per group” filter this lesson’s capstone builds.

Top three tags per organization

This report puts all three placements to work together: a CTE feeds a window function, which feeds an outer filter. Build it in stages so you can watch each piece slot into the next.

Stage one, tag_counts. Count how many invoices carry each tag, per org.

const tagCounts = db.$with('tag_counts').as(
  db
    .select({
      orgId: invoices.organizationId,
      tagId: tags.id,
      tagName: tags.name,
      count: count(invoices.id).as('count'),
    })
    .from(invoices)
    .innerJoin(invoiceTags, eq(invoiceTags.invoiceId, invoices.id))
    .innerJoin(tags, eq(tags.id, invoiceTags.tagId))
    .groupBy(invoices.organizationId, tags.id, tags.name),
);

Stage two, ranked. Select from tag_counts and add a rank column with row_number().

const ranked = db.$with('ranked').as(
  db
    .select({
      orgId: tagCounts.orgId,
      tagName: tagCounts.tagName,
      count: tagCounts.count,
      rank: sql<number>`row_number() over (
        partition by ${tagCounts.orgId}
        order by ${tagCounts.count} desc, ${tagCounts.tagId}
      )`.as('rank'),
    })
    .from(tagCounts),
);

Final select, keep the top three. Wire both CTEs in with db.with(...), select from ranked, and filter to ranks one through three.

const topTags = await db
  .with(tagCounts, ranked)
  .select({
    orgId: ranked.orgId,
    tagName: ranked.tagName,
    count: ranked.count,
    rank: ranked.rank,
  })
  .from(ranked)
  .where(lte(ranked.rank, 3))
  .orderBy(ranked.orgId, ranked.rank);

Why two layers? SQL evaluates window functions after where, so when where runs the rank doesn’t exist yet and can’t be referenced. The fix is structural: compute the rank in one layer, then filter it in the next. That’s why CTEs and window functions travel together; whenever you filter on a ranking, you’ll reach for both.

const tagCounts = db.$with('tag_counts').as(
  db
    .select({
      orgId: invoices.organizationId,
      tagId: tags.id,
      tagName: tags.name,
      count: count(invoices.id).as('count'),
    })
    .from(invoices)
    .innerJoin(invoiceTags, eq(invoiceTags.invoiceId, invoices.id))
    .innerJoin(tags, eq(tags.id, invoiceTags.tagId))
    .groupBy(invoices.organizationId, tags.id, tags.name),
);

const ranked = db.$with('ranked').as(
  db
    .select({
      orgId: tagCounts.orgId,
      tagName: tagCounts.tagName,
      count: tagCounts.count,
      rank: sql<number>`row_number() over (
        partition by ${tagCounts.orgId}
        order by ${tagCounts.count} desc, ${tagCounts.tagId}
      )`.as('rank'),
    })
    .from(tagCounts),
);

Stage one is an aggregate you already know: two joins walk invoices → invoice_tags → tags, then groupBy collapses to one row per (org, tag) with its count. Named tag_counts for the next stage to build on.

const tagCounts = db.$with('tag_counts').as(
  db
    .select({
      orgId: invoices.organizationId,
      tagId: tags.id,
      tagName: tags.name,
      count: count(invoices.id).as('count'),
    })
    .from(invoices)
    .innerJoin(invoiceTags, eq(invoiceTags.invoiceId, invoices.id))
    .innerJoin(tags, eq(tags.id, invoiceTags.tagId))
    .groupBy(invoices.organizationId, tags.id, tags.name),
);

const ranked = db.$with('ranked').as(
  db
    .select({
      orgId: tagCounts.orgId,
      tagName: tagCounts.tagName,
      count: tagCounts.count,
      rank: sql<number>`row_number() over (
        partition by ${tagCounts.orgId}
        order by ${tagCounts.count} desc, ${tagCounts.tagId}
      )`.as('rank'),
    })
    .from(tagCounts),
);

Stage two adds the rank. partition by org restarts the numbering per org; order by count desc makes the most-used tag number 1. This annotates each row rather than collapsing anything: every (org, tag) row from stage one survives, now carrying a rank.

const tagCounts = db.$with('tag_counts').as(
  db
    .select({
      orgId: invoices.organizationId,
      tagId: tags.id,
      tagName: tags.name,
      count: count(invoices.id).as('count'),
    })
    .from(invoices)
    .innerJoin(invoiceTags, eq(invoiceTags.invoiceId, invoices.id))
    .innerJoin(tags, eq(tags.id, invoiceTags.tagId))
    .groupBy(invoices.organizationId, tags.id, tags.name),
);

const ranked = db.$with('ranked').as(
  db
    .select({
      orgId: tagCounts.orgId,
      tagName: tagCounts.tagName,
      count: tagCounts.count,
      rank: sql<number>`row_number() over (
        partition by ${tagCounts.orgId}
        order by ${tagCounts.count} desc, ${tagCounts.tagId}
      )`.as('rank'),
    })
    .from(tagCounts),
);

Two tags tied on count would let row_number pick a winner arbitrarily, so ranks could shuffle between runs. Adding tagId as a second sort key breaks ties deterministically, so rank 2 is always the same tag.

← Previous 1 / 1 Next →

The final select wires both CTEs in and cuts to the top three.

const topTags = await db
  .with(tagCounts, ranked)
  .select({
    orgId: ranked.orgId,
    tagName: ranked.tagName,
    count: ranked.count,
    rank: ranked.rank,
  })
  .from(ranked)
  .where(lte(ranked.rank, 3))
  .orderBy(ranked.orgId, ranked.rank);

Both CTEs come into scope here, chained through one with. ranked was defined in terms of tag_counts, and the main query gets both.

const topTags = await db
  .with(tagCounts, ranked)
  .select({
    orgId: ranked.orgId,
    tagName: ranked.tagName,
    count: ranked.count,
    rank: ranked.rank,
  })
  .from(ranked)
  .where(lte(ranked.rank, 3))
  .orderBy(ranked.orgId, ranked.rank);

The payoff: rank is now a real column, so an ordinary where can filter it, keeping ranks 1, 2, 3 per org and dropping the rest.

← Previous 1 / 1 Next →

The rows come back as { orgId, tagName, count, rank }, fully inferred through two CTEs and a window function with no hand-written interface, the same “derive, don’t declare” reflex from earlier lessons.

Now write one yourself: a CTE plus row_number(), assembled into a real ranking.

Return the top 2 most-used tags for each organization, by how many invoices carry each tag. The tag_counts CTE is written for you. Finish the ranked CTE's row_number() window, wire both CTEs into the final query with .with(...), and keep only ranks 1 and 2 per org. Ties on count are broken by tag id, lower id wins. Return orgId, tagName, count, and rank, ordered by org then rank.

View schema & seed rows

Schema (Drizzle)

export const organizations = pgTable('organizations', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
});

export const invoices = pgTable('invoices', {
  id: integer('id').primaryKey(),
  organizationId: integer('organization_id')
    .notNull()
    .references(() => organizations.id),
});

export const tags = pgTable('tags', {
  id: integer('id').primaryKey(),
  name: text('name').notNull(),
});

export const invoiceTags = pgTable('invoice_tags', {
  invoiceId: integer('invoice_id')
    .notNull()
    .references(() => invoices.id),
  tagId: integer('tag_id')
    .notNull()
    .references(() => tags.id),
});

Seed rows (SQL)

INSERT INTO organizations (id, name) VALUES
  (1, 'Acme'),
  (2, 'Globex');

INSERT INTO tags (id, name) VALUES
  (1, 'urgent'),
  (2, 'billing'),
  (3, 'support'),
  (4, 'design');

INSERT INTO invoices (id, organization_id) VALUES
  (1, 1), (2, 1), (3, 1), (4, 1), (5, 1), (6, 1), (7, 1),
  (8, 2), (9, 2), (10, 2), (11, 2), (12, 2);

-- Acme: urgent on 3 invoices, billing on 2, support on 2 (billing & support tie)
INSERT INTO invoice_tags (invoice_id, tag_id) VALUES
  (1, 1), (2, 1), (3, 1),
  (1, 2), (2, 2),
  (3, 3), (4, 3);

-- Globex: urgent on 1 invoice, design on 4
INSERT INTO invoice_tags (invoice_id, tag_id) VALUES
  (8, 1),
  (8, 4), (9, 4), (10, 4), (11, 4);

Run query Feedback Reset

If your first instinct was to groupBy and grab each org’s single biggest tag, that’s the instinct this exercise corrects: groupBy gives you one tag per org, but the question asked for two, and the collapse threw away the runner-up. The window function keeps every tag in play long enough to rank it, then cuts to the top two.

Recursive queries, named for recognition

When data is a tree (an org chart where employees report to employees, a comment that replies to a comment, a category nested inside a category) walking it takes a query that refers to itself: a recursive CTE , written WITH RECURSIVE. It starts from a base case (the root rows), then repeatedly joins a recursive step onto its own prior results until a pass adds no new rows.

Drizzle’s query builder has no method for this. When you need one, write it as raw sql and run it through db.execute(...). Deep tree recursion is rare in early SaaS, since most hierarchies are two levels and a plain join handles them, so treat this as recognition only.

Choosing the query shape

The durable takeaway is the ladder for choosing a shape. Run it on the next two-pass query you meet:

• Existence yes/no? → exists / notExists. The answer is a boolean, so don’t build pairs or counts.
• Used once, and reads better with the set right there? → inline subquery, or a derived table if it’s an aggregate you need to join against.
• Used more than once, or naming it untangles an inside-out query? → a CTE.
• Small result, and two short reads are simpler than one composed one? → two queries in app code.
• Need to rank rows and keep the top N per group? → a CTE plus row_number().
• Walking a tree? → raw WITH RECURSIVE (rare).

Each row describes a query you need to write. Drop it into the shape you'd reach for — judged on readability first, the way this lesson taught. Drag each item into the bucket it belongs to, then press Check.

Inline / exists Used once, or a yes/no existence question.

CTE Used more than once, or naming untangles it.

App code Small result; two straight reads beat one clever one.

Filter organizations by whether any invoice exists for them

Keep invoices whose org is in a small computed set, used just once

The same per-org total is needed in three different places in one query

Rank tags and keep the top three per organization

Fetch about 10 ids, then fetch those 10 rows; both reads are tiny

A single clever query that no one on the team can read at a glance

Check Reset

One principle carries the lesson: a CTE that nobody can read is a worse outcome than two queries that read straight. Keep the work in the database when the intermediate is big enough that round-tripping it through TypeScript would cost real latency, not to look clever. Whether a given shape is actually fast on your data, and the EXPLAIN ANALYZE and indexing that decide it, belong to the next chapter.

External resources

Drizzle — Select, the with clause

orm.drizzle.team

Canonical reference for CTEs and subqueries in the builder: $with to define, .with() to wire them in.

Drizzle — Magic sql operator

orm.drizzle.team

How to drop window functions and other raw fragments into a query with sql<T> while keeping parameterization.

PostgreSQL — WITH Queries (CTEs)

postgresql.org

The source of truth for CTE semantics, MATERIALIZED, and WITH RECURSIVE when a real tree shows up.

How Window Functions Work (animated)

dataschool.com

Data School visualizes OVER, PARTITION BY, and ORDER BY with animated GIFs — the per-row mental model, made visual.