select *

from $query('75f58bb3-b2cd-4208-85e9-e3d75d901a1c')),

tokens_pool as (

select *

from $query('998239b1-00be-4fa2-8c02-617cd563917c')

),

holders as (

select a.*, case when pool_address is not null then 'Pool' when user in (select address from polygon.core.dim_contracts) then 'Contract' end as tag,

row_number() over (partition by contract order by case when tag is null then 0 else 1 end, balance desc) as rank,

row_number() over (partition by contract order by case when tag='Contract' or tag is null then 0 else 1 end, balance desc) as rank_with_contracts

from

(select user, symbol, a.contract, sum(amount/pow(10, decimal)) as balance

from

(

select from_address as user, -RAW_AMOUNT_PRECISE as amount, tx_hash, contract

from polygon.core.fact_token_transfers

join tokens_data b

on contract=CONTRACT_ADDRESS

where BLOCK_TIMESTAMP::date>=start_date

and BLOCK_TIMESTAMP<date_trunc(hour, '2025-04-04 18:00:00.000'::timestamp)

union all

select to_address, RAW_AMOUNT_PRECISE, tx_hash, contract

from polygon.core.fact_token_transfers

join tokens_data

on contract=CONTRACT_ADDRESS

where BLOCK_TIMESTAMP::date>=start_date

and BLOCK_TIMESTAMP<date_trunc(hour, '2025-04-04 18:00:00.000'::timestamp)

) a

join tokens_data b

on a.contract=b.contract

group by 1,2,3) a

left join tokens_pool

on user=pool_address