shreex2024-05-19 08:02 PM
    Updated 2024-05-19
    with zero_address AS
    (
    SELECT address FROM arbitrum.core.dim_labels WHERE project_name = 'stargate finance'
    UNION
    SELECT address FROM optimism.core.dim_labels WHERE project_name = 'stargate finance'
    UNION
    SELECT address FROM polygon.core.dim_labels WHERE project_name = 'stargate finance'
    ),
    cexes AS
    (
    SELECT address FROM arbitrum.core.dim_labels WHERE label_type = 'cex' AND label_subtype = 'hot_wallet'
    UNION
    SELECT address FROM optimism.core.dim_labels WHERE label_type = 'cex' AND label_subtype = 'hot_wallet'
    UNION
    SELECT address FROM polygon.core.dim_labels WHERE label_type = 'cex' AND label_subtype = 'hot_wallet'
    ),


    user_list as (
    SELECT
    DISTINCT origin_from_address AS basic_address,
    COUNT(DISTINCT tx_hash) AS transactions,
    sum(amount_usd) as amount_transfered
    FROM arbitrum.core.ez_native_transfers
    WHERE origin_to_address IN (SELECT address FROM zero_address)
    AND amount_usd > 150
    AND block_timestamp >= '2022-03-15'
    GROUP BY 1
    UNION