elsina✅ Certain collection: Daily - single number
    Updated 2023-04-13
    with price as (
    select
    date_trunc('day', hour) as date,
    avg(price) as usd_price
    from ethereum.core.fact_hourly_token_prices
    where
    token_address = lower('0xD31a59c85aE9D8edEFeC411D448f90841571b89c')
    group by 1
    ),
    info as (
    SELECT
    date_trunc('day', block_timestamp) as "day",
    sum(sales_amount * usd_price) as "sales volume",
    avg(sales_amount * usd_price) as "avg nft price",
    count(distinct purchaser) as "unique buyers",
    count(distinct seller) as "unique sellers",
    count(distinct tx_id) as "sales count"
    FROM solana.core.fact_nft_sales s join price n on block_timestamp::date = date join solana.core.dim_nft_metadata m on s.mint = m.mint
    where
    "day" >= current_date - 90 and
    project_name = '{{collection}}' and
    succeeded = TRUE
    group by 1
    ),
    avg_info as (
    select
    avg("sales volume") as "daily sales volume (in usd)",
    avg("avg nft price") as "daily nft price (in usd)",
    avg("sales count") as "daily sales count",
    avg("unique buyers") as "daily unique buyers",
    avg("unique sellers") as "daily unique sellers"
    from info
    )
    select *
    from avg_info
    Run a query to Download Data