tools (view)

40 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: owner, name:1, usesCustomOpenGraphImage, is_plugin, latest_release_at (date), created_at (date)

CREATE VIEW tools AS select
  repos.name as name,
  repos.full_name as full_name,
  users.login as owner,
  repos.description as description,
  repos.stargazers_count,
  pypi_versions.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
  left join pypi_versions on pypi_releases.version = pypi_versions.id
  join users on users.id = repos.owner
where
  datasette_repos.nameWithOwner in (
    select
      repo
    from
      tool_repos
  )
group by
  repos.id
order by
  latest_release_at desc;