SocioCryptoCBSC Users Fund
    Updated 2024-01-29
    -- forked from CBS Users @ https://flipsidecrypto.xyz/edit/queries/8800763b-b17d-4996-9707-4740ac74f0a7

    -- forked from AGX @ https://flipsidecrypto.xyz/edit/queries/94d1e8d3-e646-429f-aaab-718202df2457
    with cbsc_users as (
    SELECT DISTINCT origin_from_address as users
    FROM avalanche.defi.ez_dex_swaps
    WHERE block_timestamp::date BETWEEN current_date-interval'{{last_period}}, 1 day' AND current_date-1
    AND symbol_in IN ('AGX', 'AUX')
    UNION
    SELECT DISTINCT origin_to_address
    FROM avalanche.defi.ez_dex_swaps
    WHERE block_timestamp::date BETWEEN current_date-interval'{{last_period}}, 1 day' AND current_date-1
    AND symbol_out IN ('AGX', 'AUX')
    ),
    first_received as (
    SELECT *
    from(
    SELECT to_address as user,
    project_name as CEX,
    tx_hash,
    rank()over(partition by to_address order by block_timestamp) as rank
    FROM
    (
    SELECT
    project_name,
    to_address,
    tx_hash,
    block_timestamp
    FROM avalanche.core.ez_token_transfers a
    LEFT JOIN avalanche.core.dim_labels b
    on a.from_address = b.address
    WHERE b.label_type = 'cex'
    UNION
    SELECT
    project_name,
    avax_to_address,
    Last run: over 1 year agoAuto-refreshes every 24 hours
    ONBOARDED_BY_CEX_PCNT
    1
    15.445545
    1
    13B
    266s