Compute Snowflake cost per user

snowflake_cost_per_user

SELECT
    user_name,
    warehouse_name,
    warehouse_size,
    database_name,
    run_date,
    ROUND(t/1000/3600*credits_per_hour, 2) credits
FROM (
    SELECT
        user_name,
        warehouse_name,
        warehouse_size,
        database_name,
        start_time::DATE run_date,
        SUM(total_elapsed_time) t
    FROM
        snowflake.account_usage.query_history
    WHERE
        start_time >= CURRENT_DATE() -28
        AND start_time < CURRENT_DATE()
        AND warehouse_name IS NOT NULL
        AND warehouse_size IS NOT NULL
    GROUP BY
        user_name,
        warehouse_name,
        warehouse_size,
        database_name,
        run_date
) q
JOIN (
    SELECT
        'X-Small' warehouse_size,
        1 credits_per_hour
    UNION ALL
    SELECT
        'Small' warehouse_size,
        2 credits_per_hour
    UNION ALL
    SELECT
        'Medium'  warehouse_size,
        4 credits_per_hour
    UNION ALL
    SELECT
        'Large' warehouse_size,
        8 credits_per_hour
    UNION ALL
    SELECT
        'X-Large' warehouse_size,
        16 credits_per_hour
    UNION ALL
    SELECT
        '2X-Large' warehouse_size,
        32 credits_per_hour
    UNION ALL
    SELECT
        '3X-Large' warehouse_size,
        64 credits_per_hour
    UNION ALL
    SELECT
        '4X-Large' warehouse_size,
        128 credits_per_hour
    UNION ALL
    SELECT
        '5X-Large' warehouse_size,
        256 credits_per_hour
    UNION ALL
    SELECT
        '6X-Large' warehouse_size,
        512 credits_per_hour
) w
USING (
    warehouse_size
);