A as(

select protocol, sum(revenue) as revenue, max(date) as latest

from external.defillama.fact_protocol_fees_revenue

where date BETWEEN '2022-12-31' and '2023-12-31'

and lower(chain) != lower(protocol)

and protocol not in ('Op_Bnb')

group by 1),

B as(

select a.protocol,

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

a.revenue, a.latest

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.latest = (select max(latest) from A)

and a.revenue > 0),

C as(

select protocol as protocols, sum(revenue) as revenue, max(date) as latest

from external.defillama.fact_protocol_fees_revenue

where date BETWEEN '2021-12-31' and '2022-12-31'

and protocol in (select protocol from B)

group by 1),

D as (

select

lower(protocol),

'v1',''),'v3',''),'v2.1',''),'v2',''),' amm',''),