Abolfazl_771025top 10 token that swapped in by volume (USD) at autumn
    Updated 2022-10-20
    WITH swaps_main AS (SELECT
    block_timestamp,
    tx_hash,
    split(value,' ') as split,
    split[0]::string as stat,
    replace(split[2],',') as token_in1,
    replace(split[5],',') as token_out1,
    split[1] as amount_in1,
    split[4] as amount_out1
    FROM near.core.fact_receipts,table(flatten(input => logs))
    where receiver_id = 'v2.ref-finance.near'

    ),swaps AS (SELECT
    block_timestamp,
    date(block_timestamp) as swap_date,
    tx_hash,
    tx_signer,
    nvl(a.symbol, token_in1) as token_in,
    nvl(b.symbol, token_out1) as token_out,
    (try_to_numeric(amount_in1::string))/pow(10,a.decimals) as amount_in,
    (try_to_numeric(amount_out1::string))/pow(10,b.decimals) as amount_out
    FROM swaps_main
    LEFT JOIN near.core.dim_token_labels a ON a.token_contract = token_in1
    LEFT JOIN near.core.dim_token_labels b ON b.token_contract = token_out1
    LEFT JOIN (SELECT tx_signer,tx_hash as tx_h FROM near.core.fact_transactions) ON tx_h = tx_hash
    WHERE stat = 'Swapped'
    ),ORACLE_PRICES AS (SELECT
    date(block_timestamp) as price_date,
    token_in as oracle_symbol,
    sum(amount_out)/sum(amount_in) as price_usd
    FROM swaps
    GROUP BY 1,2
    ),swaps_usd AS (SELECT
    swap_date,
    tx_hash,
    tx_signer,
    Run a query to Download Data