select

date_trunc('day', recorded_hour)::date as day,

(avg(open) + avg(close)) / 2 as price

from crosschain.core.fact_hourly_prices

where id = 'terra-luna-2'

group by 1

),

t2 as (

select

amount::number / pow(10, 6) as staking_rewards_luna,

(amount::number / pow(10, 6)) * price as staking_rewards_usd

from terra.core.ez_transfers

join t1

on day = block_timestamp::date

where currency = 'uluna'

and message_type = '/cosmos.distribution.v1beta1.MsgWithdrawDelegatorReward'

and tx_succeeded = 1

select

case

when staking_rewards_usd < 1 then '1. Less Than $1'

when staking_rewards_usd < 10 then '2. Between $1 and $10'

when staking_rewards_usd < 100 then '3. Between $10 and $100'

when staking_rewards_usd < 1000 then '4. Between $100 and $1,000'

when staking_rewards_usd < 10000 then '5. Between $1000 and $10,000'

else '6. More than $10,000'

end as type,

count(*) as count

from t2

group by type

order by type