select

date_trunc('day', block_timestamp) as date,

tx_hash,

from_address as address,

-raw_amount as amount,

contract_address

from ethereum.core.fact_token_transfers

where contract_address = '0x92d6c1e31e14520e676a687f0a93788b716beff5'

and date_trunc('day', block_timestamp) < '2023-01-01'

union all

select

date_trunc('day', block_timestamp) as date,

tx_hash,

to_address as address,

raw_amount as amount,

contract_address

from ethereum.core.fact_token_transfers

where contract_address = '0x92d6c1e31e14520e676a687f0a93788b716beff5'

and date_trunc('day', block_timestamp) < '2023-01-01'

select address,

sum(amount)/1e18 as holdings,

case when holdings > 0 and holdings <= 100 then 'Holders'

when holdings > 100 and holdings <= 1000 then 'Small Investors'

when holdings > 1000 and holdings <= 10000 then 'Medium Investors'

when holdings > 10000 and holdings <= 100000 then 'Large Investors'

when holdings > 100000 then 'Whales'

else 'Other' end as category

from transfers

group by 1

having holdings > 0

order by 2 desc;