Progression of records number by time

Question

Let's say I have three users created on different dates. Now I want do have a graph of users progression. So I want to get something like:

{
  Thu, 02 Nov 2017=>1,
  Sat, 04 Feb 2017=>2,
  Wed, 21 Mar 2018=>3
}

It's very similar to grouping by created_at::date, but I want to have number of all the records created before this date rather than number of items created exactly on this date.

How can I achieve this using group_by and aggregate functions in Postgresql? I need it for Ruby on Rails project, but I expect simple vanilla SQL, no maps and complex queries.

I guess this could be useful: https://stackoverflow.com/a/51143287/5239030 — iGian, Jul 19 '18 at 12:54
Not really, I need SQL, this involves Ruby and Hash. I have a yml file with configuration that includes sql. It calls where, then group_by, then order_by and aggregate function. I want to follow this conventions, so I expect rather custom aggregate sql function than ruby code. — ciemborowicz, Jul 19 '18 at 13:05
Well, can you show the code you tried so far and where you are getting stuck? Anyway, in GroupDate source you could get some inspiration: https://github.com/ankane/groupdate — iGian, Jul 19 '18 at 14:39

Maxence · Answer 1 · 2018-07-25T00:56:31.497

I am no rails expert and my solution requires loading a big relationship like:

@u = User.all

And then find the smallest creation date :

@start = @u.minimum("created_at")

Then you can calculate the number of days between creation and now:

@days = ((Time.now - @start)/1.day).to_i

Then you can calculate for each day the number of users already created:

@days.each do |day|
puts "day "+day.to_s
puts @u.where("created_at < ?", @start+day.day).count
end

Probably some easier solutions in SQL though. (Also you may take only a subset of users by choosing a range of dates not to exceed the server memory)

Progression of records number by time

1 Answers1