shreexAaaA
    Updated 2022-12-13
    with prices as (
    select
    date_trunc('day',recorded_hour) as price_date,
    avg(close) as price
    from solana.core.fact_token_prices_hourly where id='solana' and provider='coingecko' and symbol='SOL' and price_date > '2022-01-01'
    GROUP BY price_date
    )
    select
    date_trunc('day',block_timestamp) as date,
    marketplace,
    count(tx_id) as sales,
    count(distinct purchaser) as unique_buyers,
    count(distinct seller) as unique_sellers,
    sum(sales_amount) as volume_sol,
    volume_sol*avg(price) as volume_usd,
    sum(volume_sol) over (order by date) as cumulative_volume,
    sum(sales) over (order by date) as cumulative_sales
    from solana.core.fact_nft_sales
    left join prices on price_date=date_trunc('day',block_timestamp)
    where date > '2022-09-17' and marketplace not in ('solana monkey business marketplace','solport','magic eden v1' )
    GROUP BY date,marketplace
    ORDER BY date
    Run a query to Download Data