date_trunc('day',RECORDED_HOUR) as date,

avg(CLOSE) as price

from crosschain.core.fact_hourly_prices

where ID = 'terra-luna-2'

group by 1

),main as (select

date_trunc('day',block_timestamp) as date,

RECEIVER,

TX_ID,

sum(AMOUNT)/pow(10,6) as "staking reward (Luna)"

from terra.core.ez_transfers

where MESSAGE_VALUE['@type'] ='/cosmos.distribution.v1beta1.MsgWithdrawDelegatorReward'

and CURRENCY='uluna'

and TX_SUCCEEDED = TRUE

group by 1,2,3

), main1 as(select

DISTINCT RECEIVER as user,

count(DISTINCT tx_id) as "count of rewards distributed",

sum("staking reward (Luna)"*price) as "total staking reward (USD)"

from main a join luna_price b on a.date=b.date

group by 1)

case

when "count of rewards distributed" = 1 then 'just once rewarded'

when "count of rewards distributed" between 2 and 5 then '2 - 5 rewarded'

when "count of rewards distributed" between 6 and 10 then '6 - 10 rewarded'

when "count of rewards distributed" between 11 and 20 then '11 - 20 rewarded'

when "count of rewards distributed" between 21 and 50 then '21 - 50 rewarded'

when "count of rewards distributed" between 51 and 100 then '51 - 100 rewarded'

when "count of rewards distributed" between 101 and 200 then '101 - 200 rewarded'

when "count of rewards distributed" between 201 and 500 then '201 - 500 rewarded'

when "count of rewards distributed" between 501 and 1000 then '501 - 1000 rewarded'

else 'more than 1000 rewarded'

end as "Reward count",

count(user) as "count of users"