L AS ( select * from (VALUES

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

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

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

( '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, b.chain, fees, revenue, tag, technology,

sum(revenue) over(partition by protocol order by date) as revenue_growth,

sum(fees) over(partition by protocol order by date) as fees_growth,

sum(fees - revenue) over(partition by protocol order by date) as fees_to_l1

from external.defillama.fact_protocol_fees_revenue as a

join L as b on a.protocol = b.chain

)

select *

(select date,

chain,

(revenue_growth/lag(revenue_growth,7) over (partition by chain order by date)-1)*100 as revenue_change_7d,

(revenue_growth/lag(revenue_growth,30) over (partition by chain order by date)-1)*100 as revenue_change_30d

from A

where date >= '2023-01-01'