0xtableauSafety Module Rewards
    Updated 2022-11-28
    with staked_aave as (

    select date_trunc('day',block_timestamp) as "day",
    contract_name,
    TX_TO_LABEL_SUBTYPE,
    sum(event_inputs:amount::float/1e18) as "amount"
    from ethereum.events_emitted
    where block_timestamp > getdate() - interval '3 months'
    and upper(contract_address) = upper('0x4da27a545c0c5B758a6BA100e3a049001de870f5')
    and event_name = 'RewardsAccrued'
    and TX_SUCCEEDED = 'TRUE'
    group by 1,2,3
    order by 1 desc, 2 desc),

    prices as (
    select date_trunc('day',hour) as "day",
    symbol,
    round(avg(price),4) as Avg_Daily_Price
    from ethereum.token_prices_hourly
    where hour > getdate() - interval '3 months'
    and symbol = 'AAVE'
    group by 1, 2
    order by 1 desc
    )

    select a.*
    , b.Avg_Daily_Price
    ,'AAVE' as type
    , round(a."amount" * b.Avg_Daily_Price,4) as amount_usd
    from staked_aave a
    left join prices b
    on a."day" = b."day"
    order by 1 desc


    Run a query to Download Data