BigQuery Window Functions and Analytic SQL

GROUP BY answers questions about groups and throws the rows away. Many questions need both: each day's trips and the total so far, each sale and its rank in its region, each month and the month before. A window function computes a value from a set of related rows and returns it on every row, keeping the detail. In BigQuery they are also called analytic functions, and they replace most of the self-joins that make queries slow and hard to read.

The shape of a window function

Every window function has the same form (Google Cloud: window function calls):

function_name(arguments) OVER (
  PARTITION BY ...    -- which rows belong together
  ORDER BY ...        -- their order within the partition
  ROWS BETWEEN ...    -- which of them count for this row: the frame
)

All three parts are optional, and each changes the answer:

• Partitioning. PARTITION BY restarts the calculation for each group. Without it, the whole result is one partition.
• Ordering. ORDER BY gives the rows a sequence, which running totals, LAG and ranking require.
• The frame. It picks the rows around the current one. It is where most mistakes happen, as two of the examples show.

One rule of timing matters: window functions are evaluated after GROUP BY and aggregation. So a window function can be applied to an aggregate, such as ranking groups by their COUNT(*).

Four queries on real data

The script runs four GoogleSQL queries on the New York yellow taxi trips for January 2024 (NYC TLC: trip record data). Each is parsed with the BigQuery grammar of the sqlglot library, translated to DuckDB and run locally. It is not a run of BigQuery.

# Window functions in GoogleSQL, on real trip data. Each query is parsed with sqlglot's BigQuery
# grammar, translated to DuckDB and run locally on the January 2024 file. It is not a run of BigQuery.
import duckdb
import sqlglot

con = duckdb.connect()
con.execute("CREATE VIEW trips AS SELECT * FROM read_parquet('../../dedata/yellow_tripdata_2024-01.parquet')")


def bq(title, sql, header):
    rows = con.execute(sqlglot.transpile(sql, read="bigquery", write="duckdb")[0]).fetchall()
    print(f"\n{title}\n" + "".join(f"{h:>14}" for h in header))
    for row in rows:
        print("".join(f"{str(value):>14}" for value in row))


DAILY = """
WITH daily AS (
  SELECT DATE(tpep_pickup_datetime) AS day, COUNT(*) AS trips
  FROM trips
  WHERE DATE(tpep_pickup_datetime) BETWEEN '2024-01-01' AND '2024-01-31'
  GROUP BY day)"""

bq("1. One week of daily trips, five questions answered per row", DAILY + """
SELECT day, trips,
       SUM(trips) OVER by_day AS running_total,
       CAST(AVG(trips) OVER (by_day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS INT64) AS avg_7_days,
       trips - LAG(trips) OVER by_day AS change,
       ROUND(100 * trips / SUM(trips) OVER (), 2) AS pct_of_month,
       RANK() OVER (ORDER BY trips DESC) AS busiest
FROM daily
QUALIFY day BETWEEN '2024-01-08' AND '2024-01-14'
WINDOW by_day AS (ORDER BY day)
ORDER BY day""", ["day", "trips", "running_total", "avg_7_days", "change", "pct_of_month", "busiest"])

bq("2. The busiest pickup zone of each day, with QUALIFY", """
SELECT DATE(tpep_pickup_datetime) AS day, PULocationID AS zone, COUNT(*) AS trips
FROM trips
WHERE DATE(tpep_pickup_datetime) BETWEEN '2024-01-08' AND '2024-01-12'
GROUP BY day, zone
QUALIFY ROW_NUMBER() OVER (PARTITION BY day ORDER BY COUNT(*) DESC) = 1
ORDER BY day""", ["day", "zone", "trips"])

bq("3. LAST_VALUE with the default frame, and with the whole partition", DAILY + """
SELECT day, trips,
       LAST_VALUE(trips) OVER (ORDER BY day) AS default_frame,
       LAST_VALUE(trips) OVER (ORDER BY day
                               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS whole_frame
FROM daily
QUALIFY day >= '2024-01-29'
ORDER BY day""", ["day", "trips", "default_frame", "whole_frame"])

bq("4. Ties: zones ranked by fewest pickups on 15 January, rows 27 to 32", """
WITH zones AS (
  SELECT PULocationID AS zone, COUNT(*) AS trips
  FROM trips
  WHERE DATE(tpep_pickup_datetime) = '2024-01-15'
  GROUP BY zone)
SELECT zone, trips,
       ROW_NUMBER() OVER (ORDER BY trips, zone) AS row_num,
       RANK() OVER (ORDER BY trips) AS rank_,
       DENSE_RANK() OVER (ORDER BY trips) AS dense_rank_,
       SUM(trips) OVER (ORDER BY trips) AS sum_range,
       SUM(trips) OVER (ORDER BY trips, zone ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_rows
FROM zones
QUALIFY row_num BETWEEN 27 AND 32
ORDER BY trips, zone""", ["zone", "trips", "row_num", "rank", "dense_rank", "sum_range", "sum_rows"])

1. One week of daily trips, five questions answered per row
           day         trips running_total    avg_7_days        change  pct_of_month       busiest
    2024-01-08         80034        689732         86960         12491           2.7            28
    2024-01-09         93962        783694         89595         13928          3.17            20
    2024-01-10         95000        878694         91391          1038           3.2            18
    2024-01-11        105010        983704         91692         10010          3.54             8
    2024-01-12        103655       1087359         91760         -1355           3.5            10
    2024-01-13        104758       1192117         92852          1103          3.53             9
    2024-01-14         94420       1286537         96691        -10338          3.18            19

2. The busiest pickup zone of each day, with QUALIFY
           day          zone         trips
    2024-01-08           132          5092
    2024-01-09           161          5756
    2024-01-10           161          5414
    2024-01-11           161          5808
    2024-01-12           132          4995

3. LAST_VALUE with the default frame, and with the whole partition
           day         trips default_frame   whole_frame
    2024-01-29         84547         84547        102120
    2024-01-30        101233        101233        102120
    2024-01-31        102120        102120        102120

4. Ties: zones ranked by fewest pickups on 15 January, rows 27 to 32
          zone         trips       row_num          rank    dense_rank     sum_range      sum_rows
           252             1            27             1             1            29            27
           257             1            28             1             1            29            28
           258             1            29             1             1            29            29
             9             2            30            30             2            69            31
            11             2            31            30             2            69            33
            15             2            32            30             2            69            35

Query 1: five answers on every row

The first query aggregates trips by day, then adds five window calculations to each day.

• Running total. SUM(trips) OVER by_day accumulates in date order. By 14 January there had been 1,286,537 trips.
• Moving average. The same window with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW averages a trailing week, which smooths the weekday pattern.
• Change from the previous day. LAG(trips) fetches the prior row's value. Sunday 14 January was 10,338 trips below Saturday.
• Share of the month. SUM(trips) OVER () with an empty window is the grand total, so each day can be divided by it.
• Rank. RANK() OVER (ORDER BY trips DESC) places each day among all 31.

Two features of the query deserve attention. WINDOW by_day AS (ORDER BY day) defines a named window once and reuses it three times, extended with a frame where needed. And the week is selected with QUALIFY, not WHERE. That is deliberate. WHERE runs before the window functions, so filtering to one week there would make the running total start at zero on 8 January and the rank compare only seven days. QUALIFY runs after them, so the figures are computed over the month and only then trimmed (Google Cloud: query syntax).

Query 2: the top row in each group

"The best one per group" is the commonest window pattern. Number the rows within each partition and keep number one:

• PARTITION BY day restarts the numbering every day.
• ORDER BY COUNT(*) DESC sorts zones by trips, an aggregate used inside a window.
• QUALIFY ... = 1 keeps the top zone, with no subquery.

On every day the answer is either zone 161, Midtown Center, or zone 132, JFK Airport. Without QUALIFY the same result needs the query wrapped in another query with a WHERE on the row number.

Query 3: the default frame trap

LAST_VALUE(trips) OVER (ORDER BY day) looks as if it should return the last day's trips. It returns the current row's own value. The output shows it: 84,547 on the 29th, 101,233 on the 30th. The function is defined as returning the value for the last row in the current window frame (Google Cloud: navigation functions), and the frame is the catch.

The reason is the default frame. When a window has ORDER BY and no frame, BigQuery uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The frame ends at the current row, so the last value in it is the current row. Stating the frame fixes it:

LAST_VALUE(trips) OVER (
  ORDER BY day
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Now every row shows 102,120, the trips on 31 January. No error was raised in either case, which is what makes this mistake expensive.

Query 4: ties, and the second frame trap

The fourth query ranks pickup zones by fewest trips on 15 January, where 29 zones had exactly one trip. Rows 27 to 32 straddle the boundary.

The three numbering functions differ only in how they treat ties (Google Cloud: numbering functions):

Use ROW_NUMBER to pick exactly one row, RANK for positions in the sporting sense, and DENSE_RANK when you want the nth distinct value.

The last two columns are the same running sum with different frames, and they disagree. sum_range uses the default frame, which is a RANGE. A range frame includes every row that ties with the current one in the ORDER BY, so all 29 single-trip zones show 29, and the next group jumps to 69. sum_rows uses a ROWS frame and counts one row at a time: 27, 28, 29, 31.

A running total over a column with duplicates therefore needs two things: an explicit ROWS frame, and an ORDER BY that is unique, here trips, zone, so the order of tied rows is defined.

Rules that prevent wrong answers

1. State the frame whenever you use ORDER BY with an aggregate. Do not rely on the default.
2. Make the window's ORDER BY unique when the result depends on row order.
3. Filter window results with QUALIFY, and filter input rows with WHERE. Know which one you mean.
4. Check the first rows of a moving average. With 6 PRECEDING, the first six rows average fewer than seven values.
5. Name windows you reuse. One definition cannot drift out of step with itself.
6. Remember NULL from LAG. The first row of each partition has no predecessor, as a default or COALESCE can handle.

When a window function is the wrong tool

• You only need group totals. Use GROUP BY; it returns fewer rows.
• The partition is the whole of a very large table and needs ordering. Sorting everything in one partition is heavy work for any engine. Partition by something, or aggregate first as these examples do.
• You need a value from a different table. That is still a join.

Related reading: nested and repeated fields for arrays, BigQuery SQL tips for writing efficient queries, and BigQuery explained for how the engine runs them.

Analytic SQL is part of the Data Engineering course.

Sources

• Window function calls (Google Cloud Documentation) — Google Cloud
• Query syntax (Google Cloud Documentation) — Google Cloud
• Numbering functions (Google Cloud Documentation) — Google Cloud
• Navigation functions (Google Cloud Documentation) — Google Cloud
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission