Cumulative total over time in SQL

This is a quick trick for creating a cumulative chart of the total number of items created over time based just on their creation date.

Try it here

select
  created_at,
  (
    select
      count(*)
    from
      repos repos2
    where
      repos2.owner = 9599
      and repos2.created_at <= repos.created_at
  ) as cumulative
from
  repos
where
  "owner" = 9599
order by
  created_at desc

I imagine there's a more elegant way to do this using a window function but this works fine.

Created 2021-09-13T08:33:52-07:00 · Edit