flipside

    Distribution of Active Token Holders by Segment copy
    Kruys-CollinsDistribution of Active Token Holders by Segment copy
    Updated 2025-03-17
    -- forked from Distribution of Active Token Holders by Segment @ https://flipsidecrypto.xyz/studio/queries/34492bf5-1f94-4c97-9ef1-cd8e179d2d6c

    WITH
    token_params AS (
    SELECT lower('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E') AS token_address --XSGD token
    ),

    -- Getting all relevant transfer activity
    transfers AS (
    SELECT
    BLOCK_TIMESTAMP,
    FROM_ADDRESS,
    TO_ADDRESS,
    AMOUNT,
    AMOUNT_USD,
    TX_HASH
    FROM avalanche.core.ez_token_transfers
    WHERE CONTRACT_ADDRESS = (SELECT token_address FROM token_params)
    ),

    -- Get liquidity pools that contain the XSGD token
    liquidity_pools AS (
    SELECT
    POOL_ADDRESS,
    PLATFORM
    FROM avalanche.defi.dim_dex_liquidity_pools
    WHERE
    TOKENS:token0::STRING = LOWER((SELECT token_address FROM token_params))
    OR TOKENS:token1::STRING = LOWER((SELECT token_address FROM token_params))
    ),

    -- Calculating metrics per address to enable segmentation
    address_metrics AS (
    SELECT
    address,
    SUM(total_volume_out) AS total_sent_volume,
    Last run: about 1 month ago
    ADDRESS
    SEGMENT
    TOTAL_SENT_VOLUME
    TOTAL_RECEIVED_VOLUME
    TOTAL_SEND_COUNT
    TOTAL_RECEIVE_COUNT
    FIRST_ACTIVITY
    LAST_ACTIVITY
    HOLDING_PERIOD_DAYS
    DAILY_AVG_VOLUME
    DAILY_AVG_TXN_COUNT
    RECENT_VOLUME
    RECENT_TXN_COUNT
    CURRENT_BALANCE
    1
    		0x7dbf3792c0bb891976fda0aad9a33957df8d1ab5Active Trader18.08666825.2248973292025-02-05 15:27:35.0002025-02-18 13:12:24.000133.3316588462.4615384.77882357.138229
    2
    		0x69bbbfde43c49fc50f5647223da5ef4756d78659Active Trader659.78516664.43652691882024-12-20 10:52:09.0002025-01-13 21:08:03.0002455.1759035838.208333004.651366
    3
    		0x8a1a66ccd6b5652a19e927308529c3a5bc7a9605Active Trader3928.9716753930.22118420872025-01-26 13:28:04.0002025-02-02 16:37:24.00071122.74183715.285714001.249509
    4
    		0xb711fad49ef1bd7f60dab5828aca5c45bb036f74Active Trader836.33172836.344228161112024-12-29 20:14:05.0002025-01-02 21:11:41.0004418.16898731.75000.012508
    5
    		0x24cffc09ae59ccf998b97f6441548ceeec86b037Active Trader1125959.3009671125959.30096747642024-12-28 17:26:39.0002025-01-07 06:53:30.00010225191.860193411.1002.328306437e-10
    6
    		0x5fe12b590570598ce854d243fd249229850ca17bActive Trader611205.431354611205.43135429262024-09-21 10:27:29.0002024-10-14 08:35:19.0002353148.2983786092.391304000
    7
    		0x06633ab4cea23895821f54097363227802ca38bfActive Trader185791.721762185791.72176219192025-02-09 00:35:31.0002025-02-25 02:12:30.0001623223.965220252.37583423.546836120
    8
    		0xe1495c0eee2fa937d36b56009c6daaf005367ca8Active Trader836097.115118836097.11511817182025-02-05 16:04:04.0002025-02-13 20:30:39.0008209024.27877954.375000
    9
    		0x009b6592b52ea5b5cd553928a78a365045b8586eActive Trader2927428.3788772927428.37887762622025-01-24 08:27:35.0002025-02-15 03:35:46.00022266129.8526251825.63636460095.50697620
    10
    		0xa971245014cd468b678b0b75c853182c2b3db51bActive Trader1604024.7534361604024.75343629292025-02-09 06:45:50.0002025-03-04 13:25:03.00023139480.4133422612.521739215675.437302120
    11
    		0x21f504cc9e535285e8ba109642d255f07230002dActive Trader411535.74411535.7416162024-09-21 10:28:27.0002024-10-03 07:25:22.0001268589.292.666667000
    12
    		0x8bce8b6bac1639f2adb4496389fa2efbf61bc454Active Trader167.9167.932322024-07-08 04:03:32.0002024-07-17 06:56:11.000937.3111111117.11111100-2.842170943e-14
    13
    		0x7734881ed0c663fd8bf4e7b8bf526bf89051e83dActive Trader1536052.2034361536052.20343624242025-02-09 06:46:49.0002025-02-15 01:07:13.0006512017.401145333879730.3373022-4.656612873e-10
    14
    		0xf004c17147db78496d9b5f16e99838676ec728b2Casual Trader30006.75449551942.130118232025-01-27 07:00:14.0002025-02-03 05:14:53.000711706.9835161430.7142860021935.375623
    15
    		0x54d9faec00d973c93a62efd458ee271b7d1dbd00Casual Trader79669.8789669.8515142025-02-20 11:42:12.0002025-03-13 03:15:27.000218063.7961904761.380952169339.72299999.98
    16
    		0xad75aa8a906caa7c9495167d25518f06a194be26Casual Trader13111.99615875.69061214122025-01-19 08:37:36.0002025-03-05 16:00:04.00045644.17081360.5777785003.29407852763.694612
    17
    		0xe9deb769926abf11102d9d3dfbee72885bf494feCasual Trader6820.988821.98376542025-01-16 08:53:35.0002025-03-10 11:11:36.00053295.1502596230.1698112000.95230912001.00376
    18
    		0x08fb9cec97c8472b538b09adea4982fbd843503aCasual Trader4154.425154.41232024-11-24 01:54:09.0002025-02-18 14:35:19.00086108.2422093020.05814999.991999.99
    19
    		0xac33308f61c4b5b2a942292bc1864f6686da2af0Casual Trader49790.3516150009612025-02-13 09:27:37.0002025-03-14 10:01:38.000293441.3569520690.24137949790.351616218.64839
    20
    		0x57358a3280603f8863e0006c48c8ba869fd85d83Casual Trader110210222024-07-04 11:07:05.0002024-07-10 08:38:42.000653.3333333330.66666700100
    ...
    1268
    211KB
    4s