SELECT

*,

avg_fee_matic * price_m as avg_mfee_usd,

total_fee_matic * price_m as total_mfee_usd

FROM (

SELECT

date_trunc('day', block_timestamp) as day_1,

avg(tx_fee) as avg_fee_matic,

sum(tx_fee) as total_fee_matic

FROM polygon.core.fact_transactions

GROUP BY 1

) as m_fees

LEFT outer JOIN (

SELECT

date_trunc('day', hour) as day_2,

avg(price) price_m

FROM ethereum.core.fact_hourly_token_prices

WHERE symbol LIKE 'MATIC'

GROUP BY 1

)

ON day_1 = day_2

), eth_fees as (

SELECT

*,

avg_fee_eth * price_e as avg_efee_usd,

total_fee_eth * price_e as total_efee_usd

FROM (

SELECT

date_trunc('day', block_timestamp) as day_3,

avg(tx_fee) as avg_fee_eth,

sum(tx_fee) as total_fee_eth

FROM ethereum.core.fact_transactions

GROUP BY 1

) as m_fees

LEFT outer JOIN (