plugins as (select concat('0x',substr(TOPICS[1], 27,64)) as plugin, concat('0x',substr(TOPICS[2], 27,64)) as gauge from berachain.testnet.fact_event_logs

where (ORIGIN_TO_ADDRESS=lower('0x580ABF764405aA82dC96788b356435474c5956A7') or ORIGIN_TO_ADDRESS=lower('0x2363BB86cD2ABF89cc059A654f89f11bCceffcA9')

or ORIGIN_TO_ADDRESS=lower('0x1f9505Ae18755915DcD2a95f38c7560Cab149d9C'))

and ORIGIN_FUNCTION_SIGNATURE='0xd8867fc8'

and topics[0]='0xb920b936f556d443772609e0cc06a72c3bb67fc606de10dcdd51323d1ddbc7fc'),

deposits as (

select plugin, user, sum(amount) as dep_amount from (

select ORIGIN_TO_ADDRESS as plugin, ORIGIN_FROM_ADDRESS as user, (utils.udf_hex_to_int(substr(data,3,66))::int)/1e18 as amount from berachain.testnet.fact_event_logs

where ORIGIN_TO_ADDRESS in (select plugin from plugins)

and ORIGIN_FUNCTION_SIGNATURE='0x2f4f21e2'

and topics[0]='0x9a5084d92dfc874319b7345834de998ed1154f2ed11a4a6d353cf77a1bdb456e')

group by 1,2

),

withdraws as (

select plugin, user, sum(-amount) as with_amount from (

select ORIGIN_TO_ADDRESS as plugin, ORIGIN_FROM_ADDRESS as user,(utils.udf_hex_to_int(substr(data,3,66))::int)/1e18 as amount from berachain.testnet.fact_event_logs

where ORIGIN_TO_ADDRESS in (select plugin from plugins)

and ORIGIN_FUNCTION_SIGNATURE='0x205c2878'

and topics[0]='0x3d9b7cdf60349a62a2f52625a9697e0e2782b9ef0985bec5e10de869ec541ac5')

group by 1,2

select plugin, farmers, sum(farmers) over (order by farmers) as unique_deposits from (

select plugin, count(distinct user) as farmers from (

select d.plugin, d.user, dep_amount+coalesce(with_amount,0) as current_deposit from deposits d

left join withdraws w on w.plugin=d.plugin and w.user=d.user

where current_deposit > 0) r

group by 1

order by farmers desc)

order by farmers desc