misaghlbFlipside's World Cup Kick Off: Gas Guzzlers - top solana
    Updated 2022-11-15
    with eth_prices as (
    SELECT date_trunc('day', hour) as pdate, avg(price) as aprice
    FROM ethereum.core.fact_hourly_token_prices
    WHERE symbol = 'WETH' and pdate >= CURRENT_DATE - 30
    GROUP BY pdate
    ),
    matic_prices as (
    SELECT date_trunc('day', hour) as pdate, avg(price) as aprice
    FROM ethereum.core.fact_hourly_token_prices
    WHERE symbol = 'MATIC' and pdate >= CURRENT_DATE - 30
    GROUP BY pdate
    ),
    algo_prices as (
    SELECT date_trunc('day', block_hour) as pdate, avg(price_usd) as aprice
    FROM algorand.core.ez_price_pool_balances
    WHERE asset_name = 'ALGO' and pdate >= CURRENT_DATE - 30
    GROUP BY pdate
    ),
    near_prices as (
    SELECT date_trunc('day', timestamp) as pdate, avg(price_usd) as aprice
    FROM near.core.fact_prices
    WHERE symbol = 'wNEAR' and pdate >= CURRENT_DATE - 30
    GROUP BY pdate
    ),
    flow_prices as (
    SELECT date_trunc('day', timestamp) as pdate, avg(price_usd) as aprice
    FROM flow.core.fact_prices
    WHERE symbol = 'FLOW' and pdate >= CURRENT_DATE - 30
    GROUP BY pdate
    ),
    osmo_prices as (
    SELECT date_trunc('day', recorded_at) as pdate, avg(price) as aprice
    FROM osmosis.core.dim_prices
    WHERE symbol = 'OSMO' and pdate >= CURRENT_DATE - 30
    GROUP BY pdate
    ),