snowmanUntitled Query
    Updated 2022-11-15
    with roo as (
    select date_trunc('hour', hour)
    as hour,
    avg(price) as price
    from ethereum.core.fact_hourly_token_prices
    --from ethereum.core.fact_hourly_token_prices
    where token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH
    and hour::date >=
    current_date - 30
    group by 1
    ),
    ethereum_data as (
    select 1000000 * (sum(tx_fee*price) / sum(eth_value*price))
    as transfered
    from ethereum.core.fact_transactions
    inner join roo on date_trunc('hour', block_timestamp) =
    hour
    and block_timestamp::date >=
    current_date - 30
    ),

    sol_price as (
    select date_trunc('hour', recorded_hour) as hour,
    avg(close) as price
    from solana.core.fact_token_prices_hourly
    where symbol = 'SOL'
    and recorded_hour::date >= current_date - 30
    group by 1
    ),
    solana_data as (
    select 1000000 * (sum(price*fee/pow(10,9)) /sum(price*CASE WHEN PRE_BALANCES[0] - POST_BALANCES[0] > 0 THEN PRE_BALANCES[0] - POST_BALANCES[0]
    ELSE POST_BALANCES[0] - PRE_BALANCES[0] END/pow(10,9))) as transfered
    from solana.core.fact_transactions
    inner join sol_price on date_trunc('hour', block_timestamp) =
    Run a query to Download Data