How to make raw SQL query chainable using Scope? -Rails

It's unlikely you'll be able to make that kind of query chainable with ActiveRecord. But you can certainly make a scope for the query in Rails:

scope :my_scope, -> {
  self.connection.execute("
    select '{Critical: '||"Critical"||', Major: '||"Major"||', Moderate: 
    '||"Moderate"||', Minor: '||"Minor"||', Clear: '||"Clear"||'}' as result
    from (
        select
          sum(case when max_weight=1   THEN 1 else 0 End ) as "Clear",
          sum(case when max_weight=2   THEN 1 else 0 End ) as "Minor",
          sum(case when max_weight=3   THEN 1 else 0 End ) as "Moderate",
          sum(case when max_weight=22  THEN 1 else 0 End ) as "Major",
          sum(case when max_weight=160 THEN 1 else 0 End ) as "Critical",
          count(*) as number_of_answers
        from (
          select 
            fua.forms_user_id, 
            max(fa.weight) max_weight
          from 
            public.forms_user_answers fua
            join public.forms_users fu on (fua.forms_user_id  = fu.id)
            left join public.form_answers fa on (fua.form_answer_id = fa.id)
          where 
            fu.form_id = #{form.id}
          group by 
            fua.forms_user_id
        ) subq
    ) subq1
  ")
}