'NEAR' as sub_label,

date_trunc('day',a.block_timestamp) as dates,

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

sum(b.near_price*TRANSACTION_FEE)/pow(10,24) as tx_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 = 'wNEAR'

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

GROUP by DATEs

UNION

SELECT 'Polygon' as label,

'L1' as sub_label,

date_trunc('day',a.block_timestamp) as dates,

avg(avg_daily_price*tx_fee) as daily_avg_fee,

sum(avg_daily_price*tx_fee) as tx_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',a.block_timestamp) = b.date

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

GROUP BY DATEs

SELECT 'Ethereum' as label,

'L1' as sub_label,

date_trunc('day',a.block_timestamp) as dates,

avg(b.eth_price*tx_fee) as daily_avg_fee,

sum(b.eth_price*tx_fee) as tx_fee