plugins (view)

53 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: stargazers_count, tag_name, openGraphImageUrl, usesCustomOpenGraphImage, is_tool, latest_release_at (date), created_at (date)

CREATE VIEW plugins AS select
  repos.name as name,
  repos.full_name as full_name,
  repos.description as description,
  repos.stargazers_count,
  releases.tag_name,
  max(releases.created_at) 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
      json_each(datasette_repos.topics)
    where
      value = 'datasette-plugin'
  ) as is_plugin,
  (
    select
      count(*)
    from
      json_each(datasette_repos.topics)
    where
      value = 'datasette-tool'
  ) as is_tool
from
  datasette_repos
  join repos on datasette_repos.id = repos.node_id
  join releases on repos.id = releases.repo
where
  datasette_repos.rowid in (
    select
      datasette_repos.rowid
    from
      datasette_repos,
      json_each(datasette_repos.topics) j
    where
      j.value = 'datasette-plugin'
  )
group by
  repos.id
order by
  latest_release_at desc;