'L1' as sub_label,

avg(b.near_price*TRANSACTION_FEE)/pow(10,24) as daily_avg_fee

FROM near.core.fact_transactions a

LEFT JOIN (SELECT date_trunc('day',timestamp) as date,

median(price_usd) as near_price

FROM near.core.fact_prices

WHERE symbol = 'NEAR'

GROUP BY date) b

ON date_trunc('day',a.block_timestamp) = b.date

WHERE date_trunc('day',a.block_timestamp) between CURRENT_DATE -30 and CURRENT_DATE-1

UNION

SELECT 'Polygon' as label,

'L1' as sub_label,

avg(avg_daily_price*tx_fee) as daily_avg_fee

FROM polygon.core.fact_transactions a

LEFT JOIN

(SELECT date_trunc('day', hour) as date,

avg(price) as avg_daily_price

FROM ethereum.core.fact_hourly_token_prices

WHERE symbol = 'MATIC'

GROUP by date)b

ON date_trunc('day',block_timestamp) = b.date

WHERE date_trunc('day',block_timestamp) between CURRENT_DATE -30 and CURRENT_DATE-1

SELECT 'Ethereum' as label,

'L1' as sub_label,

avg(b.eth_price*tx_fee) as daily_avg_fee

FROM ethereum.core.fact_transactions a

LEFT JOIN (

SELECT date_Trunc('day', block_timestamp) as date,

median(amount_out/amount_in) as eth_price

FROM ethereum.core.ez_dex_swaps

WHERE date >= CURRENT_DATE-30

AND symbol_in = 'WETH' AND symbol_out = 'USDC'

AND amount_in >= 0.001