0xHaM-dNew vs Recurring NFT Users
    Updated 2024-10-17
    with users as (
    SELECT
    block_timestamp,
    tx_hash,
    from_address as user,
    FROM kaia.core.fact_transactions tx
    JOIN (
    SELECT
    distinct tx_hash,
    block_timestamp
    FROM kaia.core.fact_event_logs
    join kaia.core.dim_labels on contract_address = address
    where label_type = 'dex'
    AND tx_succeeded = 'TRUE'
    ) using(block_timestamp, tx_hash)
    )
    ,DAU_u as (
    SELECT
    date_trunc('d', BLOCK_TIMESTAMP) as day,
    count(DISTINCT user) as Active_users
    FROM users
    GROUP BY 1
    )
    ,new as (
    SELECT
    date_trunc('d', first_tx) as day,
    count(DISTINCT user) as new_user
    FROM (
    SELECT
    user,
    min(block_timestamp) as first_tx
    FROM users
    GROUP BY 1
    )
    GROUP BY 1
    )