L AS ( select * from (VALUES

( 'Ethereum','Ethereum','Ethereum'),

( '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,

CASE when tag = 'L2' then 'LAYER2' else 'ETHEREUM' end as 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 *

from B