MaxCompute:GitHub public event data

SET odps.namespace.schema = TRUE;

SELECT
  dws.repo_id AS repo_id,
  repos.name AS repo_name,
  SUM(dws.stars) AS stars
FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month dws
JOIN bigdata_public_dataset.github_events.db_repos repos ON dws.repo_id = repos.id
WHERE month >= '2015-01' AND month <= '2022-12'
GROUP BY dws.repo_id, repos.name
ORDER BY stars DESC
LIMIT 10;

SET odps.namespace.schema = TRUE;
SET odps.sql.validate.orderby.limit = FALSE;

WITH tmp AS (
  SELECT
    dws.repo_id AS repo_id,
    repos.name AS repo_name,
    SUM(dws.stars) AS stars,
    SUBSTR(month, 1, 4) AS year,
    ROW_NUMBER() OVER (PARTITION BY SUBSTR(month, 1, 4) ORDER BY SUM(dws.stars) DESC) AS ranknum
  FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month dws
  JOIN bigdata_public_dataset.github_events.db_repos repos ON dws.repo_id = repos.id
  WHERE month >= '2018-01'
  GROUP BY dws.repo_id, repos.name, SUBSTR(month, 1, 4)
)
SELECT repo_id, repo_name, stars, ranknum, year
FROM tmp
WHERE year <= 2022 AND ranknum <= 10
ORDER BY year ASC, ranknum ASC;

SET odps.namespace.schema = true;
SET odps.sql.validate.orderby.limit = false;

WITH top_10_repos AS (
  SELECT
    dws.repo_id AS repo_id,
    repos.name AS repo_name,
    SUM(dws.stars) AS stars
  FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month dws
  JOIN bigdata_public_dataset.github_events.db_repos repos ON dws.repo_id = repos.id
  WHERE month >= '2018-01' AND month <= '2022-12'
  GROUP BY dws.repo_id, repos.name
  ORDER BY stars DESC
  LIMIT 10
),
tmp AS (
  SELECT
    month,
    repo_id,
    stars,
    SUM(stars) OVER (PARTITION BY repo_id ORDER BY month ASC) AS total_stars
  FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month
  WHERE month >= '2015-01' AND stars IS NOT NULL
    AND repo_id IN (SELECT repo_id FROM top_10_repos)
  GROUP BY repo_id, month, stars
  ORDER BY month ASC, repo_id
)
SELECT
  tmp.month AS month,
  top_10_repos.repo_name AS repo_name,
  tmp.total_stars AS total_stars
FROM tmp
JOIN top_10_repos ON top_10_repos.repo_id = tmp.repo_id
GROUP BY month, repo_name, total_stars
ORDER BY month ASC, repo_name;

SET odps.namespace.schema = true;

SELECT
  repos.name AS repo_name,
  SUM(dws.stars) AS stars
FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month dws
JOIN bigdata_public_dataset.github_events.db_repos repos ON repos.id = dws.repo_id
WHERE month >= '2023-01' AND month <= '2023-06'
GROUP BY repo_name
ORDER BY stars DESC
LIMIT 10;

SET odps.namespace.schema = true;

SELECT
  repos.name AS repo_name,
  COUNT(dwd.id) AS num
FROM bigdata_public_dataset.github_events.dwd_github_events_odps dwd
JOIN bigdata_public_dataset.github_events.db_repos repos ON repos.id = dwd.repo_id
WHERE type = 'PullRequestEvent'
  AND ds >= '2023-01-01' AND ds <= '2023-06-30'
  AND action = 'opened'
GROUP BY repos.name
ORDER BY num DESC
LIMIT 10;

SET odps.namespace.schema = true;

WITH a AS (
  SELECT
    repo_id,
    repo_name,
    actor_id,
    actor_login,
    COUNT(*) AS contribution,
    ds
  FROM bigdata_public_dataset.github_events.dwd_github_events_odps
  WHERE ds >= '2021-01-01' AND ds <= '2021-12-31'
    AND (
      (type = 'PullRequestEvent' AND action = 'opened')
      OR (type = 'IssuesEvent' AND action = 'opened')
      OR (type = 'IssueCommentEvent' AND action = 'created')
      OR (type = 'PullRequestReviewEvent' AND action = 'created')
      OR (type = 'PullRequestReviewCommentEvent' AND action = 'created')
      OR (type = 'PushEvent' AND action IS NULL)
    )
  GROUP BY repo_id, repo_name, actor_id, actor_login, ds
)
SELECT
  repo_name,
  actor_login,
  SUM(contribution) AS contribution
FROM a
WHERE repo_name = 'StarRocks/starrocks'
  AND actor_login NOT LIKE '%[bot]'
  AND actor_login NOT LIKE 'cockroach%'
GROUP BY repo_name, actor_login
ORDER BY contribution DESC
LIMIT 10;

SET odps.namespace.schema = true;

SELECT
  language,
  COUNT(*) AS total
FROM bigdata_public_dataset.github_events.dwd_github_events_odps
WHERE ds >= DATE_ADD(GETDATE(), -365)
  AND language IS NOT NULL
GROUP BY language
ORDER BY total DESC
LIMIT 10;

SET odps.namespace.schema = TRUE;

SELECT
  dws.repo_id AS repo_id,
  repos.name AS repo_name,
  SUM(dws.stars) AS stars
FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month dws
JOIN bigdata_public_dataset.github_events.programming_language_repos repos ON dws.repo_id = repos.id
WHERE month >= '2015-01'
GROUP BY dws.repo_id, repos.name
ORDER BY stars DESC
LIMIT 10;

SET odps.namespace.schema = true;
SET odps.sql.validate.orderby.limit = false;

WITH top_10_repos AS (
  SELECT
    dws.repo_id AS repo_id,
    repos.name AS repo_name,
    SUM(dws.stars) AS stars
  FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month dws
  JOIN bigdata_public_dataset.github_events.programming_language_repos repos ON dws.repo_id = repos.id
  WHERE month >= '2018-01' AND month <= '2022-12'
  GROUP BY dws.repo_id, repos.name
  ORDER BY stars DESC
  LIMIT 10
),
tmp AS (
  SELECT
    month,
    repo_id,
    stars,
    SUM(stars) OVER (PARTITION BY repo_id ORDER BY month ASC) AS total_stars
  FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month
  WHERE month >= '2015-01' AND stars IS NOT NULL
    AND repo_id IN (SELECT repo_id FROM top_10_repos)
  GROUP BY repo_id, month, stars
  ORDER BY month ASC, repo_id
)
SELECT
  tmp.month AS month,
  top_10_repos.repo_name AS repo_name,
  tmp.total_stars AS total_stars
FROM tmp
JOIN top_10_repos ON top_10_repos.repo_id = tmp.repo_id
GROUP BY month, repo_name, total_stars
ORDER BY month ASC, repo_name;