Madisenses chart
    Updated 2023-04-13
    with df1 as (SELECT
    CASE
    WHEN contract_address = '0x8d0501d85becda92b89e56177ddfcea5fc1f0af2' then 'The Senses'
    end as collection,
    *
    from polygon.core.fact_event_logs
    WHERE EVENT_REMOVED = 'false' and TX_STATUS = 'SUCCESS' and contract_address = '0x8d0501d85becda92b89e56177ddfcea5fc1f0af2' and date_trunc('day', BLOCK_TIMESTAMP) >= '2022-08-19'
    ),
    df as (
    SELECT
    --date_trunc('day',a.BLOCK_TIMESTAMP) as date,
    a.BLOCK_TIMESTAMP,
    a.TX_HASH as TX_HASH,
    a.CONTRACT_ADDRESS as CONTRACT_ADDRESS,
    a.collection as collection,
    c.FROM_ADDRESS as sellers,
    c.TO_ADDRESS as buyers,
    c.RAW_AMOUNT/pow(10, 18) as amount_eth,
    b.TX_FEE as TX_FEE,
    b.GAS_PRICE as GAS_PRICE,
    b.GAS_USED as GAS_USED
    from df1 a join polygon.core.fact_transactions b on a.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP and a.TX_HASH = b.TX_HASH
    join polygon.core.fact_token_transfers c on b.BLOCK_TIMESTAMP = c.BLOCK_TIMESTAMP and b.TX_HASH = c.tx_hash)

    select
    date_trunc('day', BLOCK_TIMESTAMP) as date,
    count(DISTINCT tx_hash) as tx_total,
    count(distinct buyers) as unique_buyers,
    count(distinct sellers) as unique_sellers,
    sum(amount_eth) as sales_volume_eth,
    min(amount_eth) as floor_price,
    median(amount_eth) as median_price,
    unique_buyers/unique_sellers as buyers_sellers_ratio,
    sum(tx_total) over (order by date asc rows between unbounded preceding and current row) as cum_TX,
    sum(sales_volume_eth) over (order by date asc rows between unbounded preceding and current row) as cum_amount,
    Run a query to Download Data