bridges as ( -- bridge users table

select

tx_signer,

min(block_timestamp) as debut

from near.core.fact_transactions

where tx_receiver = 'wrap.near'

group by 1

),

monthly as (

select

trunc(block_timestamp,'month') as month,

case when x.tx_receiver = 'dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near' then 'factory.bridge.near1'

when x.tx_receiver = 'a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near' then 'factory.bridge.near2'

else

x.tx_receiver end as protocol,

count(distinct tx_hash) as transactions

from near.core.fact_transactions x

join bridges y on x.tx_signer=y.tx_signer and x.block_timestamp>y.debut

GROUP BY 1,2

Order by 1 asc

),

ranks as

(SELECT monthly.*,

RANK() OVER(PARTITION BY month ORDER BY transactions DESC) as rank

FROM monthly

)

SELECT *

FROM ranks

WHERE rank <= 10

ORDER BY month asc, transactions desc