SocioCryptoSales Over time
    Updated 2023-02-23
    with eth_price as (
    SELECT date_trunc('day',hour) as date,
    median(price) as price
    FROM ethereum.core.fact_hourly_token_prices
    WHERE symbol = 'WETH'
    GROUP by date
    )

    SELECT 'solana' as chain,
    date_trunc('day',block_timestamp) as date,
    count(DISTINCT tx_id) as n_sales,
    COUNT(DISTINCT seller) as n_seller,
    COUNT(DISTINCT purchaser) as n_purchaser,
    sum(sales_amount) as amnt,
    sum(sales_amount*b.sol_price) as amnt_usd
    FROM solana.core.fact_nft_sales a
    LEFT JOIN
    (
    SELECT date_trunc('day', block_timestamp) as dates,
    median(swap_to_amount/swap_from_amount) as sol_price
    FROM solana.core.fact_swaps
    WHERE swap_from_mint = 'So11111111111111111111111111111111111111112'
    and (swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' or swap_to_mint = 'Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB'
    or swap_to_mint = 'BQcdHdAQW1hczDbBi9hiegXAR7A98Q9jx3X3iBBBDiq4')
    and succeeded = 'TRUE'
    AND swap_from_amount is not NULL
    AND swap_from_amount > 10
    GROUP BY dates
    )b
    ON date_trunc('day',a.block_timestamp)=b.dates
    WHERE succeeded = 'TRUE'
    AND date between'2022-05-26' and '2022-12-31'
    GROUP BY date
    UNION
    SELECT 'ethereum' as chain,
    date_trunc('day',block_timestamp) as date,
    Run a query to Download Data