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

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

order by 3 desc

limit 10