lifted from https://github.com/restarone/violet_rails/blob/master/app/models/ahoy/event.rb

event.rb

class Ahoy::Event < ApplicationRecord
  
  scope :with_label_grouped_data , -> {
    # Build a subquery SQL
    subquery = self.unscoped.select("(case when #{table_name}.properties->>'label' is not NULL then #{table_name}.properties->>'label' else #{table_name}.name end) as label, #{table_name}.id").to_sql

    # join the subquery to base model and returns the grouped data as Hash
    self
      .joins("INNER JOIN (#{subquery}) as labelled_events ON labelled_events.id = #{table_name}.id")
      .group(:label)
      .pluck(
        :label,
        Arel.sql("jsonb_build_object('count', COUNT(#{table_name}.id), 'name', jsonb_array_element(jsonb_agg(#{table_name}.name), 0))")
      )
      .to_h
      .with_indifferent_access
  }

  scope :with_api_resource , -> {
    # Build a subquery SQL
    subquery = self
                .unscoped
                .joins("INNER JOIN #{ApiResource.table_name} ON ahoy_events.properties->>'resource_id' IS NOT NULL AND (ahoy_events.properties ->> 'resource_id')::int = #{ApiResource.table_name}.id")
                .select(
                  "(#{self.table_name}.properties ->> 'resource_id')::int AS resource_id",
                  "#{self.table_name}.id",
                  "#{ApiResource.table_name}.api_namespace_id AS namespace_id",
                  "(#{self.table_name}.properties ->> 'watch_time')::bigint AS watch_time",
                  "round((#{self.table_name}.properties->>'total_duration')::numeric, 3) AS total_duration",
                  "CASE WHEN (#{self.table_name}.properties ->> 'video_start')::boolean THEN 1 ELSE 0 END AS is_viewed",
                  )
                  .to_sql

    # join the subquery to base model
    joins("INNER JOIN (#{subquery}) as api_resourced_events ON api_resourced_events.id = #{table_name}.id")
  }

  scope :filter_records_with_video_details_missing, -> {
    # The watch_time and total_duration maybe zero.
    # So, neglecting such records as well to prevent: "DivisionByZero error"
    self
      .where(
        "NULLIF((properties ->> 'watch_time')::float, 0.0) IS NOT NULL"\
        " AND NULLIF((properties ->> 'total_duration')::float, 0.0) IS NOT NULL"\
        " AND (properties ->> 'video_start') IS NOT NULL"\
        " AND (properties ->> 'resource_id') IS NOT NULL"
      )
  }
end