scorecard criticality score

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