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_trunc('day',date) as date, b.chain, volume

from external.defillama.fact_dex_volume as a

join L as b on lower(a.chain) = lower(b.chain)

where date >= '2023-01-01'),

B as(

select date_trunc('week',date) as date, chain, sum(volume) as "Weekly volume"

from A

where date_trunc('week',date) != date_trunc('week',current_date)

group by 1,2)

select *,

sum("Weekly volume") over(partition by date) as total_volume

from B

order by 1 desc, 3 desc