3

I am currently trying to obtain the top 100 java repositories having maximum number of stars and less than 100 commits using GitHub Archive and BigQuery. Could you please help to come up with a query for obtaining the top 100 repositories having maximum number of stars.

The final query that I have obtained is:

SELECT repository_name
FROM [githubarchive:github.timeline]
WHERE repository_language = 'Java' 
AND PARSE_UTC_USEC(repository_created_at) BETWEEN PARSE_UTC_USEC('1996-01-01 00:00:00') AND PARSE_UTC_USEC('2015-05-30 00:00:00') 
GROUP BY repository_name
HAVING COUNT(*) < 100 
ORDER BY COUNT(*) DESC 
LIMIT 100
user2475467
  • 129
  • 1
  • 10

1 Answers1

3

I think this query will work for you. Your existing query would not run because the ORDER BY clause references an aggregate calculation. ORDER BY requires the expression to refer to a field. Moving the COUNT into the SELECT clause fixes that part.

Furthermore, if you're looking for counts of git commits, you should actually check that the timeline event is a commit by adding AND payload_commit IS NOT NULL to the WHERE clause!

SELECT
  repository_name,
  COUNT(1) AS CommitCount
FROM
  [githubarchive:github.timeline]
WHERE
  repository_language = 'Java'
  AND PARSE_UTC_USEC(repository_created_at)
    BETWEEN PARSE_UTC_USEC('1996-01-01 00:00:00')
    AND PARSE_UTC_USEC('2015-05-30 00:00:00')
AND payload_commit IS NOT NULL
GROUP BY
  repository_name
HAVING
  CommitCount < 100
ORDER BY
  CommitCount DESC
LIMIT
  100
Matthew Wesley
  • 616
  • 3
  • 5
  • thank you very much for the reply... how should I first filter the projects having maximum number of stars and then filter those projects based on the number of commits – user2475467 Jun 09 '15 at 03:02
  • It's not clear to me how GitHub exposes a "star" event in the timeline data. None of the event types seem to correspond. Perhaps they added stars after this archive was created? – Matthew Wesley Jun 10 '15 at 16:02