Use a CTE to name a filtered local set

By Batu · English technical notes

Also published in our primary archive.

Use a CTE to name a filtered local set

A common table expression (CTE) gives a query-local name to a result set. In this example, open_events names only the rows from event whose state is open. The main query then reads that named set, groups it by kind, and counts each kind. Of the four input events, three are open: two bugs and one task. The closed bug never reaches the grouping stage, yielding the exact output [('bug', 2), ('task', 1)].

The CTE makes the filter a distinct, readable step without creating a permanent table or view. It is especially useful when the same derived set would otherwise make a query difficult to read, though this short query could also be written as one SELECT. A CTE name is scoped to its statement, so it cannot be queried in a later execute() call. The assertion verifies this fixture's counts and ORDER BY kind fixes their display order; neither result proves anything about production data volume or performance.

The state value is supplied through a qmark placeholder, and the database exists only in memory. No newer Python-specific API appears here, so Python 3 with the standard sqlite3 module is enough. The supported SQL grammar depends on the linked SQLite version; ordinary non-recursive CTEs are supported by modern SQLite releases. Python documents bound parameters and result fetching, while SQLite documents the WITH clause and its scope. Python sqlite3 documentation and SQLite WITH-clause documentation are the relevant official sources.

AI assistance disclosure: AI assisted the drafting of this example and explanation.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE event (event_id INTEGER, state TEXT, kind TEXT)")
con.executemany(
    "INSERT INTO event VALUES (?, ?, ?)",
    [(1, "open", "bug"), (2, "closed", "bug"),
     (3, "open", "task"), (4, "open", "bug")],
)

rows = con.execute(
    """
    WITH open_events AS (
        SELECT event_id, kind
        FROM event
        WHERE state = ?
    )
    SELECT kind, COUNT(*) AS count
    FROM open_events
    GROUP BY kind
    ORDER BY kind
    """,
    ("open",),
).fetchall()

assert rows == [("bug", 2), ("task", 1)]
con.close()
print(rows)

[('bug', 2), ('task', 1)]