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)

select

a.date,

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

count(DISTINCT RECEIVER) as "count of users that rewards distributed them",

sum("staking reward (Luna)") as "staking reward (Luna)",

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

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

group by 1