join_magic.rb

Project.joins(
  activities: Activity.active.group(:project_id).select(:project_id, 'COUNT(*) as "count"')
).where(activities: { count: [1..2, 3..4] })

join_magic.sql

SELECT *
  FROM projects
  JOIN (SELECT project_id, COUNT(*) as "count"
          FROM activities
         WHERE active = 't'
         GROUP BY project_id
       ) AS activities
    ON activities.project_id = projects.id
 WHERE "activities"."count" BETWEEN 1 AND 2
    OR "activities"."count" BETWEEN 3 AND 4

where_magic.rb

Project.where(
  id: Activity.active.group(:project_id).having("COUNT(*) IN (?)", [1..2, 3..4].flat_map(&to_a)).select(:project_id)
)

where_magic.sql

SELECT "projects".*
  FROM "projects"
 WHERE "projects"."id" IN (
       SELECT "activities"."project_id"
         FROM "activities"
        WHERE "activities"."active" = 't'
        GROUP BY "activities"."project_id"
       HAVING COUNT(*) IN (1,2,3,4)
     )