What does each Snowflake warehouse cost you a month?

-- Your Snowflake warehouses as cost calculator inputs, one row each: size,
-- generation, AUTO_SUSPEND, last-30-day credits, the share of them that was idle,
-- and the active hours and resumes a day the calculator takes.
-- Reads metadata only. Needs a role that can read SNOWFLAKE.ACCOUNT_USAGE, which
-- lags by up to three hours. In Snowsight use Run All: the SELECT reads the
-- result of SHOW WAREHOUSES through LAST_QUERY_ID(), so nothing may run between them.
SHOW WAREHOUSES;

WITH wh AS (
  SELECT
    "name" AS warehouse_name,
    "size" AS size,
    CASE
      WHEN UPPER(REPLACE("size", '-', '')) IN ('XSMALL') THEN 'XS'
      WHEN UPPER(REPLACE("size", '-', '')) IN ('SMALL') THEN 'S'
      WHEN UPPER(REPLACE("size", '-', '')) IN ('MEDIUM') THEN 'M'
      WHEN UPPER(REPLACE("size", '-', '')) IN ('LARGE') THEN 'L'
      WHEN UPPER(REPLACE("size", '-', '')) IN ('XLARGE') THEN 'XL'
      WHEN UPPER(REPLACE("size", '-', '')) IN ('2XLARGE', 'XXLARGE', 'X2LARGE') THEN '2XL'
      WHEN UPPER(REPLACE("size", '-', '')) IN ('3XLARGE', 'XXXLARGE', 'X3LARGE') THEN '3XL'
      WHEN UPPER(REPLACE("size", '-', '')) IN ('4XLARGE', 'X4LARGE') THEN '4XL'
    END AS size_code,
    CASE
      WHEN UPPER(REPLACE("size", '-', '')) IN ('XSMALL') THEN 1
      WHEN UPPER(REPLACE("size", '-', '')) IN ('SMALL') THEN 2
      WHEN UPPER(REPLACE("size", '-', '')) IN ('MEDIUM') THEN 4
      WHEN UPPER(REPLACE("size", '-', '')) IN ('LARGE') THEN 8
      WHEN UPPER(REPLACE("size", '-', '')) IN ('XLARGE') THEN 16
      WHEN UPPER(REPLACE("size", '-', '')) IN ('2XLARGE', 'XXLARGE', 'X2LARGE') THEN 32
      WHEN UPPER(REPLACE("size", '-', '')) IN ('3XLARGE', 'XXXLARGE', 'X3LARGE') THEN 64
      WHEN UPPER(REPLACE("size", '-', '')) IN ('4XLARGE', 'X4LARGE') THEN 128
    END
      * IFF(TO_VARCHAR("generation") = '2' OR "resource_constraint" = 'STANDARD_GEN_2', 1.35, 1) AS credits_per_hour,
    IFF(TO_VARCHAR("generation") = '2' OR "resource_constraint" = 'STANDARD_GEN_2', 'gen2', 'gen1') AS gen,
    -- 0 or NULL means the warehouse never suspends itself.
    COALESCE("auto_suspend", 0) AS auto_suspend,
    "max_cluster_count" AS max_clusters
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
  -- Standard warehouses only: Snowpark-optimized ones bill at other rates.
  WHERE "type" = 'STANDARD'
),
metering AS (
  SELECT
    warehouse_name,
    SUM(credits_used_compute) AS compute_credits,
    -- Credits spent running queries, which leaves out idle time.
    SUM(credits_attributed_compute_queries) AS query_credits,
    COUNT(DISTINCT IFF(credits_used_compute > 0, TO_DATE(start_time), NULL)) AS active_days
  FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
  WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
  GROUP BY warehouse_name
),
resumes AS (
  -- One row per warehouse resume, automatic or manual. Cluster-level events
  -- carry a cluster number, so they are not counted twice.
  SELECT warehouse_name, COUNT(*) AS resumes
  FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_EVENTS_HISTORY
  WHERE "TIMESTAMP" >= DATEADD(day, -30, CURRENT_TIMESTAMP())
    AND event_state = 'STARTED'
    AND event_reason IN ('WAREHOUSE_AUTORESUME', 'WAREHOUSE_RESUME')
    AND cluster_number IS NULL
  GROUP BY warehouse_name
),
inputs AS (
  SELECT
    wh.*,
    m.compute_credits,
    m.compute_credits - COALESCE(m.query_credits, m.compute_credits) AS idle_credits,
    -- The calculator takes 1 to 31 days a month and 0 to 24 hours a day.
    LEAST(GREATEST(m.active_days, 1), 31) AS days,
    -- Query time in cluster-hours, so a multi-cluster warehouse goes in as one cluster.
    LEAST(COALESCE(m.query_credits, m.compute_credits) / wh.credits_per_hour / LEAST(GREATEST(m.active_days, 1), 31), 24) AS hours,
    COALESCE(r.resumes, 0) / LEAST(GREATEST(m.active_days, 1), 31) AS resumes_per_day
  FROM wh
  JOIN metering AS m ON m.warehouse_name = wh.warehouse_name
  LEFT JOIN resumes AS r ON r.warehouse_name = wh.warehouse_name
  -- 5XL and 6XL are left out: the calculator does not price them.
  WHERE wh.size_code IS NOT NULL AND m.compute_credits > 0
),
wh_rows AS (
  SELECT
    *,
    '&wh=' || REGEXP_REPLACE(warehouse_name, '[^A-Za-z0-9_$]', '_')
      || '&size=' || size_code
      || '&gen=' || gen
      || '&clusters=1'
      || '&hours=' || TO_VARCHAR(hours::NUMBER(38, 2))
      || '&resumes=' || TO_VARCHAR(resumes_per_day::NUMBER(38, 1))
      || '&suspend=' || TO_VARCHAR(LEAST(auto_suspend, 86400))
      || '&days=' || TO_VARCHAR(days) AS params,
    ROW_NUMBER() OVER (ORDER BY compute_credits DESC) AS rn
  FROM inputs
),
-- The five warehouses that used the most credits, in one link.
top5 AS (
  SELECT 'https://lp-data.cloud/tools/snowflake-warehouse-cost-calculator#' || SUBSTR(LISTAGG(params, '') WITHIN GROUP (ORDER BY rn), 2)
    || '&via=sql-snowflake-calc' AS url
  FROM wh_rows
  WHERE rn <= 5
)
SELECT
  warehouse_name,
  size,
  gen,
  auto_suspend AS auto_suspend_seconds,
  max_clusters,
  ROUND(compute_credits, 1) AS compute_credits_30d,
  ROUND(idle_credits, 1) AS idle_credits_30d,
  ROUND(DIV0(idle_credits, compute_credits) * 100) AS idle_pct,
  days AS active_days,
  ROUND(hours, 2) AS query_hours_per_active_day,
  ROUND(resumes_per_day, 1) AS resumes_per_active_day,
  'https://lp-data.cloud/tools/snowflake-warehouse-cost-calculator#' || SUBSTR(params, 2) || '&via=sql-snowflake-calc' AS calculator_url,
  top5.url AS top5_calculator_url
FROM wh_rows CROSS JOIN top5
ORDER BY rn;