select

HOUR,

TOKEN_ADDRESS,

symbol,

PRICE

from crosschain.price.ez_prices_hourly

where TOKEN_ADDRESS in ('7vfCXTUXx5WJV5JADk17DUJ4ksgau7utNKj4b963voxs') -- ETH

and BLOCKCHAIN in ('solana')

and HOUR >= '2024-11-05'),

balances as (

select

PRE_BALANCES[1]/pow(10,9) as pre_bal,

PRE_BALANCES[1]/pow(10,9) * price as usd_pre_bal,

*

from eclipse.core.fact_transactions

join price on date_trunc('hour', block_timestamp) = date_trunc('hour', hour)

where tx_id in (select distinct tx_id from eclipse.core.fact_events where signers[0] = 'GasssK1zRZJ378zkkTuqP7J8ExwmYMaHsXtxNuDoXkne' and PROGRAM_ID = 'whirLbMiicVdio4qvUfM5KAg6Ct8VwpYzGff3uctyCc' and SUCCEEDED = 'TRUE')

order by 3)

select

case

when usd_pre_bal = 0 then 'a. Without Gas'

when usd_pre_bal > 0 and usd_pre_bal <= 0.5 then 'b. <= $0.5'

when usd_pre_bal > 0.5 and usd_pre_bal <= 1 then 'c. $0.5 - $1'

when usd_pre_bal > 1 and usd_pre_bal <= 5 then 'd. $1 - $5'

when usd_pre_bal > 5 and usd_pre_bal <= 10 then 'e. $5 - $10'

when usd_pre_bal > 10 and usd_pre_bal <= 50 then 'f. $10 - $50'

when usd_pre_bal > 50 then 'g. > $50'

end as "Balance Level",

avg(pre_bal) as "Average ETH Balance",

Median(pre_bal) as "Median ETH Balance",

count(distinct signers[1]) as "Station Visitors"

from balances

group by 1