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;