kidaTop 100000 Satellite User Detailed Volume
    Updated 2023-05-10
    with osmosis_prices as (
    select
    recorded_hour::date as date,
    lower(symbol) as symbol,
    avg(price) as price
    from
    osmosis.core.ez_prices
    group by 1,2
    ),

    eth_prices as (
    select
    hour::date as date,
    lower(symbol) as symbol, -- to make sure it matches the decoded log
    avg(price) as price
    from
    ethereum.core.fact_hourly_token_prices
    group by 1,2
    ),

    misc_prices as (
    select
    date(block_timestamp) as date,
    lower(replace(feed_name, ' / USD')) as symbol,
    median(coalesce(latest_answer_adj, latest_answer_unadj / pow(10,8))) as price --using median cause there will be some nulls / zeroes
    from ethereum.chainlink.ez_oracle_feeds
    where feed_category = 'Cryptocurrency (USD pairs)'
    and feed_name in ('BTC / USD', 'FIL / USD', 'ETH / USD', 'DOT / USD', 'AVAX / USD')
    group by 1,2
    order by 1
    ),

    agg as (
    select
    block_timestamp::date as date,
    tx_hash,
    Run a query to Download Data