with prices as (

select date_trunc('hour', block_timestamp) as date,

symbol,

currency,

avg(price_usd) as price

from terra.oracle_prices

group by 1,2,3

),

reward as (

select

date_trunc('hour', t.block_timestamp) as date,

sum(fl.value:amount / pow(10,6)) as event_amount,

fl.value:denom::string as event_currency

-- event_attributes:validator::string as validator

from terra.transitions t

, lateral flatten(input => event_attributes:amount) fl

where t.transition_type = 'begin_block'

and t.event = 'rewards'

group by 1,3)

select date_trunc('day', r.date) as date,

sum(event_amount * price) as reward

from reward r

left outer join prices p

on r.date = p.date

and r.event_currency = p.currency

where r.date <> (select max(date) from reward)

group by 1

order by 1 desc