BigQuery pipe syntax: A much cleaner way to write SQL

I've been doing SQL for nearly 15 years I think I can get behind the new syntax. I really like that I can start with the from clause. It's something I really like in things like DuckDB.

Examples:

-- Standard Syntax SELECT AVG(num_trips) AS avg_trips_per_year, payment_type

FROM

(

SELECT EXTRACT(YEAR FROM trip_start_timestamp) as year,

payment_type, COUNT() AS num_trips

FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`

GROUP BY year, payment_type

)

GROUP BY payment_type

ORDER BY payment_type;

Here’s that same query using pipe syntax — no subquery needed!

-- Pipe Syntax FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`

|> EXTEND EXTRACT(YEAR FROM trip_start_timestamp) AS year

|> AGGREGATE COUNT() AS num_trips

   GROUP BY year, payment_type

|> AGGREGATE AVG(num_trips) AS avg_trips_per_year

GROUP BY payment_type ASC;