X as(

select date_trunc('week',date) as date,

max(date) as max_date, min(date) as min_date,

count(DISTINCT date) as number

from external.defillama.fact_protocol_fees_revenue

group by 1

having count(DISTINCT date) >= 6),

X1 as ( select * from X order by 1 desc limit 1),

X2 as ( select * from X where date != (select date from X1) order by 1 desc limit 1),

A as(

select chain, protocol,

max(date) as max_date,

sum(case when date >= (select min_date from X1) and date <= (select max_date from X1) then fees else 0 end) as data_1,

sum(case when date >= (select min_date from X2) and date <= (select max_date from X2) then fees else 0 end) as data_2,

(select min_date from X1) as date_1,

(select min_date from X2) as date_2

from external.defillama.fact_protocol_fees_revenue

where chain = 'arbitrum'

and lower(protocol) != lower(chain)

group by 1,2),

B as(

select a.chain, a.protocol, data_1, data_2, date_1, date_2,

case when b.category is not null then b.category else c.category end as category

from A as a

left join external.defillama.dim_protocols as b on a.protocol = b.protocol

left join external.defillama.dim_dexes as c on a.protocol = c.dex

WHERE a.max_date = (select max(max_date) from A)

and data_1 > 0

group by 1,2,3,4,5,6,7),