Skip to content

Instantly share code, notes, and snippets.

@naveensrinivasan

naveensrinivasan/top1000-2022.sql

Created December 27, 2022 01:22
Show Gist options
  • Save naveensrinivasan/e26ebb2f1ec95f8f0d027992535bf72e to your computer and use it in GitHub Desktop.
Save naveensrinivasan/e26ebb2f1ec95f8f0d027992535bf72e to your computer and use it in GitHub Desktop.
Download ZIP
scorecard criticality score
Raw
top1000-2022.sql
WITH top_repos AS (
SELECT
REGEXP_REPLACE(repo.url, '^https://', '') as repo_name
FROM
`openssf.criticality_score_cron.criticality-score-v0`
WHERE
collection_date = (
SELECT
MAX(collection_date)
FROM
`openssf.criticality_score_cron.criticality-score-v0`)
ORDER BY
default_score DESC
LIMIT
1000
)
SELECT
date,
c.name,
AVG(c.score) as avg_score,
STDDEV(c.score) as stddev_score
FROM
`openssf.scorecardcron.scorecard-v2`,
UNNEST(checks) AS c
WHERE
repo.name IN (SELECT repo_name FROM top_repos)
and c.name in ('Branch-Protection','Maintained','Dangerous-Workflow','Code-Review')
and date BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY
date, c.name
order by date
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment