Databricks Community

Hubert-Dudek · ‎04-25-2024

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;