MahrooUntitled Query
    Updated 2022-11-15
    with nearpricet as (
    select timestamp::date as day,
    symbol,
    avg (price_usd) as usdprice
    from near.core.fact_prices
    group by 1,2),

    flowpricet as (
    select timestamp::date as day,
    token_contract,
    avg (price_usd) as usdprice
    from flow.core.fact_prices
    group by 1,2),

    solpricet as (
    select block_timestamp::date as day,
    swap_from_mint,
    median (swap_to_amount/swap_from_amount) as USDPrice
    from solana.fact_swaps
    where swap_to_mint in ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB') --USDC,USDT
    and swap_to_amount > 0
    and swap_from_amount > 0
    and succeeded = 'TRUE'
    group by 1,2),

    maintable as (
    select 'NEAR' as chain,
    date_trunc(day,block_timestamp) as date,
    case when date >= '2022-11-07' then 'After Collapse'
    else 'Before Collapse' end as timespan,
    count (distinct tx_hash) as TX_Count,
    count (distinct trader) as Users_Count,
    sum (amount_in*usdprice) as Swap_Volume,
    avg (amount_in*usdprice) as Average_Swap_Volume,
    sum (swap_volume) over (order by date) as Cumulative_Volume
    from near.core.ez_dex_swaps t1 join nearpricet t2 on t1.block_timestamp::Date = t2.day and t1.token_in = t2.symbol
    Run a query to Download Data