CODEWITHSUNDEEP
sqlruby-on-rails
robodisco
robodisco

Reputation: 4272

How to group records in ranges of their created_at age in Rails

I need to create a graph to show how long it took to close various deals. The time it takes to close a deal is calculated by the deal's created_at and closed_at timestamps.

I want to group deals into two time ranges: deals which took from 0 to 30 days to close, and deals which took from 31 days to 60 days to close.

I.e. I want a hash like this {"1-30" => 5, "31-60" => 10} where the values are the number of deals of that age range.

Since my model does not have a days_old column, I need to use Postgres to calculate this when i do the grouping.

I've tried the following but I'm getting the error below. Can anyone point out where I am going wrong? I'm sure the CASE syntax is correct.

sql = <<~SQL
  count(*),
  CASE
    WHEN ABS( DATE_PART('day', created_at - closed_at) ) BETWEEN 0 AND 30 THEN '0-30'
    WHEN ABS( DATE_PART('day', created_at - closed_at) ) BETWEEN 31 AND 60 THEN '31-60'
  END AS day_range
SQL

Deal.select(sql).group(:day_range).count

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "AS"
LINE 6: END AS day_range

Upvotes: 1

Views: 500

Answers (1)

Fangxing
Fangxing

Reputation: 6125

You don't need count in the end, you query should like this

Deal.select(sql).group(:day_range)

see the example result in here

Upvotes: 1

Related Questions