Big Query Script fetching last 3 months top trending GitHub projects with unique issues

This guide shows software house founders how to run a ready-made BigQuery script that lists the top 1000 GitHub repositories with the fastest growth in stars and issues over the last three months.

How to Use This in Google BigQuery (Step by Step)

1. Get a Google Cloud account

   • Go to cloud.google.com and sign in with your Google account.

   • If you’ve never used Google Cloud before, enable billing (you can set a monthly budget to control spend).

   • The free tier gives you 1 TB of BigQuery queries per month — plenty for this script if you run it weekly.

2. Open BigQuery

   • Navigate to console.cloud.google.com/bigquery.

   • At the top, select or create a new project (e.g., software-house-trends).

3. Create a dataset for your results

   • In the left panel, click your project name → Create dataset.

   • Name it gh_trending.

   • Set Location to US (GitHub Archive is stored in the US).

   • Click Create dataset.

4. Open the SQL editor

   • Click Compose new query.

   • Copy and paste the provided SQL script into the editor.

5. Set a safe cost limit

   • In the query editor, click Query settings → Advanced options.

   • Set Maximum bytes billed to something like 10 GB.

   • This prevents accidental large scans.

6. Run the query

   • Click Run.

   • Wait a few seconds — the results table will appear below the editor.

7. Save or export the results

   • Click Save Results in the top right of the results table.

   • Choose CSV, JSON, or Google Sheets if you want to share or analyze it further.

8. (Optional) Save as a view for reuse

   • Above the query, click Save → Save View.

   • Store it in the gh_trending dataset so you can run it again without copying SQL each time.

9. (Optional) Schedule to run automatically

   • With the query open, click Schedule.

   • Choose how often to refresh (daily, weekly).

   • Set the destination table in gh_trending so it overwrites with fresh data each run.

   • This gives you an always-up-to-date trending list.

10. Review regularly

• Go to the dataset → click the saved view or table → view results.

• You can download or share with your team anytime.

-- Top repos by UNIQUE STARRERS in the last 3 months,
-- plus unique issue openers and total issues opened (same window).

DECLARE m0 STRING DEFAULT FORMAT_DATE('%Y%m', DATE_TRUNC(CURRENT_DATE(), MONTH));
DECLARE m1 STRING DEFAULT FORMAT_DATE('%Y%m', DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH));
DECLARE m2 STRING DEFAULT FORMAT_DATE('%Y%m', DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH), MONTH));

-- Keep the base CTE column-pruned to help BigQuery read fewer bytes.
WITH base AS (
  SELECT
    repo.name                      AS repo_name,
    actor.login                    AS actor_login,
    type,
    JSON_VALUE(payload, '$.action') AS action,
    created_at
  FROM `githubarchive.month.*`
  WHERE _TABLE_SUFFIX IN UNNEST([m0, m1, m2])
    AND created_at >= TIMESTAMP(DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 3 MONTH))
    AND created_at <  CURRENT_TIMESTAMP()
),
stars AS (
  SELECT
    repo_name,
    COUNT(DISTINCT actor_login) AS unique_starrers
  FROM base
  WHERE type = 'WatchEvent'
    AND action = 'started'
    AND actor_login IS NOT NULL
    AND NOT ENDS_WITH(actor_login, '[bot]')
  GROUP BY repo_name
),
issues AS (
  SELECT
    repo_name,
    COUNT(DISTINCT actor_login) AS unique_issue_contributors,
    COUNT(*)                    AS issues_opened
  FROM base
  WHERE type = 'IssuesEvent'
    AND action = 'opened'
    AND actor_login IS NOT NULL
    AND NOT ENDS_WITH(actor_login, '[bot]')
  GROUP BY repo_name
)
SELECT
  COALESCE(stars.repo_name, issues.repo_name) AS repo_name,
  IFNULL(unique_starrers, 0)            AS unique_starrers,
  IFNULL(unique_issue_contributors, 0)  AS unique_issue_contributors,
  IFNULL(issues_opened, 0)              AS issues_opened
FROM stars
FULL JOIN issues USING (repo_name)
ORDER BY unique_starrers DESC, unique_issue_contributors DESC
LIMIT 1000;