class LimitPerGroupQueryBuilder
  class << self
    def query_builder(query:, group_by:, order_by:, limit:)
      t_outer = Arel::Table.new('t_outer')
      window_function = window_function(table: query.arel_table, partition: group_by, order_by: order_by)
      row_number_function = row_number_function(over: window_function)

      inner_query = query
        .arel.dup
        .project(row_number_function)
        .as(t_outer.name)

      select_manager = Arel::SelectManager.new
      select_manager.project(Arel.star).from(inner_query).where(t_outer[:row_number].lteq(limit))
    end

    private

    def window_function(table:, partition:, order_by:)
      window = Arel::Nodes::Window.new
      window.partition(table[partition])
      window.order(query_order(table: table, order_by: order_by))
      window
    end

    def row_number_function(over:)
      Arel::Nodes::NamedFunction.new('row_number', []).over(over).as('row_number')
    end

    def query_order(table:, order_by:)
      if order_by.is_a?(Hash)
        column, order = order_by.first
        order == 'asc' ? Arel::Nodes::Ascending.new(table[column]) : Arel::Nodes::Descending.new(table[column])
      else
        table[order_by]
      end
    end
  end
end

# Showing the 3 most popular posts in those categories
post_query = Post.where(category_id: [1,2,3])
query = LimitPerGroupQueryBuilder.query_builder(query: post_query, group_by: 'category_id', order_by: {rating: 'desc'}, limit: 3)
Post.find_by_sql(query)