John_Galtsuper cool query
    Updated 2022-03-28
    WITH prices as (
    select
    date_trunc('hour', block_timestamp) as date,
    currency,
    avg(price_usd) as price
    from terra.oracle_prices
    group by 1, 2
    ),
    rewards as (
    select
    date_trunc('hour', t.block_timestamp) as date,
    fl.value:denom::string as currency,
    SUM(fl.value:amount::float / POW (10, 6)) as total
    from terra.transitions t,
    lateral flatten(input => event_attributes:amount) fl
    where t.event = 'rewards'
    group by 1, 2
    ),
    commissions as (
    select
    date_trunc('hour', t.block_timestamp) as date,
    fl.value:denom::string as currency,
    SUM(fl.value:amount::float / POW (10, 6)) as event_amount
    from terra.transitions t,
    lateral flatten(input => event_attributes:amount) fl
    where t.event = 'commission'
    group by 1, 2
    ),
    daily_rewards as (
    select date_trunc('day', r.date) as date,
    sum(total * price) as total_rewards
    from rewards r
    left outer join prices p
    on r.date = p.date
    and r.currency = p.currency
    group by 1