从 DevStats 数据库生成的各种报告

with commits_data as ( select r.repo_group as repo_group, c.sha, lower(c.dup_actor_login) as actor_login, coalesce(aa.company_name, '') as company from gha_repos r, gha_commits c left join gha_actors_affiliations aa on aa.actor_id = c.dup_actor_id and aa.dt_from <= c.dup_created_at and aa.dt_to > c.dup_created_at where c.dup_repo_id = r.id and c.dup_repo_name = r.name and (c.dup_created_at >= now() - '1 week'::interval) and (lower(c.dup_actor_login) not like all(array['claassistant', 'containersshbuilder', 'wasmcloud-automation', 'fossabot', 'facebook-github-whois-bot-0', 'knative-automation', 'covbot', 'cdk8s-automation', 'github-action-benchmark', 'goreleaserbot', 'imgbotapp', 'backstage-service', 'openssl-machine', 'sizebot', 'dependabot', 'cncf-ci', 'poiana', 'svcbot-qecnsdp', 'nsmbot', 'ti-srebot', 'cf-buildpacks-eng', 'bosh-ci-push-pull', 'gprasath', 'zephyr-github', 'zephyrbot', 'strimzi-ci', 'athenabot', 'k8s-reviewable', 'codecov-io', 'grpc-testing', 'k8s-teamcity-mesosphere', 'angular-builds', 'devstats-sync', 'googlebot', 'hibernate-ci', 'coveralls', 'rktbot', 'coreosbot', 'web-flow', 'prometheus-roobot', 'cncf-bot', 'kernelprbot', 'istio-testing', 'spinnakerbot', 'pikbot', 'spinnaker-release', 'golangcibot', 'opencontrail-ci-admin', 'titanium-octobot', 'asfgit', 'appveyorbot', 'cadvisorjenkinsbot', 'gitcoinbot', 'katacontainersbot', 'prombot', 'prowbot', 'travis%bot', 'k8s-%', '%-bot', '%-robot', 'bot-%', 'robot-%', '%[bot]%', '%[robot]%', '%-jenkins', 'jenkins-%', '%-ci%bot', '%-testing', 'codecov-%', '%clabot%', '%cla-bot%', '%-gerrit', '%-bot-%', '%envoy-filter-example%', '%cibot', '%-ci'])) union select r.repo_group as repo_group, c.sha, lower(c.dup_author_login) as actor_login, coalesce(aa.company_name, '') as company from gha_repos r, gha_commits c left join gha_actors_affiliations aa on aa.actor_id = c.author_id and aa.dt_from <= c.dup_created_at and aa.dt_to > c.dup_created_at where c.dup_repo_id = r.id and c.dup_repo_name = r.name and c.author_id is not null and (c.dup_created_at >= now() - '1 week'::interval) and (lower(c.dup_author_login) not like all(array['claassistant', 'containersshbuilder', 'wasmcloud-automation', 'fossabot', 'facebook-github-whois-bot-0', 'knative-automation', 'covbot', 'cdk8s-automation', 'github-action-benchmark', 'goreleaserbot', 'imgbotapp', 'backstage-service', 'openssl-machine', 'sizebot', 'dependabot', 'cncf-ci', 'poiana', 'svcbot-qecnsdp', 'nsmbot', 'ti-srebot', 'cf-buildpacks-eng', 'bosh-ci-push-pull', 'gprasath', 'zephyr-github', 'zephyrbot', 'strimzi-ci', 'athenabot', 'k8s-reviewable', 'codecov-io', 'grpc-testing', 'k8s-teamcity-mesosphere', 'angular-builds', 'devstats-sync', 'googlebot', 'hibernate-ci', 'coveralls', 'rktbot', 'coreosbot', 'web-flow', 'prometheus-roobot', 'cncf-bot', 'kernelprbot', 'istio-testing', 'spinnakerbot', 'pikbot', 'spinnaker-release', 'golangcibot', 'opencontrail-ci-admin', 'titanium-octobot', 'asfgit', 'appveyorbot', 'cadvisorjenkinsbot', 'gitcoinbot', 'katacontainersbot', 'prombot', 'prowbot', 'travis%bot', 'k8s-%', '%-bot', '%-robot', 'bot-%', 'robot-%', '%[bot]%', '%[robot]%', '%-jenkins', 'jenkins-%', '%-ci%bot', '%-testing', 'codecov-%', '%clabot%', '%cla-bot%', '%-gerrit', '%-bot-%', '%envoy-filter-example%', '%cibot', '%-ci'])) union select r.repo_group as repo_group, c.sha, lower(c.dup_committer_login) as actor_login, coalesce(aa.company_name, '') as company from gha_repos r, gha_commits c left join gha_actors_affiliations aa on aa.actor_id = c.committer_id and aa.dt_from <= c.dup_created_at and aa.dt_to > c.dup_created_at where c.dup_repo_id = r.id and c.dup_repo_name = r.name and c.committer_id is not null and (c.dup_created_at >= now() - '1 week'::interval) and (lower(c.dup_committer_login) not like all(array['claassistant', 'containersshbuilder', 'wasmcloud-automation', 'fossabot', 'facebook-github-whois-bot-0', 'knative-automation', 'covbot', 'cdk8s-automation', 'github-action-benchmark', 'goreleaserbot', 'imgbotapp', 'backstage-service', 'openssl-machine', 'sizebot', 'dependabot', 'cncf-ci', 'poiana', 'svcbot-qecnsdp', 'nsmbot', 'ti-srebot', 'cf-buildpacks-eng', 'bosh-ci-push-pull', 'gprasath', 'zephyr-github', 'zephyrbot', 'strimzi-ci', 'athenabot', 'k8s-reviewable', 'codecov-io', 'grpc-testing', 'k8s-teamcity-mesosphere', 'angular-builds', 'devstats-sync', 'googlebot', 'hibernate-ci', 'coveralls', 'rktbot', 'coreosbot', 'web-flow', 'prometheus-roobot', 'cncf-bot', 'kernelprbot', 'istio-testing', 'spinnakerbot', 'pikbot', 'spinnaker-release', 'golangcibot', 'opencontrail-ci-admin', 'titanium-octobot', 'asfgit', 'appveyorbot', 'cadvisorjenkinsbot', 'gitcoinbot', 'katacontainersbot', 'prombot', 'prowbot', 'travis%bot', 'k8s-%', '%-bot', '%-robot', 'bot-%', 'robot-%', '%[bot]%', '%[robot]%', '%-jenkins', 'jenkins-%', '%-ci%bot', '%-testing', 'codecov-%', '%clabot%', '%cla-bot%', '%-gerrit', '%-bot-%', '%envoy-filter-example%', '%cibot', '%-ci'])) union select r.repo_group as repo_group, cr.sha, lower(cr.actor_login) as actor_login, coalesce(aa.company_name, '') as company from gha_repos r, gha_commits_roles cr left join gha_actors_affiliations aa on aa.actor_id = cr.actor_id and aa.dt_from <= cr.dup_created_at and aa.dt_to > cr.dup_created_at where cr.dup_repo_id = r.id and cr.dup_repo_name = r.name and cr.actor_id is not null and cr.actor_id != 0 and cr.role = 'Co-authored-by' and (cr.dup_created_at >= now() - '1 week'::interval) and (lower(cr.actor_login) not like all(array['claassistant', 'containersshbuilder', 'wasmcloud-automation', 'fossabot', 'facebook-github-whois-bot-0', 'knative-automation', 'covbot', 'cdk8s-automation', 'github-action-benchmark', 'goreleaserbot', 'imgbotapp', 'backstage-service', 'openssl-machine', 'sizebot', 'dependabot', 'cncf-ci', 'poiana', 'svcbot-qecnsdp', 'nsmbot', 'ti-srebot', 'cf-buildpacks-eng', 'bosh-ci-push-pull', 'gprasath', 'zephyr-github', 'zephyrbot', 'strimzi-ci', 'athenabot', 'k8s-reviewable', 'codecov-io', 'grpc-testing', 'k8s-teamcity-mesosphere', 'angular-builds', 'devstats-sync', 'googlebot', 'hibernate-ci', 'coveralls', 'rktbot', 'coreosbot', 'web-flow', 'prometheus-roobot', 'cncf-bot', 'kernelprbot', 'istio-testing', 'spinnakerbot', 'pikbot', 'spinnaker-release', 'golangcibot', 'opencontrail-ci-admin', 'titanium-octobot', 'asfgit', 'appveyorbot', 'cadvisorjenkinsbot', 'gitcoinbot', 'katacontainersbot', 'prombot', 'prowbot', 'travis%bot', 'k8s-%', '%-bot', '%-robot', 'bot-%', 'robot-%', '%[bot]%', '%[robot]%', '%-jenkins', 'jenkins-%', '%-ci%bot', '%-testing', 'codecov-%', '%clabot%', '%cla-bot%', '%-gerrit', '%-bot-%', '%envoy-filter-example%', '%cibot', '%-ci'])) ), org as ( select row_number() over ( partition by repo_group order by round(hll_cardinality(hll_add_agg(hll_hash_text(sha)))) desc ) as row_number, repo_group, 'commits' as metric, 'org' as tp, company as name, round(hll_cardinality(hll_add_agg(hll_hash_text(sha)))) as cnt from commits_data where company != '' group by repo_group, company union select row_number() over ( partition by r.repo_group order by round(hll_cardinality(hll_add_agg(hll_hash_bigint(e.id)))) desc ) as row_number, r.repo_group, 'contributions' as metric, 'org' as tp, aa.company_name as name, round(hll_cardinality(hll_add_agg(hll_hash_bigint(e.id)))) as cnt from gha_events e, gha_repos r, gha_actors_affiliations aa where r.name = e.dup_repo_name and r.id = e.repo_id and e.actor_id = aa.actor_id and aa.dt_from <= e.created_at and aa.dt_to > e.created_at and e.type in ( 'IssuesEvent', 'PullRequestEvent', 'PushEvent',