Which npm package has the largest version number? with Google BigQuery

0_query.sql

-- Largest single semver component seen for npm packages
WITH parsed AS (
  SELECT
    Name,
    Version,
    SAFE_CAST(REGEXP_EXTRACT(Version, r'^(\d+)') AS INT64) AS major,
    SAFE_CAST(REGEXP_EXTRACT(Version, r'^\d+\.(\d+)') AS INT64) AS minor,
    SAFE_CAST(REGEXP_EXTRACT(Version, r'^\d+\.\d+\.(\d+)') AS INT64) AS patch
  FROM `bigquery-public-data.deps_dev_v1.PackageVersionsLatest`
  WHERE System = 'NPM'
),
semver_only AS (
  SELECT *
  FROM parsed
  WHERE major IS NOT NULL AND minor IS NOT NULL AND patch IS NOT NULL
),
per_pkg AS (
  SELECT
    Name,
    MAX(GREATEST(major, minor, patch)) AS largest_component,
    ARRAY_AGG(
      STRUCT(Version, major, minor, patch)
      ORDER BY GREATEST(major,minor,patch) DESC, major DESC, minor DESC, patch DESC, Version DESC
      LIMIT 1
    )[OFFSET(0)] AS example
  FROM semver_only
  GROUP BY Name
),
semver_counts AS (
  SELECT Name, COUNT(*) AS semver_version_count
  FROM semver_only
  GROUP BY Name
)
SELECT
  p.Name,
  p.largest_component,
  p.example.Version AS example_version
FROM per_pkg p
JOIN semver_counts c USING (Name)
WHERE c.semver_version_count >= p.largest_component
ORDER BY p.largest_component DESC
LIMIT 100;

1_results.csv