Pricing and billing

WITH engine_costs AS (
  SELECT
    SUM(IF(NOT is_credit, billed_cost, 0)) AS engines_cost,
    SUM(IF(is_credit, billed_cost, 0)) AS engines_credits,
    SUM(consumed_fbu) AS total_fbu
  FROM information_schema.engines_billing
  WHERE DATE_TRUNC('month', usage_date) = DATE_TRUNC('month', CURRENT_DATE)
),
storage_costs AS (
  SELECT
    SUM(IF(NOT is_credit, billed_cost, 0)) AS storage_cost,
    SUM(IF(is_credit, billed_cost, 0)) AS storage_credits,
    SUM(consumed_gib_per_month) AS total_storage_gibpm
  FROM information_schema.storage_billing
  WHERE DATE_TRUNC('month', usage_date) = DATE_TRUNC('month', CURRENT_DATE)
),
SELECT
  engines_cost + storage_cost + engines_credits + storage_credits AS total_cost_usd,
  engines_credits + storage_credits AS total_credits_used_usd,
  engines_cost AS engine_cost_usd,
  storage_cost AS storage_cost_usd,
  total_fbu AS total_engines_fbu,
  (total_storage_gibpm / EXTRACT(DAY FROM DATE_ADD('day', -1, CURRENT_DATE))) AS avg_daily_storage_gibpm
FROM engine_costs, storage_costs;

SELECT 
  usage_date,
  SUM(billed_cost) AS engines_cost,
  SUM(consumed_fbu) AS fbu
FROM information_schema.engines_billing
WHERE DATE_TRUNC('month', usage_date) = DATE_TRUNC('month', CURRENT_DATE)
GROUP BY usage_date
ORDER BY usage_date DESC;

SELECT 
  usage_date,
  SUM(billed_cost) AS storage_cost,
  SUM(consumed_gib_per_month) AS consumed_gibpm
FROM information_schema.storage_billing
WHERE DATE_TRUNC('month', usage_date) = DATE_TRUNC('month', CURRENT_DATE)
GROUP BY usage_date
ORDER BY usage_date DESC;

WITH
  engines_base AS (
    SELECT
      usage_date,
      SUM(IF(is_credit = FALSE, billed_cost, 0)) AS engines_cost,
      SUM(IF(is_credit = TRUE, billed_cost, 0)) AS engines_cost_credit,
      SUM(consumed_fbu) AS fbu
    FROM
      information_schema.engines_billing
    WHERE
      usage_date BETWEEN '2025-05-01' AND '2025-05-31'
    GROUP BY
      usage_date
  ),

  storage_base AS (
    SELECT
      usage_date,
      SUM(IF(is_credit = FALSE, billed_cost, 0)) AS storage_cost,
      SUM(IF(is_credit = TRUE, billed_cost, 0)) AS storage_cost_credit,
      CAST(NULLIF(SUM(consumed_gib_per_month), 0) AS DOUBLE) AS consumed_gibpm
    FROM
      information_schema.storage_billing
    WHERE
      usage_date BETWEEN '2025-05-01' AND '2025-05-31'
    GROUP BY
      usage_date
  )

SELECT
  eb.usage_date,
  sb.storage_cost,
  sb.storage_cost_credit,
  eb.engines_cost,
  eb.engines_cost_credit,
  eb.fbu,
  sb.consumed_gibpm
FROM
  engines_base eb
  INNER JOIN storage_base sb ON eb.usage_date = sb.usage_date
ORDER BY
  eb.usage_date DESC;

WITH
  engines_base AS (
    SELECT
      usage_date,
      SUM(IF(is_credit = FALSE, billed_cost, 0)) AS engines_cost,
      SUM(IF(is_credit = TRUE, billed_cost, 0))  AS engines_cost_credit,
      SUM(consumed_fbu)                          AS fbu
    FROM information_schema.engines_billing
    WHERE usage_date BETWEEN '2025-01-01' AND '2025-05-31'
    GROUP BY usage_date
  ),

  storage_base AS (
    SELECT
      usage_date,
      SUM(IF(is_credit = FALSE, billed_cost, 0)) AS storage_cost,
      SUM(IF(is_credit = TRUE, billed_cost, 0))  AS storage_cost_credit,
      NULLIF(SUM(consumed_gib_per_month), 0)     AS consumed_gibpm
    FROM information_schema.storage_billing
    WHERE usage_date BETWEEN '2025-01-01' AND '2025-05-31'
    GROUP BY usage_date
  ),

  daily_combined AS (
    SELECT
      eb.usage_date,
      sb.storage_cost,
      sb.storage_cost_credit,
      eb.engines_cost,
      eb.engines_cost_credit,
      eb.fbu,
      sb.consumed_gibpm
    FROM engines_base eb
    INNER JOIN storage_base sb ON eb.usage_date = sb.usage_date
  ),

  monthly_agg AS (
    SELECT
      TO_DATE(DATE_TRUNC('month', usage_date))         AS usage_date,
      SUM(storage_cost + engines_cost)                 AS total_cost_usd,
      SUM(storage_cost_credit + engines_cost_credit)   AS total_cost_credit,
      SUM(storage_cost)                                AS storage_cost,
      SUM(storage_cost_credit)                         AS storage_cost_credit,
      SUM(engines_cost)                                AS engines_cost,
      SUM(engines_cost_credit)                         AS engines_cost_credit,
      SUM(fbu)                                         AS fbu,
      AVG(consumed_gibpm)                              AS consumed_gibpm
    FROM daily_combined
    GROUP BY TO_DATE(DATE_TRUNC('month', usage_date))
  )

SELECT *
FROM monthly_agg
ORDER BY usage_date DESC;

SELECT 
  engine_name,
  SUM(billed_cost) AS total_billed_cost_usd,
  SUM(consumed_fbu) AS total_consumed_fbus
FROM 
  information_schema.engines_billing
WHERE 
  usage_date BETWEEN DATE '2025-05-01' AND DATE '2025-05-30'
GROUP BY 
  engine_name
ORDER BY 
  total_billed_cost_usd DESC
LIMIT 10;