Join two system tables and get exactly how much USD you are spending.
The short version of the query:
SELECT
u.usage_date,
u.sku_name,
SUM(u.usage_quantity * p.pricing.default) AS total_spent,
p.currency_code
FROM
system.billing.usage u
LEFT JOIN
system.billing.list_prices p
ON
u.sku_name = p.sku_name
AND u.cloud = p.cloud
AND u.usage_start_time < COALESCE(p.price_end_time, date'2029-12-31')
AND u.usage_end_time > p.price_start_time
GROUP BY
ALL
ORDER BY
ALL;
Extended version (which is handling potential price overlap):
SELECT
u.usage_date,
u.sku_name,
SUM(
(UNIX_TIMESTAMP(
LEAST(u.usage_end_time, COALESCE(p.price_end_time, date'2029-12-31'))
) -
UNIX_TIMESTAMP(
GREATEST(u.usage_start_time, p.price_start_time)
))
/ (UNIX_TIMESTAMP(u.usage_end_time) - UNIX_TIMESTAMP(u.usage_start_time))
* u.usage_quantity * p.pricing.default
) AS total_spent,
p.currency_code
FROM
system.billing.usage u
LEFT JOIN
system.billing.list_prices p
ON
u.sku_name = p.sku_name
AND u.cloud = p.cloud
AND u.usage_start_time < COALESCE(p.price_end_time, date'2029-12-31')
AND u.usage_end_time > p.price_start_time
GROUP BY
ALL
ORDER BY
ALL;
