AS (

SELECT date_trunc('day', BLOCK_TIMESTAMP) AS "Date"

,sum(FEE) / 1e6 AS "Daily_Fees"

FROM lava.core.fact_transactions

WHERE 1 = 1

--AND TRANSFER_TYPE = 'IBC_TRANSFER_IN'

--AND TRANSFER_TYPE = 'IBC_TRANSFER_OUT'

--AND TRANSFER_TYPE = 'LAVA'

--AND CURRENCY = 'ulava'

--AND CURRENCY = 'ibc/0471F1C4E7AFD3F07702BEF6DC365268D64570F7C1FDC98EA6098DD6DE59817B'

--AND TX_ID = '155173C7401CA66FE0241F03626C18C23169E3522769C48D145E52931BDAE30A'

--AND MSG_INDEX = '32'

--AND TX_SUCCEEDED = 'TRUE'

AND BLOCK_TIMESTAMP >= '2024-07-28'

GROUP BY 1

)

,prices

AS (

SELECT date_trunc('day', HOUR) AS "Date"

,avg(PRICE) AS "Price"

--,avg(PRICE) * 200000000 AS "FDV"

FROM arbitrum.price.ez_prices_hourly

WHERE 1 = 1

AND TOKEN_ADDRESS = lower('0x11e969e9b3f89cb16d686a03cd8508c9fc0361af')

AND BLOCKCHAIN = 'arbitrum'

AND date_trunc('day', HOUR) > CURRENT_DATE - interval '360 days'

GROUP BY 1

)

SELECT c."Date"

,c."Daily_Fees"

,"Price"

,c."Daily_Fees" * "Price" AS "Daily_Fees_$"

FROM cte AS c

LEFT JOIN prices AS p ON c."Date" = p."Date"

ORDER BY 1 DESC