plugins (view)

156 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: owner, usesCustomOpenGraphImage, is_tool, created_at (date)

Next page

CREATE VIEW plugins AS select
  repos.name as name,
  repos.full_name as full_name,
  users.login as owner,
  repos.description as description,
  plugin_repos.extra_search as extra_search,
  plugin_repos.tags as tags,
  repos.stargazers_count,
  pypi_versions.name as tag_name,
  max(pypi_releases.upload_time) as latest_release_at,
  repos.created_at as created_at,
  datasette_repos.openGraphImageUrl,
  datasette_repos.usesCustomOpenGraphImage,
  (
    select
      sum(downloads)
    from
      stats
    where
      stats.package = repos.name
      and stats.date > date('now', '-7 days')
  ) as downloads_this_week,
  (
    select
      count(*)
    from
      plugin_repos
    where
      repo = repos.full_name
  ) as is_plugin,
  (
    select
      count(*)
    from
      tool_repos
    where
      repo = repos.full_name
  ) as is_tool
from
  datasette_repos
  join repos on datasette_repos.id = repos.node_id
  left join pypi_releases on (
    pypi_releases.package = repos.name or pypi_releases.package = 'datasette-' || repos.name
  )
  left join pypi_versions on pypi_releases.version = pypi_versions.id
  join users on users.id = repos.owner
  join plugin_repos on plugin_repos.repo = datasette_repos.nameWithOwner
group by
  repos.id
order by
  latest_release_at desc;