flipside

    NEW WALLET WHERE 2
    yasminNEW WALLET WHERE 2
    Updated 2025-03-11
    WITH interactions AS (
    SELECT
    block_timestamp::date AS date,
    from_address AS wallet,
    INITCAP(label_type) AS label_category
    FROM avalanche.core.fact_event_logs
    JOIN avalanche.core.dim_labels
    ON address = contract_address
    JOIN avalanche.core.fact_transactions USING (tx_hash)
    WHERE tx_status = 'SUCCESS'
    AND label_type IS NOT NULL
    AND INITCAP(label_type) != 'Token'
    ),
    first_interaction AS (
    SELECT
    from_address,
    MIN(block_timestamp)::date AS first_date
    FROM avalanche.core.fact_transactions
    GROUP BY from_address
    ),
    weekly_stats AS (
    SELECT
    DATE_TRUNC('week', first_date) AS date,
    label_category,
    COUNT(DISTINCT wallet) AS unique_wallets
    FROM interactions
    JOIN first_interaction
    ON interactions.wallet = first_interaction.from_address
    GROUP BY 1, 2
    )
    SELECT
    date,
    label_category,
    unique_wallets
    FROM weekly_stats
    WHERE date >= '2021-02-01'
    Last run: 19 days ago
    DATE
    LABEL_CATEGORY
    UNIQUE_WALLETS
    1
    		2021-02-01 00:00:00.000Bridge177
    2
    		2021-02-01 00:00:00.000Games2
    3
    		2021-02-01 00:00:00.000Nft69
    4
    		2021-02-01 00:00:00.000Cex22
    5
    		2021-02-01 00:00:00.000Dex406
    6
    		2021-02-01 00:00:00.000Defi279
    7
    		2021-02-01 00:00:00.000Dapp120
    8
    		2021-02-08 00:00:00.000Cex94
    9
    		2021-02-08 00:00:00.000Defi1793
    10
    		2021-02-08 00:00:00.000Dex1895
    11
    		2021-02-08 00:00:00.000Bridge954
    12
    		2021-02-08 00:00:00.000Games1
    13
    		2021-02-08 00:00:00.000Nft549
    14
    		2021-02-08 00:00:00.000Dapp706
    15
    		2021-02-08 00:00:00.000Chadmin1
    16
    		2021-02-15 00:00:00.000Games11
    17
    		2021-02-15 00:00:00.000Defi4785
    18
    		2021-02-15 00:00:00.000Dapp1583
    19
    		2021-02-15 00:00:00.000Dex5065
    20
    		2021-02-15 00:00:00.000Nft929
    ...
    1502
    58KB
    123s