MaxCompute:GitHub public event data

Which open source databases were most popular from 2018 to 2022?

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;

Analysis result: elasticsearch was the most popular open source database project, followed by redis and prometheus.

How did the rankings of the top database projects change each year from 2018 to 2022?

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;

Analysis result: clickhouse showed the fastest growth of any project, rising from tenth place in 2018 to first place in 2021. In 2022, it was overtaken by the steadily rising redis. taosdata/TDengine reached first place in 2019 but dropped to ninth within a year.

How did monthly star counts for the top 10 database projects trend from 2018 to 2022?

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;

Analysis result: elasticsearch was consistently the most starred database project. clickhouse saw rapid cumulative growth starting in 2021.

Which databases were most popular in the first half of 2023?

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;

Analysis result: clickhouse was the most popular database in the first half of 2023, followed by prometheus and redis.

Which databases were most actively maintained in the first half of 2023?

This query measures activity by counting opened pull request events, which reflects ongoing development work.

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;

Analysis result: StarRocks was the most actively maintained open source database project in the first half of 2023.

Who were the top individual contributors to the most active database project?

This query counts contribution events — pull requests, issues, comments, reviews, and pushes — per contributor for a given repository and time range.

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;

Analysis result: kangkaisen was the top individual contributor to the StarRocks/starrocks repository in 2021.

What were the top 10 most used programming languages over the past year?

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;

Analysis result: JavaScript was the most used language, followed by TypeScript and Python.

Which programming language projects received the most stars from 2018 to 2022?

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;

Analysis result: Go was the most popular programming language project with 81,642 stars, followed by TypeScript and Node.

How did cumulative star counts for the top 10 language projects grow month by month?

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;

Analysis result: Go had the fastest cumulative star growth of any programming language project over the five-year period.