top_25.sql

WITH RECURSIVE transitive_dependencies AS (
    SELECT package_id AS dependency_id, package_id AS root_id
    FROM dependencies
    WHERE kind = 'runtime'
  UNION ALL
    SELECT d.package_id, td.root_id
    FROM dependencies d
    JOIN transitive_dependencies td ON td.dependency_id = d.package_id AND td.dependency_id <> td.root_id  -- Avoid self-joins
    WHERE d.kind = 'runtime'
),

dependency_counts AS (
    SELECT root_id, COUNT(*) AS dependency_count
    FROM transitive_dependencies
    GROUP BY root_id
)

SELECT p.name, dc.dependency_count
FROM dependency_counts dc
JOIN packages p ON p.id = dc.root_id
ORDER BY dc.dependency_count DESC
LIMIT 25;