select

date_trunc('day',RECORDED_HOUR) as date,

avg(CLOSE) as price

from crosschain.core.fact_hourly_prices

where ID ilike '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 luna_reward

from terra.core.ez_transfers

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

and CURRENCY ilike 'uluna'

and TX_SUCCEEDED = TRUE

group by 1,2,3

)

select

m.date,

count(DISTINCT tx_id) as rewards,

count(DISTINCT RECEIVER) as reward_receivers,

sum(luna_reward) as reward_volume_luna,

sum(luna_reward*price) as reward_volume_usd,

sum(reward_volume_luna) over (order by m.date) as cum_vol_luna,

sum(reward_volume_usd) over (order by m.date) as cum_vol_usd

from main m

join luna_price l on m.date=l.date

group by 1