'L1' as sub_label,

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

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 between CURRENT_DATE-interval '1 d,1 y' and CURRENT_DATE-1

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

AND amount_in >= 0.001

GROUP BY date

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

WHERE date_trunc('day',a.block_timestamp) between CURRENT_DATE-interval '1 d,1 y' and CURRENT_DATE-1

GROUP BY dates

SELECT 'Solana' as label,

'L1' as sub_label,

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

avg(sol_price*fee)/pow(10,9) as daily_avg_fee

FROM solana.core.fact_transactions a

LEFT JOIN

(

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

median(swap_to_amount/swap_from_amount) as sol_price

FROM solana.core.fact_swaps

WHERE swap_from_mint = 'So11111111111111111111111111111111111111112'

and (swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' or swap_to_mint = 'Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB'

or swap_to_mint = 'BQcdHdAQW1hczDbBi9hiegXAR7A98Q9jx3X3iBBBDiq4')

and succeeded = 'TRUE'

AND swap_from_amount is not NULL

AND swap_from_amount > 10

AND date < CURRENT_DATE

GROUP BY date