Skip to content

Instantly share code, notes, and snippets.

@igrigorik

igrigorik/table-ranges.sql

Last active July 21, 2021 23:13
Show Gist options
  • Save igrigorik/1cf65f6528e27ed1a580 to your computer and use it in GitHub Desktop.
Save igrigorik/1cf65f6528e27ed1a580 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
table-ranges.sql
/* count number of pushes between Jan 1 and Jan 5 */
SELECT
COUNT(*)
FROM `githubarchive.day.2015*`
WHERE
type = 'PushEvent'
AND (_TABLE_SUFFIX BETWEEN '0101' AND '0105')
/* count number of watches between Jan~Oct 2014 */
SELECT COUNT(*)
FROM `githubarchive.month.2014*`
WHERE
type = 'WatchEvent'
AND (_TABLE_SUFFIX BETWEEN '01' AND '10')
*/
/* count number of forks in 2012~2014 */
SELECT COUNT(*)
FROM `githubarchive.year.20*`
WHERE
type = 'ForkEvent'
AND (_TABLE_SUFFIX BETWEEN '12' AND '14')
@amcasari
Copy link

amcasari commented Jul 3, 2021 •
edited
Loading

BigQuery has updated Legacy + Standard SQL formatting for table wildcards so these snippets no longer work on githubarchive on BQ.

I was able to get the same desired results using _TABLE_SUFFIX, if it helps update documentation: https://gist.github.com/amcasari/6a9d971aadf33ec0d80ba5cea6c2f98e

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