LittlerData2023-04-20 11:36 AM
    Updated 2023-04-20
    with pricet as (
    select
    date_trunc('day', hour) as hour
    ,symbol
    ,avg(price) as price
    from crosschain.core.ez_hourly_prices
    group by 1,2

    union all

    select
    date_trunc('day', recorded_hour) as hour,
    symbol,
    avg(price) as price
    from osmosis.core.ez_prices
    group by 1,2
    ),

    satellite as (
    select
    date_trunc('hour',block_timestamp) hour
    ,case when token_address = '0x120ad3e5a7c796349e591f1570d9f7980f4ea9cb' then 'LUNC'
    else token_symbol
    end as token_symbol
    ,amount
    ,source_chain
    from axelar.core.ez_satellite
    ),

    joining as (
    select
    date_trunc('week',hour) date
    ,sum(amount*price) usd_volume
    ,token_symbol
    from satellite
    join pricet using (hour)