with atom_price as ( select avg(price) as atom_price

from osmosis.core.ez_prices

where RECORDED_HOUR::date = CURRENT_DATE - 1

and symbol = 'ATOM')

,

senders as ( select sender, sum(amount/pow(10,6)) as total_sent

from cosmos.core.fact_transfers

where sender ilike 'cos%'

and CURRENCY = 'uatom'

and amount/pow(10,6) > 0 and TX_SUCCEEDED = 'TRUE'

group by 1)

,

receiver as ( select RECEIVER , sum(amount/pow(10,6)) as total_received

from cosmos.core.fact_transfers

where RECEIVER ilike 'cos%'

and CURRENCY = 'uatom'

and amount/pow(10,6) > 0 and TX_SUCCEEDED = 'TRUE'

group by 1)

,

final as ( select sender, total_received-total_sent as net

from senders a join RECEIVER b on a.sender = b.RECEIVER

where sender not in ('cosmos14yrwzkjxcgsupam8yd09zruk2jnu2r4k0rx78m','cosmos1my7d7egfkz5k00q97fmwdmvcmac02axmezapdc',

,

final_2 as ( select sender , net, net*atom_price as net_usd

from final, atom_price

where net > 0)

select count(DISTINCT(sender)) as holders, sum(net) as total_atom, sum(net_usd) as atom_in_usd,

avg(net) as avg_atom, avg(net_usd) as avg_usd, median(net) as median_atom, max(net) as max_net

from final_2