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.

Example below which shows number of repositories I have created over time
Cumulative stars for a GitHub repository over time

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, updated 2021-12-24T15:20:40-08:00 · History · Edit

Cumulative total over time in SQL

Related