L AS ( select * from (VALUES

( 'L2','OptimisticRollup','Arbitrum'),

( 'L2','OptimisticRollup','Optimism'),

( 'L2','OptimisticRollup','Base'),

( 'L2','OptimisticRollup','Manta'),

( 'L2','OptimisticRollup','Kroma'),

( 'L2','OptimisticRollup','Public Good'),

( 'L2','zkRollup','Scroll'),

( 'L2','zkRollup','zkSync Era'),

( 'L2','zkRollup','Linea'),

( 'L2','zkRollup','Polygon zkEVM'),

( 'L2','zkRollup','Starknet'),

( 'L2','Optimium','Mantle')

) as l1(tag, technology, chain) ),

A as( select date,

a.chain,

--CASE when a.chain in ('Arbitrum','Optimism') then a.chain else 'New L2s' end as chain,

tvl_usd as tvl

from external.defillama.fact_chain_tvl as a

JOIN L as c on a.chain = c.chain

where tvl_usd is not null and tvl_usd > 0

and date >= '2022-01-01')

select date,

avg(change_7d) as avg_change_7d,

avg(change_30d) as avg_change_30d

(select date,

chain,

tvl,