select

date_trunc('month', date) as date,

'Bridge' as sector,

protocol,

category,

avg(chain_tvl) as tvl

from external.defillama.fact_protocol_tvl

where chain = 'Avalanche'

and category in ('Bridge' )

and date >= dateadd(year, -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

, bridge_tvl as (

select

date,

sector,

category,

protocol,

rank() over (partition by date order by sum(tvl) desc) as tvl_rank,

sum(tvl) as tvl

from bridge

group by 1,2,3,4

)

select

*

from bridge_tvl

order by date, tvl desc