MLDZMNmad2
    Updated 2023-06-03
    with time as (
    select
    case
    when '{{Period}}' = 'Last 24 Hours' then 1
    when '{{Period}}' = 'Last 7 Days' then 7
    when '{{Period}}' = 'Last 30 Days' then 30
    when '{{Period}}' = 'All Time' then 500
    else 30
    end as time_gp
    ),

    tb2 as (select
    RECORDED_HOUR::date as day,
    avg(close) as price_token
    from solana.core.fact_token_prices_hourly where SYMBOL='SOL'
    group by 1)

    select
    date_trunc('{{Time_basis}}',BLOCK_TIMESTAMP) as date,
    count(distinct TX_ID) as sale_no,
    count(distinct PURCHASER) as buyer_no,
    count (distinct s.MINT) as no_NFTs,
    sum(SALES_AMOUNT) as volume_SOL,
    sum(SALES_AMOUNT*price_token) as volume_usd,
    avg(SALES_AMOUNT) as average_volume_SOL,
    avg(SALES_AMOUNT*price_token) as average_volume,
    lag(average_volume) ignore nulls over(ORDER BY date ASC) as lag_volume,
    ((average_volume-lag_volume)/volume_usd)*100 as deviation_price,
    avg(average_volume) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as MA_7_Days,
    min(SALES_AMOUNT*price_token) as floor_price,
    max(SALES_AMOUNT*price_token) as highest_price,
    median(SALES_AMOUNT*price_token) as median_price
    from solana.core.fact_nft_sales s
    left outer join solana.core.dim_labels b on s.mint=b.address
    join tb2 y on s.BLOCK_TIMESTAMP::date=y.day
    where SUCCEEDED='TRUE'
    Run a query to Download Data