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
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
|> AGGREGATE AVG(num_trips) AS avg_trips_per_yearGROUP BY payment_type ASC;