Rails model for aggregate data instead of a real table

I have a rails app with a Project model. Each project has usages which is numeric data.

I need to get all the usage for each month and year. I also need to get usage for specific months and years across multiple projects.

Instead of writing one large service file with raw SQL queries to handle this, I have decided to extract a lot of the code to a model which isn’t based on any table, but instead gets the data by using certain SQL aggregation features.

This allows me to take (almost) full advantage of ActiveRecord in consumers to extend the default query to get specific fields (e.g. where the month is May or the project is project #15).

Here is the example of the code I wrote:

class MonthlyUsage < ActiveRecord::Base
  self.table_name = 'projects'

  default_scope do
    select(select_monthly_data_and_dates)
      .group('year', 'month', '"projects"."id"')
  end

  def self.select_monthly_data_and_dates
    <<~SQL.squish
      "projects"."id" AS "project_id",
      SUM("usages"."usage") AS "monthly_usage",
      date_part('year', "usages"."timestamp")::INT AS "year",
      date_part('month', "usages"."timestamp")::INT AS "month"
    SQL
  end
end

In reality there are a few joins and extra steps to this code which I’m omitting here since they aren’t relevant.

Basically I’m of two minds on this. On the one hand I think it allows for powerful querying and fits the definition of a model, but on the other hand I think it might be too "hacky" and not really much clearer than just using a service file (e.g. setting the table name as ‘projects’ even though that’s not really the real table name). That’s why I’m asking for code review here, to get some opinions on whether this is good or bad, as well as how you would do it.

class MonthlyUsageQuery
  def intialize(relation: Project.all)
    @relation = relation
  end

  def all
    relation.select(select_monthly_data_and_dates)
      .group('year', 'month', '"projects"."id"')
  end

  private
  
  attr_reader :relation

  def select_monthly_data_and_dates
    <<~SQL.squish
      "projects"."id" AS "project_id",
      SUM("usages"."usage") AS "monthly_usage",
      date_part('year', "usages"."timestamp")::INT AS "year",
      date_part('month', "usages"."timestamp")::INT AS "month"
    SQL
  end
end

MonthlyUsage.all
MonthlyUsage.new(relation: Project.where(name: 'Stackoverflow')).all