SELECT

DATE_TRUNC('month', TIMESTAMP) AS month,

TOKEN_CONTRACT,

AVG(PRICE_USD) AS price

FROM flow.core.fact_prices

WHERE TIMESTAMP >= '2022-01-01'

GROUP BY month, token_contract

), volumes AS (

SELECT

DATE_TRUNC('Month', BLOCK_TIMESTAMP) AS month,

TOKEN_CONTRACT,

-- SPLIT(TOKEN_CONTRACT, '.')[2] AS token,

SUM(AMOUNT) AS amount

FROM

flow.core.ez_token_transfers

WHERE BLOCK_TIMESTAMP >= '2022-01-01'

GROUP BY month, TOKEN_CONTRACT

), RESULT AS (

SELECT

volumes.month AS month,

-- volumes.TOKEN_CONTRACT AS TOKEN_CONTRACT,

SPLIT(volumes.TOKEN_CONTRACT, '.')[2] AS token,

volumes.amount AS amount,

prices.price AS price,

volumes.amount * prices.price AS usd_volume

FROM volumes

LEFT JOIN (

SELECT * FROM monthly_average_prices

) AS prices

ON volumes.TOKEN_CONTRACT = prices.TOKEN_CONTRACT

AND volumes.month = prices.month

SELECT * FROM result

WHERE price IS NOT NULL