Skip to content

Instantly share code, notes, and snippets.

@amcasari

amcasari/standard-sql-table-ranges.sql

Created July 3, 2021 00:10
Show Gist options
  • Save amcasari/6a9d971aadf33ec0d80ba5cea6c2f98e to your computer and use it in GitHub Desktop.
Save amcasari/6a9d971aadf33ec0d80ba5cea6c2f98e to your computer and use it in GitHub Desktop.
Download ZIP
updated table-ranges.sql for gharchive.org
Raw
standard-sql-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')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment