Problem:

You need to count rows created in a model and group them according to date ranges. Such range-grouping can be done in Ruby, but in this scenario the ranges are too numerous i.e. doing this in Ruby would be very slow.

Solution:

A mixture of raw SQL CASE statements and ActiveRecord’s methods.

# ruby code

# ranges must be ordered
ranges = [
  date_range1,
  date_range2
]

group_sub_str = \
  ranges.map.with_index do |range, i|
    "WHEN created_at BETWEEN '#{range.min}' AND '#{range.max}' THEN 'range#{i + 1}'"
  end.join(' ')

group_str = "CASE #{group_sub_str} END"

complete_range = ranges.first.min..ranges.last.max # which is why we kept our array ordered

ModelName.where(created_at: complete_range).group(group_str).count(:*)

That code will produce a hash with the keys being ‘range1’, ‘range2’, etc. and the values being corresponding counts.