tarikflipsideweekly volume solana coins
    Updated 2023-01-29
    with base as (SELECT *, date_trunc('HOUR', block_timestamp) as date_hour
    FROM solana.core.fact_swaps
    where block_timestamp >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '7 DAY'
    and succeeded),

    -- SELECT * FROM base;

    swaps_w_prices as (
    SELECT s.*,
    h.close as from_token_price,
    h.token_name as from_token_name,
    h.symbol as from_token_symbol,
    hh.close as to_token_price,
    hh.token_name as to_token_name,
    hh.symbol as to_token_symbol
    FROM base as s
    left join solana.core.ez_token_prices_hourly as h
    on s.swap_from_mint = h.token_address
    and date_trunc('HOUR', s.block_timestamp) = h.recorded_hour
    left join solana.core.ez_token_prices_hourly as hh
    on s.swap_to_mint = hh.token_address
    and date_trunc('HOUR', s.block_timestamp) = hh.recorded_hour)
    ,

    swap_amounts_in_usd as (
    SELECT *,
    from_token_price * swap_from_amount as swap_from_amt_in_usd,
    to_token_price * swap_to_amount as swap_to_amt_in_usd
    from swaps_w_prices
    where to_token_name is not null
    and from_token_name is not null)

    SELECT top 10
    to_token_name,
    sum(swap_to_amt_in_usd) as usd_amount_sum,
    Run a query to Download Data