To generate top_200_popular_packages_20240718.json:
.mode list
.output sources/top_200_popular_packages_20240605.txt
SELECT
lower_id
FROM 'tables/package_scores.json'
ORDER BY popularity_score DESC
LIMIT 200;To generate top_200_popular_flutter_plugins_20240718.json:
.mode list
.output sources/top_200_popular_flutter_plugins_20240605.txt
WITH
flutter_plugins AS (
SELECT DISTINCT
lower_id,
FROM 'tables/package_versions.json'
WHERE
CAST(pubspec->'$.dependencies.flutter.sdk' AS VARCHAR) = '"flutter"'
)
SELECT
p.lower_id
FROM flutter_plugins p
JOIN 'tables/package_scores.json' AS s ON
p.lower_id = s.lower_id
ORDER BY s.popularity_score DESC
LIMIT 200;To generate top_200_popular_android_flutter_plugins_20240605.json:
.mode list
.output sources/top_200_popular_android_flutter_plugins_20240605.txt
WITH android_endorsed_packages AS (
SELECT
lower_id AS platform_agnostic_lower_id,
TRIM(CAST(pubspec->'$.flutter.plugin.platforms.android.default_package' AS VARCHAR), '"') AS platform_android_lower_id
FROM 'tables/package_versions.json'
WHERE
is_latest AND
platform_android_lower_id IS NOT NULL
)
SELECT
platform_android_lower_id AS lower_id,
FROM android_endorsed_packages AS p
JOIN 'tables/package_scores.json' AS s ON
p.platform_agnostic_lower_id = s.lower_id
ORDER BY popularity_score DESC
LIMIT 200;