authors_per_project.sql

#Copyright 2022 Google LLC.
#SPDX-License-Identifier: Apache-2.0
WITH
  initial_log AS (
  SELECT
    commit_hash,
    author_name,
    author_email,
    TIMESTAMP_SECONDS(author_date) AS author_date,
    REGEXP_EXTRACT(file, "(tools|examples)/[^/]+/.*") AS type,
    REGEXP_EXTRACT(file, "[tools|examples]/([^/]+)/.*") AS sub_repo,
    file,
  FROM
    `myproject.gitlogs.pso`
  WHERE
    REGEXP_EXTRACT(file, "(tools|examples)/[^/]+/.*") IS NOT NULL
    AND REGEXP_EXTRACT(file, "[tools|examples]/([^/]+)/.*") IS NOT NULL)
SELECT
  type,
  sub_repo,
  MAX(author_date) AS last_author_date,
  ARRAY_AGG(DISTINCT author_email) AS authors,
FROM
  initial_log
GROUP BY
  type,
  sub_repo
ORDER BY
  last_author_date DESC

commits_per_user_per_repo.sql

#Copyright 2022 Google LLC.
#SPDX-License-Identifier: Apache-2.0
WITH
  initial_log AS (
  SELECT
    commit_hash,
    author_name,
    author_email,
    TIMESTAMP_SECONDS(author_date) AS author_date,
    REGEXP_EXTRACT(file, "(tools|examples)/[^/]+/.*") AS type,
    REGEXP_EXTRACT(file, "[tools|examples]/([^/]+)/.*") AS sub_repo,
    file,
  FROM
    `myproject.gitlogs.pso`
  WHERE
    REGEXP_EXTRACT(file, "(tools|examples)/[^/]+/.*") IS NOT NULL
    AND REGEXP_EXTRACT(file, "[tools|examples]/([^/]+)/.*") IS NOT NULL)
SELECT
  author_email,
  sub_repo,
  COUNT(DISTINCT commit_hash) AS commits_in_folder
FROM
  initial_log
GROUP BY
  author_email,
  sub_repo

extract_type_subrepo.sql

#Copyright 2022 Google LLC.
#SPDX-License-Identifier: Apache-2.0
SELECT
  commit_hash,
  author_name,
  author_email,
  TIMESTAMP_SECONDS(author_date) AS author_date,
  REGEXP_EXTRACT(file, "(tools|examples)/[^/]+/.*") AS type,
  REGEXP_EXTRACT(file, "[tools|examples]/([^/]+)/.*") AS sub_repo,
  file,
FROM
  `myproject.gitlogs.pso`
WHERE
  REGEXP_EXTRACT(file, "(tools|examples)/[^/]+/.*") IS NOT NULL
  AND REGEXP_EXTRACT(file, "[tools|examples]/([^/]+)/.*") IS NOT NULL

last_author_by_subfolder.sql

#Copyright 2022 Google LLC.
#SPDX-License-Identifier: Apache-2.0
WITH
  initial_log AS (
  SELECT
    commit_hash,
    author_name,
    author_email,
    TIMESTAMP_SECONDS(author_date) AS author_date,
    REGEXP_EXTRACT(file, "(tools|examples)/[^/]+/.*") AS type,
    REGEXP_EXTRACT(file, "[tools|examples]/([^/]+)/.*") AS sub_repo,
    file,
  FROM
    `myproject.gitlogs.pso`
  WHERE
    REGEXP_EXTRACT(file, "(tools|examples)/[^/]+/.*") IS NOT NULL
    AND REGEXP_EXTRACT(file, "[tools|examples]/([^/]+)/.*") IS NOT NULL)
SELECT
  type,
  sub_repo,
  MAX(author_date) AS last_author_date
FROM
  initial_log
GROUP BY
  type,
  sub_repo
ORDER BY
  last_author_date DESC

top_authors_per_repo.sql

  #Copyright 2022 Google LLC.
  #SPDX-License-Identifier: Apache-2.0
WITH
  initial_log AS (
  SELECT
    commit_hash,
    author_name,
    author_email,
    TIMESTAMP_SECONDS(author_date) AS author_date,
    REGEXP_EXTRACT(file, "(tools|examples)/[^/]+/.*") AS type,
    REGEXP_EXTRACT(file, "[tools|examples]/([^/]+)/.*") AS sub_repo,
    file,
  FROM
    `myproject.gitlogs.pso`
  WHERE
    REGEXP_EXTRACT(file, "(tools|examples)/[^/]+/.*") IS NOT NULL
    AND REGEXP_EXTRACT(file, "[tools|examples]/([^/]+)/.*") IS NOT NULL),
  commits_repo_author AS (
  SELECT
    sub_repo,
    author_email,
    type,
    COUNT(DISTINCT commit_hash) AS commits_in_folder
  FROM
    initial_log
  GROUP BY
    author_email,
    sub_repo,
    type)
SELECT
  type,
  sub_repo,
  ARRAY_AGG(STRUCT(author_email,
      commits_in_folder)
  ORDER BY
    commits_in_folder DESC) AS authors
FROM
  commits_repo_author
GROUP BY
  type,
  sub_repo