Skip to content

Instantly share code, notes, and snippets.

@YumaInaura
Last active February 9, 2023 01:28
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Embed
What would you like to do?
BigQuery — Nested WITH clause ( WITH is like a Subquery )

BigQuery — Nested WITH clause ( WITH is like a Subquery )

Query Example

WITH result AS (
   WITH example AS ( SELECT * FROM `dataset.table` )
   SELECT * FROM example
)

SELECT * FROM result

Public Dataset Query Example

WITH result AS (
   WITH games_wide_limitted AS ( SELECT * FROM `bigquery-public-data.baseball.games_wide` LIMIT 100 )
   SELECT * FROM games_wide_limitted
)

SELECT * FROM result

Deeper nest

WITH result AS (
   WITH reg_season AS (
     WITH  year_2016 AS (
       SELECT * FROM `bigquery-public-data.baseball.games_wide` WHERE year = 2016
     )
     SELECT * FROM year_2016 WHERE seasonType = 'REG'
   )
   SELECT * FROM reg_season
)

SELECT * FROM result

Links

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment