Five SQL patterns I use every week

These are five small SQL habits that do most of the heavy lifting in my week. None of them is fancy. Every one of them saves me from a mess I used to make by hand!

If they look familiar, that's because they all snuck into the SQL or Python? Yes. note already. This time each one gets its own little example.

Everything below runs in DuckDB on Ookla open data, fixed tiles (home and business internet, not phones), Texas, all four quarters of 2025. A tile is a patch of map about 600 m across. It carries an average speed and a count of tests, so anything I call "per test" is really a test-weighted average of tile averages.

1. Query the file where it sits

You don't have to load a parquet file into a database first. DuckDB reads it like a table, right off the disk.

SELECT
    quarter,
    COUNT(*) AS tiles,
    SUM(tests) AS tests
FROM
    read_parquet('../data/texas_fixed_2025.parquet')
GROUP BY
    quarter
ORDER BY
    quarter
;

That's about 727,000 rows and it comes back in well under a second on my laptop. These counts are for the Texas bounding box, which spills into a few neighbors, so they run higher than the county numbers below.

2. GROUP BY ALL

This one tells SQL to group by every column that isn't wrapped in a SUM or a COUNT. Add a column to the SELECT and the GROUP BY keeps up on its own. No more "column must appear in the GROUP BY clause" errors!

SELECT
    county,
    quarter,
    COUNT(*) AS tiles,
    SUM(tests) AS tests,
    ROUND(MEDIAN(avg_d_kbps) / 1000) AS median_tile_mbps
FROM
    read_parquet('../data/tx_tiles_by_county.parquet')
WHERE
    county IN ('Harris', 'Travis', 'Loving')
GROUP BY ALL
ORDER BY
    county,
    quarter
;

I flipped the quarters into columns to fit it on the page; the query returns one row per county and quarter.

Loving County is in there on purpose. It had 346 tests in Q4, against Harris County's 230,210, so its median wobbles from quarter to quarter. A median over a few hundred tiles is a lot shakier than one over ten thousand.

3. FILTER for conditional counts

The old way is to write SUM(CASE WHEN ... THEN tests ELSE 0 END) for every column you want. FILTER says the same thing in plain words, and you can put several of them side by side in one pass over the data. (DuckDB also lets the last line reuse tests_under_100 by name. Most other databases make you write it out again.)

SELECT
    county,
    SUM(tests) AS tests,
    SUM(tests) FILTER (WHERE avg_d_kbps < 100000) AS tests_under_100,
    ROUND(100.0 * tests_under_100 / SUM(tests), 1) AS pct_under_100
FROM
    read_parquet('../data/tx_tiles_by_county.parquet')
WHERE
    quarter = 4
GROUP BY ALL
HAVING
    SUM(tests) >= 5000
ORDER BY
    pct_under_100 DESC
LIMIT 5
;

Read the "under 100 Mbps" column carefully. It counts tests in tiles whose average download is under 100 Mbps. Some of those tests were faster than 100 and some tiles above the line hold slow tests, because the tile average is all the open data gives us. And these are tests, not homes, so a house that tests a lot counts a lot (that's the whole sampling note).

4. Running totals with a window

How many Texas counties does it take to cover half of all the fixed tests? Sort the counties, then keep a running sum. A window function does that without collapsing the rows.

WITH by_county AS (
    SELECT
        county,
        SUM(tests) AS tests
    FROM
        read_parquet('../data/tx_tiles_by_county.parquet')
    WHERE
        quarter = 4
    GROUP BY ALL
)

SELECT
    county,
    tests,
    ROW_NUMBER() OVER (ORDER BY tests DESC) AS rank,
    ROUND(
        100.0 * SUM(tests) OVER (ORDER BY tests DESC ROWS UNBOUNDED PRECEDING)
        / SUM(tests) OVER (),
        1
    ) AS running_pct
FROM
    by_county
ORDER BY
    tests DESC
LIMIT 8
;

Eight counties out of 254 hold more than half of the state's fixed tests. That's mostly where people live, and it's a good reason to check which counties are driving any statewide number before you quote it.

The empty OVER () is the other half of the trick. It's the total over every row, so you can divide by it without a second query.

5. CTEs that stack small steps

A CTE is a named step (WITH this AS (...)). Stack a few and the query reads top to bottom like a recipe, and you can run any step on its own when the answer looks weird. Which it did here!

WITH tiles AS (
    -- just the columns we need, in Mbps
    SELECT
        county,
        quarter,
        tests,
        avg_d_kbps / 1000.0 AS d_mbps
    FROM
        read_parquet('../data/tx_tiles_by_county.parquet')
),

county_quarter AS (
    -- test-weighted mean per county and quarter
    SELECT
        county,
        quarter,
        SUM(tests) AS tests,
        SUM(d_mbps * tests) / SUM(tests) AS mean_mbps
    FROM
        tiles
    GROUP BY ALL
),

q1_vs_q4 AS (
    -- Q1 next to Q4, plus the thinnest quarter's test count
    SELECT
        county,
        MAX(mean_mbps) FILTER (WHERE quarter = 1) AS q1_mbps,
        MAX(mean_mbps) FILTER (WHERE quarter = 4) AS q4_mbps,
        MIN(tests) AS min_tests
    FROM
        county_quarter
    GROUP BY ALL
)

SELECT
    county,
    ROUND(q1_mbps) AS q1_mbps,
    ROUND(q4_mbps) AS q4_mbps,
    ROUND(100 * (q4_mbps / q1_mbps - 1), 1) AS pct_change
FROM
    q1_vs_q4
WHERE
    min_tests >= 1000
ORDER BY
    pct_change DESC
LIMIT 5
;

Milam County nearly doubled in a year? I didn't believe it, so I ran step 2 on its own for Milam and added a median. The test-weighted mean went from 149 to 295 Mbps. The median tile went from 89 to 96. So a slice of Milam got a lot faster and pulled the mean up, while the typical tile barely moved. That's the Averages lie note all over again, and I only caught it because the steps had names I could poke at.

Run it yourself

All five queries live in sql-patterns/patterns.sql in the data-stories repo. python3 run.py runs them in order and prints each result. You'll need the 2025 fixed tiles (tools/fetch_ookla.py 2025) and the tile-to-county table from sql-or-python/prep.py.

A few things the open data can't tell you. Tiles hold average speeds and test counts, so every "per test" number here is a test-weighted average of tile averages. There's no split by provider or technology. And a test is one moment on one device, so one slow test doesn't mean someone has bad internet.

Speedtest® by Ookla® Global Fixed and Mobile Network Performance Maps, fixed tiles, from Ookla for Good via the AWS Open Data Registry. Based on analysis by Ookla of Speedtest Intelligence® data for Q1 to Q4 2025. Provided by Ookla and accessed 13 October 2026. Ookla trademarks used under license and reprinted with permission. Data licensed CC BY-NC-SA 4.0. County boundaries: US Census Bureau. Queries run in DuckDB.