group models by range of values

The task is to group calls with durations:

• up to one minute
• up to two minutes
• up to five minutes
• more than five minutes

Consider the following records with various durations

# duration is in seconds
2.times { Call.create(duration: rand(10..60)) }
3.times { Call.create(duration: rand(65..120)) }
4.times { Call.create(duration: rand(130..300)) }
5.times { Call.create(duration: rand(300..3000)) }

We can send such SQL to #group to get this done

def duration_in_minutes_ranges_sql
  <<~SQL
    CASE
      WHEN duration BETWEEN 0 AND 60 THEN 'up_to_one'
      WHEN duration BETWEEN 61 AND 120 THEN 'up_to_two'
      WHEN duration BETWEEN 121 AND 300 THEN 'up_to_five'
      WHEN duration > 300 THEN 'over_five'
    END
  SQL
end

Call.group(duration_in_minutes_ranges_sql).count
=> {
  "up_to_one"=>2,
  "up_to_two"=>3,
  "up_to_five"=>4
  "over_five"=>5,
}

versions: rails: 6.0.0.rc1 pg: 1.1.4 psql: 11.3

Tweet