Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-21-2025 06:45 AM
Hi @pranitha
Use this query to get the cluster details along with cost info as well.
WITH hourly_metrics AS (
SELECT
date_trunc('hour', usage_start_time) as hour,
usage_metadata.cluster_id,
sku_name,
MAX(usage_quantity) as max_usage,
ROUND(AVG(usage_quantity), 2) as avg_usage,
SUM(usage_quantity) as DBUs_consumed
FROM system.billing.usage
WHERE usage_start_time BETWEEN '2025-01-01' AND '2025-01-21'
GROUP BY
date_trunc('hour', usage_start_time),
usage_metadata.cluster_id,
sku_name
),
cost_calculation AS (
SELECT
h.*,
l.pricing.default AS unitprice,
ROUND((h.DBUs_consumed * l.pricing.default)) AS total_cost
FROM hourly_metrics h
LEFT OUTER JOIN system.billing.list_prices l ON l.sku_name = h.sku_name
)
SELECT
cluster_id,
sku_name,
hour,
max_usage,
avg_usage,
DBUs_consumed,
unitprice,
total_cost
FROM cost_calculation
ORDER BY hour DESC, total_cost DESC;
