app/services/db/monthly_sp_auth_count/unique_monthly_auth_counts_by_iaa.rb
# Aggregates a user metric at across issuers within the same IAA, during that IAA's
# period of performance (between its start and end date), month-over-month, by IAL level
# @param [String] iaa
# @param [Symbol] aggregate (one of :sum, :unique, :new_unique)
# @return [PG::Result, Array]
Aggregates a user metric at across issuers within the same IAA, during that IAA's
period of performance (between its start and end date), month-over-month, by IAL level
Aggregates a user metric at across issuers within the same IAA during that IAA ' s period of performance ( between its start and end date ) month - over - month by IAL level
def call(iaa:, aggregate:)
        date_range, issuers = iaa_parts(iaa)
        return [] if !date_range || !issuers
        full_months, partial_months = Reports::MonthHelper.months(date_range).
          partition do |month_range|
            Reports::MonthHelper.full_month?(month_range)
          end
        subquery = [
          full_month_subquery(issuers: issuers, full_months: full_months),
          *partial_month_subqueries(issuers: issuers, partial_months: partial_months),
        ].join(' UNION ALL ')
        select_clause = case aggregate
        when :sum
          <<~SQL
            SUM(billing_month_logs.auth_count)::bigint AS total_auth_count
          SQL
        when :unique
          <<~SQL
            COUNT(DISTINCT billing_month_logs.user_id) AS unique_users
          SQL
        when :new_unique
          <<~SQL
            COUNT(DISTINCT billing_month_logs.user_id) AS new_unique_users
          SQL
        else
          raise "unknown aggregate=#{aggregate}"
        end
        where_clause = case aggregate
        when :new_unique
          <<~SQL
            NOT EXISTS (
              SELECT 1
              FROM subquery lookback_logs
              WHERE
                  lookback_logs.user_id = billing_month_logs.user_id
              AND lookback_logs.ial = billing_month_logs.ial
              AND lookback_logs.year_month < billing_month_logs.year_month
            )
          SQL
        else
          'TRUE'
        end
        params = {
          iaa_start_date: quote(date_range.begin),
          iaa_end_date: quote(date_range.end),
          iaa: quote(iaa),
          subquery: subquery,
          select_clause: select_clause,
          where_clause: where_clause,
        }
        sql = format(<<~SQL, params)
          WITH subquery AS (%{subquery})
          SELECT
            billing_month_logs.year_month
          , billing_month_logs.ial
          , %{iaa} AS iaa
          , %{iaa_start_date} AS iaa_start_date
          , %{iaa_end_date} AS iaa_end_date
          , %{select_clause}
          FROM
            subquery billing_month_logs
          WHERE
            %{where_clause}
          GROUP BY
            billing_month_logs.year_month
          , billing_month_logs.ial
        SQL
        ActiveRecord::Base.connection.execute(sql)
      end
def call ( iaa : , aggregate : ) date_range , issuers = iaa_parts ( iaa ) return [ ] if ! date_range || ! issuers full_months , partial_months = Reports :: MonthHelper . months ( date_range ) . partition do | month_range | Reports :: MonthHelper . full_month? ( month_range ) end subquery = [ full_month_subquery ( issuers : issuers , full_months : full_months ) , * partial_month_subqueries ( issuers : issuers , partial_months : partial_months ) , ] . join ( ' UNION ALL ' ) select_clause = case aggregate when :sum <<~SQL 
            SUM(billing_month_logs.auth_count)::bigint AS total_auth_count
           SQL when :unique <<~SQL 
            COUNT(DISTINCT billing_month_logs.user_id) AS unique_users
           SQL when :new_unique <<~SQL 
            COUNT(DISTINCT billing_month_logs.user_id) AS new_unique_users
           SQL else raise "unknown aggregate=#{aggregate}" end where_clause = case aggregate when :new_unique <<~SQL 
            NOT EXISTS (
              SELECT 1
              FROM subquery lookback_logs
              WHERE
                  lookback_logs.user_id = billing_month_logs.user_id
              AND lookback_logs.ial = billing_month_logs.ial
              AND lookback_logs.year_month < billing_month_logs.year_month
            )
           SQL else 'TRUE' end params = { iaa_start_date : quote ( date_range . begin ) , iaa_end_date : quote ( date_range . end ) , iaa : quote ( iaa ) , subquery : subquery , select_clause : select_clause , where_clause : where_clause , } sql = format ( <<~SQL , params ) 
          WITH subquery AS (%{subquery})
          SELECT
            billing_month_logs.year_month
          , billing_month_logs.ial
          , %{iaa} AS iaa
          , %{iaa_start_date} AS iaa_start_date
          , %{iaa_end_date} AS iaa_end_date
          , %{select_clause}
          FROM
            subquery billing_month_logs
          WHERE
            %{where_clause}
          GROUP BY
            billing_month_logs.year_month
          , billing_month_logs.ial
         SQL ActiveRecord :: Base . connection . execute ( sql ) end
Aggregates a user metric at across issuers within the same IAA, during that IAA's
period of performance (between its start and end date), month-over-month, by IAL level
Aggregates a user metric at across issuers within the same IAA during that IAA ' s period of performance ( between its start and end date ) month - over - month by IAL level
# The subqueries create a uniform representation of data: # - full months from monthly_sp_auth_counts # - partial months by aggregating sp_return_logs # The results are rows with [user_id, ial, auth_count, year_month] # "new unique users" are users that we are seeing for the first # time this month, so this filters out users we have seen in a past # month by joining the subquery against itself
None
PG::Result, Array
iaa
<not_specific>
aggregate
<not_specific>
<not_specific>
aggregate (one of :sum, :unique, :new_unique)
aggregate ( one of : sum : unique : new_unique )