P as( select CATEGORY, PROTOCOL from external.defillama.dim_protocols ),

L as( select *

from external.defillama.fact_protocol_fees_revenue

where lower(chain) != lower(protocol)

and revenue > 0),

A as( select CATEGORY, sum(revenue) as revenue, LISTAGG(DISTINCT a.protocol, ' - ') as tag

from L as a

left join P as b on a.protocol = b.protocol

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

GROUP by 1),

B as( select CATEGORY, sum(revenue) as revenue

from L as a

left join P as b on a.protocol = b.protocol

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

GROUP by 1),

C as(

select a.category, tag,

a.revenue as revenue_2023, a.revenue / sum(a.revenue) over() as rate_2023,

b.revenue as revenue_2022, b.revenue / sum(b.revenue) over() as rate_2022,

sum(a.revenue) over() as total_revenue_2023,

sum(b.revenue) over() as total_revenue_2022

from A as a

left join B as b on a.category = b.category)

select category,

to_varchar(revenue_2023, '$999,999,999,999') as revenue_2023,

to_varchar(revenue_2022, '$999,999,999,999') as revenue_2022,

to_varchar(rate_2023*100, '999,999,999') || '%' as rate_2023,

to_varchar(rate_2022*100, '999,999,999') || '%' as rate_2022,

to_varchar(round((rate_2023/rate_2022 -1 )*100),'999,999') || '%' as change,