Discount Effectiveness by Service

Postgres SQL, as run

with aggregated_data as (
  select case when publisher_name is not null and publisher_name is distinct from invoice_issuer_name then publisher_name else provider_name end as service_provider_name,
         billing_account_id as billing_account_id,
         billing_account_name as billing_account_name,
         billing_currency as billing_currency,
         service_name as service_name,
         sum(effective_cost) as total_effective_cost,
         sum(billed_cost) as total_billed_cost,
         sum(case when charge_category = 'Usage' and billed_cost = 0 and effective_cost <> 0 then 0::numeric else contracted_cost end) as total_contracted_cost,
         sum(case when charge_category = 'Usage' and billed_cost = 0 and effective_cost <> 0 then 0::numeric else list_cost end) as total_list_cost
  from focus.cost_and_usage
  where billing_period_start >= $1::timestamptz
    and billing_period_end < $2::timestamptz
    and charge_class is null
  group by service_provider_name, billing_account_id, billing_account_name, billing_currency, service_name
)
select service_provider_name as service_provider_name,
       billing_account_id as billing_account_id,
       billing_account_name as billing_account_name,
       billing_currency as billing_currency,
       service_name as service_name,
       total_effective_cost as total_effective_cost,
       total_billed_cost as total_billed_cost,
       total_list_cost as total_list_cost,
       (1 - total_contracted_cost / nullif(total_list_cost, 0)) * 100 as contracted_discount,
       (1 - total_effective_cost / nullif(total_list_cost, 0)) * 100 as effective_discount
from aggregated_data
order by total_effective_cost desc nulls last, service_provider_name, billing_account_id, billing_account_name nulls last, billing_currency nulls last, service_name
limit 500

Parameters: $1 = period_start, $2 = period_end_before

Original FOCUS SQL

WITH AggregatedData AS (
  SELECT
    ServiceProviderName,
    BillingAccountId,
    BillingAccountName,
    BillingCurrency,
    ServiceName,
    SUM(EffectiveCost) AS TotalEffectiveCost,
    SUM(BilledCost) AS TotalBilledCost,
    SUM(
      CASE
        WHEN ChargeCategory = 'Usage' AND BilledCost = 0 AND EffectiveCost <> 0
        THEN 0
        ELSE ContractedCost
      END
    ) AS TotalContractedCost,
    SUM(
      CASE
        WHEN ChargeCategory = 'Usage' AND BilledCost = 0 AND EffectiveCost <> 0
        THEN 0
        ELSE ListCost
      END
    ) AS TotalListCost
  FROM focus_data_table
  WHERE
    BillingPeriodStart >= ? AND BillingPeriodEnd < ? AND ChargeClass IS NULL
  GROUP BY
    ServiceProviderName,
    BillingAccountId,
    BillingAccountName,
    BillingCurrency,
    ServiceName
)
SELECT
  ServiceProviderName,
  BillingAccountId,
  BillingAccountName,
  BillingCurrency,
  ServiceName,
  TotalEffectiveCost,
  TotalBilledCost,
  TotalListCost,
  (1 - TotalContractedCost / NULLIF(TotalListCost, 0)) * 100 AS ContractedDiscount,
  (1 - TotalEffectiveCost / NULLIF(TotalListCost, 0)) * 100 AS EffectiveDiscount
FROM AggregatedData