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

    flowpricet as (
    select timestamp::date as day,
    avg (price_usd) as usdprice
    from flow.core.fact_prices
    where source = 'coinmarketcap'
    and symbol = 'FLOW'
    group by 1),

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

    select 'NEAR' as chain,
    date_trunc (day,block_timestamp) as date,
    count (distinct tx_hash) as TX_Count,
    count (distinct tx_signer) as Users_Count,
    sum ((stake_amount*usdprice)/1e24) as Total_Volume,
    avg ((stake_amount*usdprice)/1e24) as Average_Volume,
    sum (Total_Volume) over (order by date) as Cumulative_Volume
    from near.core.dim_staking_actions t1 join nearpricet t2 on t1.block_timestamp::date = t2.day
    where action = 'Unstake'
    and block_timestamp >= '2022-11-07' and block_timestamp < '2022-11-15'
    Run a query to Download Data