BigQuery GoogleSQL Pipe Syntax (|>) & GROUP BY ALL Blueprint

Supanote Core Team @supanote · 1 min read

plain (sql) 1 day ago · 28 lines · 4 views

Artifact

Verified Blueprint Google BigQuery

Refactor nested BigQuery CTEs into clean top-to-bottom GoogleSQL Pipe Syntax (|>) with early partition pruning, EXTEND, and AGGREGATE.

✓ Replaces deeply nested subqueries and boilerplate CTE chains with composable |> pipe operators
✓ Applies partition/cluster pruning and column selection up front so every prefix is runnable
✓ Chains WHERE, EXTEND, AGGREGATE, JOIN, and QUALIFY top-to-bottom with zero column-list repetition

Quick Fetch

-- Google BigQuery Modern Pipe Syntax (|>) Analytics Blueprint (2025-2026 GA)
-- Every prefix up to any '|>' line is a valid standalone query for instant debugging!

-- 1. Linear Funnel & Revenue Attribution Pipeline Without Nested CTE Boilerplate
FROM `analytics_prod.events_partitioned`
-- Step A: Prune partition + cluster columns immediately to minimize scanned bytes
|> WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY)
     AND event_name IN ('page_view', 'checkout_started', 'purchase')
|> SELECT user_pseudo_id, event_date, event_name, traffic_source.source AS utm_source, ecommerce.purchase_revenue_usd
-- Step B: Derive computed columns inline via EXTEND (immediately referenceable in next pipe!)
|> EXTEND COALESCE(NULLIF(LOWER(utm_source), ''), 'direct') AS channel
-- Step C: Aggregate per user-day first
|> AGGREGATE
     COUNTIF(event_name = 'checkout_started') AS checkouts,
     COUNTIF(event_name = 'purchase') AS purchases,
     SUM(COALESCE(purchase_revenue_usd, 0)) AS user_revenue_usd
   GROUP BY event_date, channel, user_pseudo_id
-- Step D: Roll up channel KPIs and filter top-performing channels in one linear flow
|> AGGREGATE
     COUNT(DISTINCT user_pseudo_id) AS active_users,
     COUNTIF(purchases > 0) AS converting_users,
     ROUND(SAFE_DIVIDE(COUNTIF(purchases > 0) * 100.0, COUNT(DISTINCT user_pseudo_id)), 2) AS conversion_pct,
     ROUND(SUM(user_revenue_usd), 2) AS gross_revenue_usd
   GROUP BY event_date, channel
|> WHERE active_users >= 25
|> ORDER BY event_date DESC, gross_revenue_usd DESC
|> LIMIT 100;

1-- Google BigQuery Modern Pipe Syntax (|>) Analytics Blueprint (2025-2026 GA)

2-- Every prefix up to any '|>' line is a valid standalone query for instant debugging!

4-- 1. Linear Funnel & Revenue Attribution Pipeline Without Nested CTE Boilerplate

5FROM `analytics_prod.events_partitioned`

6-- Step A: Prune partition + cluster columns immediately to minimize scanned bytes

7|> WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY)

8 AND event_name IN ('page_view', 'checkout_started', 'purchase')

9|> SELECT user_pseudo_id, event_date, event_name, traffic_source.source AS utm_source, ecommerce.purchase_revenue_usd

10-- Step B: Derive computed columns inline via EXTEND (immediately referenceable in next pipe!)

11|> EXTEND COALESCE(NULLIF(LOWER(utm_source), ''), 'direct') AS channel

12-- Step C: Aggregate per user-day first

13|> AGGREGATE

14 COUNTIF(event_name = 'checkout_started') AS checkouts,

15 COUNTIF(event_name = 'purchase') AS purchases,

16 SUM(COALESCE(purchase_revenue_usd, 0)) AS user_revenue_usd

17 GROUP BY event_date, channel, user_pseudo_id

18-- Step D: Roll up channel KPIs and filter top-performing channels in one linear flow

19|> AGGREGATE

20 COUNT(DISTINCT user_pseudo_id) AS active_users,

21 COUNTIF(purchases > 0) AS converting_users,

22 ROUND(SAFE_DIVIDE(COUNTIF(purchases > 0) * 100.0, COUNT(DISTINCT user_pseudo_id)), 2) AS conversion_pct,

23 ROUND(SUM(user_revenue_usd), 2) AS gross_revenue_usd

24 GROUP BY event_date, channel

25|> WHERE active_users >= 25

26|> ORDER BY event_date DESC, gross_revenue_usd DESC

27|> LIMIT 100;

Replies 0

Reply with note

No replies yet

Every reply is a note. Start a discussion, ask a question, or attach a code snippet.

Reply with a note

Related Google BigQuery & Database, Cache & SQL Blueprints

Production-ready configurations and diagnostics from the verified Supanote library.

All Database, Cache & SQL →

SQL Google BigQuery

BigQuery FinOps: PHYSICAL vs LOGICAL Storage Savings Calculator

Audit every BigQuery dataset via INFORMATION_SCHEMA.TABLE_STORAGE to calculate exact monthly savings from switching to PHYSICAL billing.

SQL Google BigQuery

BigQuery INFORMATION_SCHEMA.JOBS Cost, Slot & Spill Profiler

Group 30-day BigQuery INFORMATION_SCHEMA.JOBS by normalized query fingerprint to expose top TB billed, slot contention, and shuffle spills.

SQL SQLite

High-Performance SQLite Production PRAGMAs

Maximum performance SQLite pragmas for WAL mode, concurrency, and memory buffers.

SQL SQLite

SQLite FTS5 Full-Text Search Schema & Sync Triggers

External content FTS5 virtual table with unicode61 tokenizer and insert, update, and delete synchronization triggers.

Interactive Developer Workbench: Online CSV & TSV Table Viewer

Turn raw comma, tab, or semicolon-delimited text into a fast, sortable spreadsheet table in one paste.

Open CSV & TSV Viewer →