select

date_trunc('month', date) as date,

'Defi' as sector,

protocol,

category,

avg(chain_tvl) as tvl

from external.defillama.fact_protocol_tvl

where chain = 'Avalanche'

and category in ('Farm', 'Liquid Restaking', 'Options Vault', 'Staking Pool', 'Yield', 'Yield Aggregator', 'Leveraged Farming', 'RWA Lending',

'Options', 'CDP', 'Synthetics', 'Lending', 'Liquid Staking', 'Basis Trading', 'Derivatives', 'Uncollateralized Lending', 'Restaking')

and date >= dateadd(month, -1, date_trunc('month',current_date()))

and date < date_trunc('month',current_date())

and chain_tvl >0

group by 1, 2, 3, 4

order by 1 desc

select

category,

sum(tvl) as tvl

from defi

group by 1

order by 2 desc