profile_project.rb

class ProfileProject < ApplicationRecord
  acts_as_votable

  scope :join_votes, (lambda do
    joins(sanitize_sql_array([%(
        LEFT JOIN votes ON votes.votable_id = profile_projects.id
                        AND votes.votable_type = 'ProfileProject'
      )
    ]))
  end)

  scope :blazing_projects, (lambda do
    join_votes.group('profile_projects.id').select(
      <<~CODE
        DISTINCT profile_projects.*,
        coalesce(sum(
          CASE
          WHEN (votes.created_at >= current_date - interval '7' day)
            THEN (votes.vote_weight * 6)
          WHEN (votes.created_at >= current_date - interval '14' day)
            AND (votes.created_at < current_date - interval '7' day)
            THEN (votes.vote_weight * 5)
          WHEN (votes.created_at >= current_date - interval '21' day)
            AND (votes.created_at < current_date - interval '14' day)
            THEN (votes.vote_weight * 4)
          WHEN (votes.created_at >= current_date - interval '28' day)
            AND (votes.created_at < current_date - interval '21' day)
            THEN (votes.vote_weight * 3)
          WHEN (votes.created_at >= current_date - interval '35' day)
            AND (votes.created_at < current_date - interval '28' day)
            THEN (votes.vote_weight * 2)
          WHEN (votes.created_at < current_date - interval '35' day)
            THEN (votes.vote_weight * 1)
          END
        )) as total_votes
      CODE
    ).order('total_votes desc nulls last')
  end)
end

profile_project2.rb

class ProfileProject < ApplicationRecord
  acts_as_votable

  scope :join_recent_votes, (lambda do
    joins(sanitize_sql_array([%(
        LEFT JOIN votes ON votes.votable_id = profile_projects.id
                        AND votes.votable_type = 'ProfileProject'
                        AND votes.created_at > :a_week_ago
      ),
      a_week_ago: 7.days.ago
    ]))
  end)

  scope :blazing_projects, (lambda do
    join_recent_votes.group('profile_projects.id')
                     .select('DISTINCT profile_projects.*, coalesce(sum(votes.vote_weight)) as total_votes')
                     .order('total_votes desc nulls last')
  end)
end