issue_comments: 660551397

This data as json

html_url issue_url id node_id user created_at updated_at author_association body reactions issue performed_via_github_app
https://github.com/dogsheep/github-to-sqlite/issues/43#issuecomment-660551397 https://api.github.com/repos/dogsheep/github-to-sqlite/issues/43 660551397 MDEyOklzc3VlQ29tbWVudDY2MDU1MTM5Nw== 9599 2020-07-18T22:27:32Z 2020-07-18T23:05:45Z MEMBER
with most_recent_releases as (
  with ranked as (
    select
      repo,
      tag_name,
      published_at,
      row_number() OVER (
        partition BY repo
        ORDER BY
          published_at DESC
      ) rank
    FROM
      releases
  )
  select
    *
  from
    ranked
  where
    rank = 1
)
select
  repos.full_name as repo,
  most_recent_releases.tag_name as release,
  commits.committer_date as release_commit_date,
  (
    select
      count(*)
    from
      commits c2
    where
      c2.repo = repos.id
      and c2.committer_date > commits.committer_date
  ) as commits_since_release,
  'https://github.com/' || repos.full_name || '/compare/' || most_recent_releases.tag_name || '...' || repos.default_branch as view_commits
from
  most_recent_releases
  join repos on most_recent_releases.repo = repos.id
  join tags on tags.repo = repos.id
  and tags.name = most_recent_releases.tag_name
  join commits on tags.sha = commits.sha
order by
  commits_since_release desc
<table> <thead> <tr> <th>repo</th> <th>release</th> <th>release_commit_date</th> <th>commits_since_release</th> <th>view_commits</th> </tr> </thead> <tbody> <tr> <td>simonw/datasette</td> <td>0.45</td> <td>2020-07-01T21:43:07Z</td> <td>9</td> <td>https://github.com/simonw/datasette/compare/0.45...master</td> </tr> <tr> <td>dogsheep/twitter-to-sqlite</td> <td>0.21.1</td> <td>2020-04-30T18:20:43Z</td> <td>2</td> <td>https://github.com/dogsheep/twitter-to-sqlite/compare/0.21.1...master</td> </tr> <tr> <td>dogsheep/github-to-sqlite</td> <td>2.3</td> <td>2020-07-09T23:26:34Z</td> <td>2</td> <td>https://github.com/dogsheep/github-to-sqlite/compare/2.3...master</td> </tr> <tr> <td>dogsheep/dogsheep-photos</td> <td>0.4.1</td> <td>2020-05-25T20:11:20Z</td> <td>2</td> <td>https://github.com/dogsheep/dogsheep-photos/compare/0.4.1...master</td> </tr> <tr> <td>dogsheep/swarm-to-sqlite</td> <td>0.3.1</td> <td>2020-03-28T02:29:41Z</td> <td>1</td> <td>https://github.com/dogsheep/swarm-to-sqlite/compare/0.3.1...master</td> </tr> <tr> <td>dogsheep/hacker-news-to-sqlite</td> <td>0.3.1</td> <td>2020-03-21T22:39:34Z</td> <td>1</td> <td>https://github.com/dogsheep/hacker-news-to-sqlite/compare/0.3.1...master</td> </tr> <tr> <td>simonw/sqlite-utils</td> <td>2.11</td> <td>2020-07-08T17:36:07Z</td> <td>0</td> <td>https://github.com/simonw/sqlite-utils/compare/2.11...master</td> </tr> <tr> <td>dogsheep/healthkit-to-sqlite</td> <td>0.5</td> <td>2020-03-28T01:50:51Z</td> <td>0</td> <td>https://github.com/dogsheep/healthkit-to-sqlite/compare/0.5...master</td> </tr> <tr> <td>dogsheep/inaturalist-to-sqlite</td> <td>0.2</td> <td>2020-03-24T00:35:44Z</td> <td>0</td> <td>https://github.com/dogsheep/inaturalist-to-sqlite/compare/0.2...master</td> </tr> <tr> <td>dogsheep/genome-to-sqlite</td> <td>0.1</td> <td>2019-09-19T15:38:10Z</td> <td>0</td> <td>https://github.com/dogsheep/genome-to-sqlite/compare/0.1...master</td> </tr> <tr> <td>dogsheep/pocket-to-sqlite</td> <td>0.2</td> <td>2020-03-27T22:23:16Z</td> <td>0</td> <td>https://github.com/dogsheep/pocket-to-sqlite/compare/0.2...master</td> </tr> </tbody> </table>

https://github-to-sqlite.dogsheep.net/github?sql=with+most_recent_releases+as+%28%0D%0A++with+ranked+as+%28%0D%0A++++select%0D%0A++++++repo%2C%0D%0A++++++tag_name%2C%0D%0A++++++published_at%2C%0D%0A++++++row_number%28%29+OVER+%28%0D%0A++++++++partition+BY+repo%0D%0A++++++++ORDER+BY%0D%0A++++++++++published_at+DESC%0D%0A++++++%29+rank%0D%0A++++FROM%0D%0A++++++releases%0D%0A++%29%0D%0A++select%0D%0A++++*%0D%0A++from%0D%0A++++ranked%0D%0A++where%0D%0A++++rank+%3D+1%0D%0A%29%0D%0Aselect%0D%0A++repos.full_name+as+repo%2C%0D%0A++most_recent_releases.tag_name+as+release%2C%0D%0A++commits.committer_date+as+release_commit_date%2C%0D%0A++%28%0D%0A++++select%0D%0A++++++count%28*%29%0D%0A++++from%0D%0A++++++commits+c2%0D%0A++++where%0D%0A++++++c2.repo+%3D+repos.id%0D%0A++++++and+c2.committer_date+%3E+commits.committer_date%0D%0A++%29+as+commits_since_release%2C%0D%0A++%27https%3A%2F%2Fgithub.com%2F%27+%7C%7C+repos.full_name+%7C%7C+%27%2Fcompare%2F%27+%7C%7C+most_recent_releases.tag_name+%7C%7C+%27...%27+%7C%7C+repos.default_branch+as+view_commits%0D%0Afrom%0D%0A++most_recent_releases%0D%0A++join+repos+on+most_recent_releases.repo+%3D+repos.id%0D%0A++join+tags+on+tags.repo+%3D+repos.id%0D%0A++and+tags.name+%3D+most_recent_releases.tag_name%0D%0A++join+commits+on+tags.sha+%3D+commits.sha%0D%0Aorder+by%0D%0A++commits_since_release+desc

{
    "total_count": 0,
    "+1": 0,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
660355904  
Powered by Datasette · Query took 1.408ms · About: github-to-sqlite