Aggregation - GROUP BY and CTEs

Context

An aggregate function turns many rows into one value, a count, a sum or an average, and GROUP BY does that once per group, one row for each neighborhood or each condition, instead of once for the whole table.

Most of what you learn about a dataset before modeling it is an aggregate: how many listings each neighborhood has, how many of them have a price, what a typical price is in each, which neighborhood costs most per square meter. Some of the same numbers become features, a neighborhood's listing count among them, and keeping the most expensive or the newest listing per group is a common way to reduce a table to one row per entity.

Aggregates are quiet about what they leave out. An average ignores missing values and divides by the known ones, a count of a column skips that column's NULLs, and a row count, a sum or an average computed after a join through a duplicated key counts those rows twice without an error. Lesson 2.1.2 combined two tables and counted the result, and this lesson summarizes rows, per table and per group.

Goal

• Summarize a table with COUNT, SUM, AVG, MIN and MAX, stating which rows each one counted.
• Group rows with GROUP BY and order the groups by an aggregate.
• Filter rows with WHERE and groups with HAVING in one query, each in its place.
• Name an intermediate result with a CTE and join it back to compare each row with its group.
• Keep one whole row per group with ROW_NUMBER, ordered by enough keys to break every tie.

Prerequisites

Optional, since the lesson builds its tables from an empty folder. They are here if you want a first look at the lesson's two newest shapes.

• YouTube - "Advanced SQL Tutorial | CTE (Common Table Expression)" by Alex The Analyst. Four minutes on writing a CTE and querying it.
• Official docs - SQLite, "Built-in Aggregate Functions". SQLite's built-in aggregates, and what each one does with NULL.

Table of Contents

• Aggregate Functions
• GROUP BY
• HAVING
• CTEs - WITH
• Ranking Within Groups - ROW_NUMBER

Theory

Nothing here is installed. The lesson opens with the same build_housing_db.py as Lesson 2.1.2, so it runs from an empty folder, and every block after it is a script saved in ~/dev/bootcamp and run with python3.

Aggregate Functions

What. An aggregate function reduces many rows to one value: COUNT counts, SUM adds, AVG averages, and MIN and MAX pick the extremes. A query whose columns are all aggregates and that has no GROUP BY returns exactly one row. Why. Every summary a dataset is checked against, how many listings, what a typical price is, is an aggregate, and each aggregate decides what to do with NULL in a way that changes the answer. When. Use an aggregate whenever the question is about a set of rows rather than one, and know for each one which rows it counted.

# 'build_housing_db.py', rebuilds both tables on every run
import sqlite3

LISTINGS = [
    (1, "Downtown", 472000, 88.0, 3, "good", "2024-03-02"),
    (2, "Riverside", 318500, 74.5, 2, "fair", "2024-01-15"),
    (3, "Hillcrest", 389000, 120.0, 4, "excellent", "2024-02-20"),
    (4, "Eastgate", 228000, 61.0, 2, "poor", "2024-03-11"),
    (5, "Downtown", None, 95.0, 3, "good", "2024-02-08"),
    (6, "Northend", 274500, 102.5, 4, None, "2024-01-29"),
    (7, "Riverside", 420000, 110.0, 3, "good", "2024-03-18"),
    (8, "Lakeview", 351000, 97.0, 3, "excellent", "2024-02-14"),
    (9, "Southgate", 199000, 58.0, 1, "fair", "2024-01-07"),
    (10, "Hillcrest", 389000, 105.0, 3, "good", "2024-03-25"),
    (11, "Downtown", 512000, 101.0, 4, "excellent", "2024-01-22"),
    (12, "Eastgate", 300000, 83.0, 3, "fair", "2024-02-27"),
]
REGIONS = [
    ("Downtown", "Central", 1.2),
    ("Riverside", "Central", 3.5),
    ("Riverside", "Central", 3.7),
    ("Hillcrest", "North", 7.8),
    ("Eastgate", "East", 5.1),
    ("Northend", "North", 9.4),
    ("Lakeview", "West", None),
]

con = sqlite3.connect("housing.db")
con.execute("DROP TABLE IF EXISTS listings")
con.execute("DROP TABLE IF EXISTS regions")
con.execute("""
    CREATE TABLE listings (
        id INTEGER PRIMARY KEY,
        neighborhood TEXT NOT NULL,
        price INTEGER,
        area_sqm REAL NOT NULL,
        rooms INTEGER NOT NULL,
        condition TEXT,
        listed_on TEXT NOT NULL
    ) STRICT
""")
# no key on 'neighborhood', so nothing refused the second Riverside row
con.execute("""
    CREATE TABLE regions (
        neighborhood TEXT,
        region TEXT,
        distance_km REAL
    ) STRICT
""")
con.executemany("INSERT INTO listings VALUES (?, ?, ?, ?, ?, ?, ?)", LISTINGS)
con.executemany("INSERT INTO regions VALUES (?, ?, ?)", REGIONS)
con.commit()
listings = con.execute("SELECT * FROM listings").fetchall()
regions = con.execute("SELECT * FROM regions").fetchall()
print(len(listings), len(regions))  # 12 7
con.close()

COUNT(*) counts rows, COUNT(price) counts the rows whose price is not NULL, and COUNT(DISTINCT neighborhood) counts different values. SUM, AVG, MIN and MAX skip NULL in the same way, so AVG(price) divides the sum of the known prices by how many are known, 11, not by the 12 listings. MIN and MAX work on text as well, and dates written as YYYY-MM-DD sort as text in date order.

import sqlite3

con = sqlite3.connect("housing.db")
counts = con.execute("""
    SELECT COUNT(*), COUNT(price), COUNT(DISTINCT neighborhood)
    FROM listings
""").fetchall()
print(counts)            # [(12, 11, 7)]
stats = con.execute("""
    SELECT SUM(price), ROUND(AVG(price), 1), ROUND(SUM(price) * 1.0 / COUNT(*), 1),
           MIN(price), MAX(price), MIN(listed_on), MAX(listed_on)
    FROM listings
""").fetchone()
# (3853000, 350272.7, 321083.3, 199000, 512000, '2024-01-07', '2024-03-25')
print(stats)
empty = con.execute("SELECT COUNT(*), AVG(price) FROM listings WHERE price > 900000")
print(empty.fetchall())  # [(0, None)]
con.close()

The two averages differ because AVG divided 3853000 by the 11 known prices, while dividing by COUNT(*) spread the same sum over all 12 listings, the average you would get if the missing price were 0. With no matching rows the query still returns one row, COUNT 0 and every other aggregate NULL.

DON'T:

• DON'T use COUNT(price) as the number of listings. It returned 11 of 12, one per known price.
• DON'T divide a SUM by COUNT(*) and call it the average of a column with missing values. It gave 321083.3 where AVG gave 350272.7, as if listing 5's price were 0.
• DON'T do arithmetic on an aggregate taken over no rows. AVG(price) came back None, and None * 1.1 raises TypeError: unsupported operand type(s) for *: 'NoneType' and 'float'.

GROUP BY

What. GROUP BY splits the rows into groups that share a value and returns one row per group, every aggregate computed within its own group. Why. Per-neighborhood counts and averages are the first look at any table before modeling it, and some become features themselves, a neighborhood's listing count among them. When. Use GROUP BY whenever a question says "per", and select only the grouped columns and aggregates.

Each result row is one group: the grouped column's value and every aggregate over that group's rows. ORDER BY can sort the groups by an aggregate or its alias, grouping by two columns makes one group per distinct pair, and NULL forms a group of its own. ROUND with no places rounds to a whole number and still returns a decimal, such as 492000.0.

import sqlite3

con = sqlite3.connect("housing.db")
hoods = con.execute("""
    SELECT neighborhood, COUNT(*) AS n, COUNT(price) AS priced,
           ROUND(AVG(price)) AS avg_price
    FROM listings
    GROUP BY neighborhood
    ORDER BY avg_price DESC
""").fetchall()
for row in hoods:
    print(row)
conditions = con.execute("""
    SELECT condition, COUNT(*), ROUND(AVG(price)) FROM listings
    GROUP BY condition
""").fetchall()
print(conditions)
pairs = con.execute("""
    SELECT neighborhood, condition, COUNT(*) FROM listings
    GROUP BY neighborhood, condition
""").fetchall()
print(len(pairs))
con.close()

('Downtown', 3, 2, 492000.0)
('Hillcrest', 2, 2, 389000.0)
('Riverside', 2, 2, 369250.0)
('Lakeview', 1, 1, 351000.0)
('Northend', 1, 1, 274500.0)
('Eastgate', 2, 2, 264000.0)
('Southgate', 1, 1, 199000.0)
[(None, 1, 274500.0), ('excellent', 3, 417333.0), ('fair', 3, 272500.0), ('good', 4, 427000.0), ('poor', 1, 228000.0)]
11

Downtown counted three listings and averaged two prices, the same skipping as before, now per group. SQLite also accepts a column that is neither grouped nor aggregated, a bare column, and fills it from one row of the group without an error, where most other SQL databases refuse the query:

import sqlite3

con = sqlite3.connect("housing.db")
rows = con.execute("""
    SELECT neighborhood, listed_on, ROUND(AVG(price)) FROM listings
    GROUP BY neighborhood
    ORDER BY neighborhood
""").fetchall()
print(rows[0])  # ('Downtown', '2024-03-02', 492000.0)
con.close()

Downtown got 2024-03-02, listing 1's date, one of its three listings' dates, and nothing in the query chose that one.

DON'T:

• DON'T select a bare column in a grouped query. SQLite filled Downtown's listed_on from listing 1 and raised nothing, and the query never said which listing.
• DON'T trust a bare column beside MAX. With one MIN or MAX in the query, SQLite takes the bare columns from the row holding that value, a rule of its own, and on a tie, like Hillcrest's two listings at 389000, from either row.
• DON'T forget the NULL group. GROUP BY condition put listing 6 in a group of its own, labeled None.

HAVING

What. HAVING filters groups after they are aggregated, where WHERE filters rows before any group exists. Why. "Neighborhoods with at least two priced listings" is a condition on a group's count, and that count does not exist until the groups do. When. Put a condition on single rows in WHERE and a condition on an aggregate in HAVING, and use both in one query when the question needs both.

WHERE runs before the groups are formed and HAVING after, so a WHERE condition changes what each group contains, while a HAVING condition keeps or removes whole groups. An aggregate cannot appear in WHERE, because when WHERE runs no group exists to aggregate.

import sqlite3

con = sqlite3.connect("housing.db")
rows = con.execute("""
    SELECT neighborhood, COUNT(*) AS n, ROUND(AVG(price)) AS avg_price
    FROM listings
    WHERE price IS NOT NULL
    GROUP BY neighborhood
    HAVING COUNT(*) >= 2
    ORDER BY avg_price DESC
""").fetchall()
print(rows)
for sql in (
    "SELECT neighborhood FROM listings WHERE COUNT(*) >= 2 GROUP BY neighborhood",
    "SELECT id, price FROM listings WHERE price > AVG(price)",
):
    try:
        con.execute(sql)
    except sqlite3.OperationalError as err:
        print(err)
con.close()

[('Downtown', 2, 492000.0), ('Hillcrest', 2, 389000.0), ('Riverside', 2, 369250.0), ('Eastgate', 2, 264000.0)]
misuse of aggregate: COUNT()
misuse of aggregate function AVG()

WHERE price IS NOT NULL ran first, so Downtown's group held 2 rows, not 3, and HAVING then removed Lakeview, Northend and Southgate, one priced listing each. The second error is the query people write first when they want the listings above average, and the next subtitle writes its per-neighborhood version properly.

DON'T:

• DON'T put an aggregate in WHERE. COUNT(*) there raised misuse of aggregate: COUNT(), because no group existed yet.
• DON'T expect HAVING to see the rows WHERE removed. With WHERE price IS NOT NULL first, Downtown's group held 2 rows, and HAVING COUNT(*) >= 2 judged it on those 2.

CTEs - WITH

What. WITH name AS (SELECT ...) names a query's result, a common table expression, so the main query reads it like a table, and several can be chained, each reading those before it. Why. Comparing each listing with its neighborhood's average needs the average first and the comparison second, and since WHERE cannot hold an aggregate, the average needs a name. When. Use a CTE whenever a query needs an intermediate result, a per-group number to join back or a per-row number to filter on.

The CTE below computes one average per neighborhood, and the main query joins it back to listings on neighborhood, safe from multiplying rows because GROUP BY left one row per neighborhood.

import sqlite3

con = sqlite3.connect("housing.db")
rows = con.execute("""
    WITH hood AS (
        SELECT neighborhood, AVG(price) AS avg_price
        FROM listings
        GROUP BY neighborhood
    )
    SELECT l.id, l.neighborhood, l.price, h.avg_price
    FROM listings AS l
    JOIN hood AS h ON l.neighborhood = h.neighborhood
    WHERE l.price > h.avg_price
    ORDER BY l.id
""").fetchall()
for row in rows:
    print(row)
con.close()

(7, 'Riverside', 420000, 369250.0)
(11, 'Downtown', 512000, 492000.0)
(12, 'Eastgate', 300000, 264000.0)

CTEs chain with commas. The second one below reads the first, which keeps only listings with a price, and the main query reads the second:

import sqlite3

con = sqlite3.connect("housing.db")
rows = con.execute("""
    WITH priced AS (
        SELECT neighborhood, price, area_sqm
        FROM listings
        WHERE price IS NOT NULL
    ),
    hood AS (
        SELECT neighborhood, AVG(price / area_sqm) AS avg_ppsqm
        FROM priced
        GROUP BY neighborhood
    )
    SELECT neighborhood, ROUND(avg_ppsqm, 1) FROM hood
    ORDER BY avg_ppsqm DESC
""").fetchall()
print(rows[:3])  # [('Downtown', 5216.5), ('Riverside', 4046.7), ('Eastgate', 3676.1)]
con.close()

A CTE is a readability tool, not a new power. The first query with its CTE written inside FROM in parentheses, a subquery, returns the same three listings, and what the name buys is a query that reads top to bottom, one step at a time. It lasts for one statement only.

DON'T:

• DON'T write the aggregate function itself inside WHERE to compare a row with its group. Compute it in a CTE and join it back, as above.
• DON'T expect a CTE to outlive its statement. A later SELECT * FROM hood raises no such table: hood.

Ranking Within Groups - ROW_NUMBER

What. ROW_NUMBER() OVER (PARTITION BY neighborhood ORDER BY price DESC) numbers the rows of each partition, a group that, unlike a GROUP BY group, keeps all of its rows, 1, 2, 3 in the order the window's ORDER BY gives. Why. "The most expensive listing per neighborhood" has to return whole rows, the id and the date along with the price, and GROUP BY returns them only through SQLite's own bare-column rule, which breaks a tie arbitrarily. When. Number the rows in a CTE and keep rn = 1 whenever you need one whole row per group, with enough sort keys to break every tie.

OVER makes the function a window function, computed for each row from a window of rows: PARTITION BY sets which rows share a window and ORDER BY the numbering order within it.

import sqlite3

con = sqlite3.connect("housing.db")
rows = con.execute("""
    SELECT id, neighborhood, price,
           ROW_NUMBER() OVER (PARTITION BY neighborhood ORDER BY price DESC) AS rn
    FROM listings
    WHERE neighborhood IN ('Downtown', 'Hillcrest')
""").fetchall()
for row in rows:
    print(row)
con.close()

(11, 'Downtown', 512000, 1)
(1, 'Downtown', 472000, 2)
(5, 'Downtown', None, 3)
(3, 'Hillcrest', 389000, 1)
(10, 'Hillcrest', 389000, 2)

Listings 3 and 10 tie at 389000, and ORDER BY price DESC says nothing about which comes first, so listing 3's 1 is the engine's choice, not the query's. A window function also cannot be filtered in the SELECT that computes it, so a CTE names the numbering and the outer query keeps rn = 1, with listed_on DESC breaking the tie toward the newer listing:

import sqlite3

con = sqlite3.connect("housing.db")
rows = con.execute("""
    WITH ranked AS (
        SELECT id, neighborhood, price, listed_on,
               ROW_NUMBER() OVER (
                   PARTITION BY neighborhood
                   ORDER BY price DESC, listed_on DESC
               ) AS rn
        FROM listings
    )
    SELECT id, neighborhood, price, listed_on
    FROM ranked
    WHERE rn = 1
    ORDER BY price DESC
""").fetchall()
for row in rows:
    print(row)
con.close()

(11, 'Downtown', 512000, '2024-01-22')
(7, 'Riverside', 420000, '2024-03-18')
(10, 'Hillcrest', 389000, '2024-03-25')
(8, 'Lakeview', 351000, '2024-02-14')
(12, 'Eastgate', 300000, '2024-02-27')
(6, 'Northend', 274500, '2024-01-29')
(9, 'Southgate', 199000, '2024-01-07')

DON'T:

• DON'T filter on the row number in the query that computes it. WHERE rn = 1 there raised misuse of aliased window function rn.
• DON'T leave a tie to the engine. With price DESC alone, nothing in the query chose listing 3 over listing 10, and listed_on DESC chose listing 10 for a reason anyone can read.

Lab Exercises

Lab Exercise 1: A Condition Report

Run build_housing_db.py first. In one query, print the number of listings, how many have a known condition, how many distinct conditions appear, and the first and last listing dates. Then print one row per condition, the missing one included, with its number of listings, their average area to one decimal and their average price rounded to a whole number, most expensive condition first. Finally, print how many listings have a price beside the total of the per-condition counts, and say in a comment why the total is 12 when the averages covered 11 prices.

Lab Exercise 2: Neighborhood Leaders

Run build_housing_db.py first. Print the neighborhoods with at least two priced listings and an average price per square meter above 3500, with that average to one decimal. Then, with a CTE, print every listing whose price per square meter is above its own neighborhood's average, both numbers to one decimal. Finally, with ROW_NUMBER in a CTE, print the newest listing in each neighborhood, a tie in date broken by the higher id.

import sqlite3

con = sqlite3.connect("housing.db")
summary = con.execute("""
    SELECT COUNT(*), COUNT(condition), COUNT(DISTINCT condition),
           MIN(listed_on), MAX(listed_on)
    FROM listings
""").fetchone()
print(summary)
report = con.execute("""
    SELECT condition, COUNT(*) AS n, ROUND(AVG(area_sqm), 1) AS avg_area,
           ROUND(AVG(price)) AS avg_price
    FROM listings
    GROUP BY condition
    ORDER BY avg_price DESC
""").fetchall()
for row in report:
    print(row)
priced = con.execute("SELECT COUNT(price) FROM listings").fetchone()[0]
print(priced, sum([row[1] for row in report]))
con.close()
# COUNT(*) per group counts rows whatever their price, so the groups hold all
# 12 listings, while AVG(price) in each group skipped listing 5, the one NULL.

(12, 11, 4, '2024-01-07', '2024-03-25')
('good', 4, 99.5, 427000.0)
('excellent', 3, 106.0, 417333.0)
(None, 1, 102.5, 274500.0)
('fair', 3, 71.8, 272500.0)
('poor', 1, 61.0, 228000.0)
11 12

import sqlite3

con = sqlite3.connect("housing.db")
strong = con.execute("""
    SELECT neighborhood, ROUND(AVG(price / area_sqm), 1) AS avg_ppsqm
    FROM listings
    WHERE price IS NOT NULL
    GROUP BY neighborhood
    HAVING COUNT(*) >= 2 AND AVG(price / area_sqm) > 3500
    ORDER BY avg_ppsqm DESC
""").fetchall()
print(strong)
above = con.execute("""
    WITH hood AS (
        SELECT neighborhood, AVG(price / area_sqm) AS avg_ppsqm
        FROM listings
        GROUP BY neighborhood
    )
    SELECT l.id, ROUND(l.price / l.area_sqm, 1), ROUND(h.avg_ppsqm, 1)
    FROM listings AS l
    JOIN hood AS h ON l.neighborhood = h.neighborhood
    WHERE l.price / l.area_sqm > h.avg_ppsqm
    ORDER BY l.id
""").fetchall()
print(above)
newest = con.execute("""
    WITH ranked AS (
        SELECT id, neighborhood, listed_on,
               ROW_NUMBER() OVER (
                   PARTITION BY neighborhood
                   ORDER BY listed_on DESC, id DESC
               ) AS rn
        FROM listings
    )
    SELECT neighborhood, id, listed_on FROM ranked
    WHERE rn = 1
    ORDER BY neighborhood
""").fetchall()
for row in newest:
    print(row)
con.close()

[('Downtown', 5216.5), ('Riverside', 4046.7), ('Eastgate', 3676.1)]
[(1, 5363.6, 5216.5), (2, 4275.2, 4046.7), (4, 3737.7, 3676.1), (10, 3704.8, 3473.2)]
('Downtown', 1, '2024-03-02')
('Eastgate', 4, '2024-03-11')
('Hillcrest', 10, '2024-03-25')
('Lakeview', 8, '2024-02-14')
('Northend', 6, '2024-01-29')
('Riverside', 7, '2024-03-18')
('Southgate', 9, '2024-01-07')

Theory Assessment

Q1: Three Counts and Two Averages

Explain why COUNT(*), COUNT(price) and COUNT(DISTINCT neighborhood) return 12, 11 and 7 on listings. Then explain why AVG(price) is 350272.7 while SUM(price) * 1.0 / COUNT(*) is 321083.3, and which of the two a report should call the average price.

Hint: Each count is counting something different. For the averages, both use the same sum, so look at what each divides by.

Q2: The Date Nobody Asked For

SELECT neighborhood, listed_on, AVG(price) FROM listings GROUP BY neighborhood runs without an error. Explain what listed_on holds in each row, why SQLite raises nothing, and what most other SQL databases do with the same query. Then say where listing 6 ends up in GROUP BY condition.

Hint: A group has one row in the result and several rows behind it. Ask which of those rows a bare column can come from.

Q3: Listings Above Their Average

A teammate writes SELECT id, price FROM listings WHERE price > AVG(price) to find the listings priced above average. Say what happens and why, then describe the CTE that answers the question per neighborhood, and say what that CTE can do that a subquery inside FROM cannot.

Hint: Ask what exists at the moment WHERE runs. For the last part, compare the results, not the reading.

Q4: A Region Report Built on a Duplicate

A teammate builds a per-region price report from the tables build_housing_db.py creates:

rows = con.execute("""
    SELECT r.region, COUNT(*), SUM(l.price), ROUND(AVG(l.price))
    FROM listings AS l
    JOIN regions AS r ON l.neighborhood = r.neighborhood
    GROUP BY r.region
    ORDER BY r.region
""").fetchall()
print(rows)

It prints [('Central', 7, 2461000, 410167.0), ('East', 2, 528000, 264000.0), ('North', 3, 1052500, 350833.0), ('West', 1, 351000, 351000.0)]. Say which numbers are wrong and what they should be. Then explain why a per-neighborhood version of the same report gets Riverside's average price right while this one gets Central's wrong, and name the listing that appears in no region at all. Finally, name the one fix that makes every number right.

Hint: Riverside's two listings are each counted twice. Ask what doubling every row of a group does to its average, and what doubling only some of them does.

Assignment

Assignment: A Top Four That Never Splits a Tie

The homepage shows "the four most expensive listings", and the product owner rules that two listings at the same price are shown together or not at all. Compute, for every listing with a price, three numbers over all the listings ordered by price, highest first: its ROW_NUMBER, its RANK and its DENSE_RANK. A window with no PARTITION BY treats the whole result as one partition. RANK() OVER (ORDER BY price DESC) gives tied rows the same number and then skips, so two rows tied for 4th are followed by 6th, and DENSE_RANK(), written the same way, gives tied rows the same number without skipping, so they are followed by 5th.

Print the first six rows with all three numbers. Then write top(con, n, column) that uses a CTE to return the ids whose number in column, one of "rn", "rk" and "dr", is at most n, and print its result for each column at n of 4 and 5. Finally, say in a comment which column the homepage should use, and what "top 5" means under each of the three.

Hints:

• n can travel as a ? placeholder and the column name cannot, so check the name against the three allowed ones before it goes into the SQL text, as Lesson 2.1.2's audit did with table names.
• Giving RANK the tie-breaking key that ROW_NUMBER needs would make it ROW_NUMBER, because a key that separates tied rows removes the tie RANK exists to keep.

import sqlite3

COLUMNS = ("rn", "rk", "dr")  # the only names this code puts into SQL
RANKED = """
    SELECT id, price,
           ROW_NUMBER() OVER (ORDER BY price DESC, id) AS rn,
           RANK() OVER (ORDER BY price DESC) AS rk,
           DENSE_RANK() OVER (ORDER BY price DESC) AS dr
    FROM listings
    WHERE price IS NOT NULL
"""


def top(con, n, column):
    if column not in COLUMNS:
        raise ValueError(f"not a rank column: {column}")
    rows = con.execute(f"""
        WITH ranked AS ({RANKED})
        SELECT id FROM ranked
        WHERE {column} <= ?
        ORDER BY {column}, id
    """, (n,)).fetchall()
    return [row[0] for row in rows]


con = sqlite3.connect("housing.db")
for row in con.execute(RANKED + " ORDER BY rn LIMIT 6").fetchall():
    print(row)
for n in (4, 5):
    print(n, top(con, n, "rn"), top(con, n, "rk"), top(con, n, "dr"))
con.close()
# The homepage uses rk. 'rk <= 4' keeps every listing with at most 3 listings
# priced above it, so the tie at 389000 shows whole and the list runs to 5.
# 'top 5' is 5 rows under rn, the same 5 under rk, which has no rank 5 after
# the tie, and 6 under dr, which counts distinct prices and adds listing 8.

(11, 512000, 1, 1, 1)
(1, 472000, 2, 2, 2)
(7, 420000, 3, 3, 3)
(3, 389000, 4, 4, 4)
(10, 389000, 5, 4, 4)
(8, 351000, 6, 6, 5)
4 [11, 1, 7, 3] [11, 1, 7, 3, 10] [11, 1, 7, 3, 10]
5 [11, 1, 7, 3, 10] [11, 1, 7, 3, 10] [11, 1, 7, 3, 10, 8]

Additional Resources

Optional, and nothing later depends on either. They reach past this lesson in two directions.

• YouTube - "SQL Window Function | How to write SQL Query using RANK, DENSE RANK, LEAD/LAG | SQL Queries Tutorial" by techTFQ. ROW_NUMBER, RANK, DENSE_RANK, LEAD and LAG, and the aggregates used as window functions.
• Official docs - SQLite, "Quirks, Caveats, and Gotchas In SQLite", section "Aggregate Queries Can Contain Non-Aggregate Result Columns That Are Not In The GROUP BY Clause". Why SQLite accepts a bare column, and the one case where it promises which row the value comes from.

Summary

An aggregate is a decision about which rows count, made once and then forgotten by everyone who reads the number. AVG decides that a missing price is no evidence at all, COUNT(column) decides the same about a missing value, and GROUP BY decides that NULL is a category of its own. None of these is wrong, and each can turn into a wrong report when the person reading it assumed a different decision, which is why an average is worth nothing without the count of values behind it.

The rest of the lesson is about order. WHERE runs before the groups exist and HAVING after, a CTE computes one result before the next query reads it, and ROW_NUMBER ranks rows within their group so a whole row, not a summary, survives. Each of these lets a question be asked in the order it is actually answered. They are also where the section's earlier failures come back as numbers: a duplicated key from a join inflates a sum, a NULL drops out of an average, and a tie left to the engine decides which listing is called the best. Section 2.1 ends with queries that pull out exactly the rows and summaries a model needs, and the module now turns to what happens to those numbers once they reach your script.

Cheat Sheet

Part A - Reference Table

Part B - Compact Code Block

import sqlite3

con = sqlite3.connect("housing.db")
rows = con.execute("""
    WITH hood AS (
        SELECT neighborhood, AVG(price) AS avg_price
        FROM listings
        WHERE price IS NOT NULL
        GROUP BY neighborhood
        HAVING COUNT(*) >= 2
    ),
    ranked AS (
        SELECT id, neighborhood, price,
               ROW_NUMBER() OVER (
                   PARTITION BY neighborhood
                   ORDER BY price DESC, listed_on DESC
               ) AS rn
        FROM listings
    )
    SELECT r.id, r.price, ROUND(h.avg_price)
    FROM ranked AS r
    JOIN hood AS h ON r.neighborhood = h.neighborhood
    WHERE r.rn = 1
""").fetchall()  # top listing per busy neighborhood
con.close()