shreexchubby-fuchsia
    Updated 2025-01-15
    WITH a1 AS (
    SELECT
    SWAPPER,
    COUNT(*) AS buy_tx_count, -- Number of buy transactions
    SUM(SWAP_TO_AMOUNT) AS total_bought_tokens, -- Total tokens bought
    SUM(SWAP_TO_AMOUNT_USD) AS total_bought_usd, -- Total USD spent
    AVG(SWAP_TO_AMOUNT_USD / SWAP_TO_AMOUNT) AS avg_buy_price, -- Average buy price
    MIN(block_timestamp) AS first_buy_time, -- First buy time
    MAX(block_timestamp) AS last_buy_time -- Last buy time
    FROM solana.defi.ez_dex_swaps
    WHERE
    swap_to_mint = 'Dfh5DzRgSvvCFDoYc2ciTkMrbDfRKybA4SoFbPmApump' -- Specific token
    AND swap_from_mint = 'So11111111111111111111111111111111111111112' -- SOL token
    AND swap_to_amount >= 10000 -- Minimum buy amount
    GROUP BY swapper
    HAVING COUNT(*) <= 30 -- Maximum buy transactions
    ),

    a2 AS (
    SELECT
    s.swapper,
    COUNT(*) AS sell_tx_count, -- Number of sell transactions
    SUM(s.swap_from_amount) AS total_sold_tokens, -- Total tokens sold
    SUM(s.swap_from_amount_usd) AS total_sold_usd, -- Total USD gained
    AVG(s.swap_from_amount_usd / s.swap_from_amount) AS avg_sell_price, -- Average sell price
    MIN(s.block_timestamp) AS first_sell_time, -- First sell time
    MAX(s.block_timestamp) AS last_sell_time -- Last sell time
    FROM solana.defi.ez_dex_swaps s
    WHERE
    s.swap_from_mint = 'Dfh5DzRgSvvCFDoYc2ciTkMrbDfRKybA4SoFbPmApump' -- Specific token
    AND s.swap_to_mint = 'So11111111111111111111111111111111111111112' -- SOL token
    AND s.swapper IN (SELECT swapper FROM a1)
    AND s.swap_from_amount >= 1000 -- Minimum sell amount
    GROUP BY s.swapper
    ),

    Last run: 11 days ago
    SWAPPER
    TOTAL_BUY_TRANSACTIONS
    TOTAL_BOUGHT_TOKENS
    TOTAL_BOUGHT_USD
    AVG_BUY_PRICE
    FIRST_BUY_TIME
    LAST_BUY_TIME
    TOTAL_SELL_TRANSACTIONS
    TOTAL_SOLD_TOKENS
    TOTAL_SOLD_USD
    AVG_SELL_PRICE
    FIRST_SELL_TIME
    LAST_SELL_TIME
    NET_PROFIT_USD
    PROFIT_PERCENTAGE
    HOLDING_PERIOD_HOURS
    1
    HTvAE8YepV54MtdXs9dtR5iUG2qKXkQ2Xg9WZs439BQC114092.056242291.310.020671929992024-12-12 05:08:18.0002024-12-12 05:08:18.000221249.9056131323.530.080763923832024-12-12 10:00:22.0002025-01-15 10:01:31.0001032.22354.344.867778
    2
    Ft2SA8UFXXc2rok41BRwTzRRLySPEpGgwgA6VJudjAP3112225.5207851018.10.083276616022025-01-09 03:04:24.0002025-01-09 03:04:24.000217372.9438372975.10.17124885482025-01-10 02:24:40.0002025-01-10 02:24:40.0001957192.2223.337778
    3
    4SrjtuT4c77S7efsTHr1k6JpTC17mnQmTpqLvzVijT7V233241.8653181569.750.047328084592025-01-07 05:10:49.0002025-01-07 06:52:06.000426723.1036353004.490.17677229252025-01-07 10:27:54.0002025-01-11 16:49:53.0001434.7491.43.596667
    4
    4kec1ArWTjt7sQYgeLDcc7JKfq5TE3JEW78Z8qzpi1o9339616.9639768731.70.22024485372025-01-11 16:11:13.0002025-01-11 22:04:37.000668296.01860914994.930.20506795082025-01-09 18:12:53.0002025-01-11 22:45:51.0006263.2371.73-51.862222
    5
    8vycunCcTX3pRijftYMHTZZ8qk1XYbZ9Mx38QPDiMnpf239481.4045439011.310.22824187982025-01-10 21:32:05.0002025-01-10 21:33:32.000139481.40454312108.510.30668893722025-01-11 04:58:27.0002025-01-11 04:58:27.0003097.234.377.415278
    6
    5ijpLWTAEVhMr8LPciEnbrFv3oCPmxv9a6wn3VbUBLCS113359.6380032222.590.16636603472025-01-13 15:23:36.0002025-01-13 15:23:36.000415497.7246124043.060.21778746632025-01-10 03:28:01.0002025-01-14 00:36:42.0001820.4781.91-83.926389
    7
    8rLDKJsWKkSYoaLCakvAvucVRhMu39Ng9G5xReeHDWFP122963.606028586.950.025560010012025-01-05 05:19:59.0002025-01-05 05:19:59.0001022493.2185514872.070.21661986552025-01-09 06:38:22.0002025-01-13 05:25:54.0004285.12730.0797.306389
    8
    FcBF6h8DDsbJRDRYqa4Z5DZCRvZKgDmWsSg5bC5D9a2N142183.9205751720.20.040778570992025-01-05 14:20:35.0002025-01-05 14:20:35.000315100.5836093508.210.23195329042025-01-11 03:47:17.0002025-01-12 07:53:07.0001788.01103.94133.445
    9
    9oEphWcFbZ8UGCEL2z6s6bTrcTELCnFBurcBU6dgS6RT120412.57010815060.073778068712025-01-08 23:05:51.0002025-01-08 23:05:51.0008100836.3136824051.120.21557241952025-01-11 04:59:39.0002025-01-14 22:06:09.00022545.121497.0253.896667
    10
    EmJpM5yFv56LBMUvYtxoBdncTWdEb3y6cdBGXauwJdrb7221021.10478321792.930.13740134192024-11-10 21:40:37.0002025-01-14 16:41:20.0004314038.70810429756.860.13183692082024-11-12 21:19:02.0002025-01-14 20:29:50.0007963.9336.54-1507.371667
    11
    9YbXS3JjQnkAU2NLs1iH1bqqEZruHUZD75jDZBQiZ1LF4160329.33495635092.910.21907456892025-01-11 13:32:52.0002025-01-11 22:20:57.0007497127.70676459125.750.18523632982024-12-06 03:13:46.0002025-01-14 03:29:55.00024032.8468.48-883.119722
    12
    7Rh4mqYAv7isqgs3fUGQSnYcjYCXM3hv7QKBUSqFFNaq7119850.5767454341.90.040942542442024-11-24 03:32:43.0002025-01-07 08:51:10.00049511554.7370634372.710.11899765352024-11-10 10:02:51.0002025-01-14 05:46:31.00030030.81691.65-1390.805278
    13
    4PbVSywb9LiwA9SnKBNLS2gc3Q8A6Qvp21D36mHn7vJS121025930.3972987448.660.036791615412024-11-09 20:50:14.0002025-01-08 10:27:39.0005899150.29440527312.30.27698205752024-11-09 20:52:52.0002025-01-11 02:09:05.00019863.64266.67-1429.579722
    14
    BKQjXfsEjzLRCgxqvMUvqQuyyXeJHdU8vQUQYGibzywx356624.8754285846.80.11820206582025-01-05 11:17:42.0002025-01-10 03:35:40.000659637.8740229285.80.19971005312025-01-06 13:27:28.0002025-01-11 17:30:45.000343958.82-86.136667
    15
    GVxsmnqXr4F1cRisYbyFK3xQdVt6yreryU2abRsVsmXo128412.6697515952.140.20948893762025-01-13 03:11:30.0002025-01-13 03:11:30.0002109937.11776314544.90.13230199742025-01-15 03:23:13.0002025-01-15 03:24:02.0008592.76144.3648.195278
    16
    6CnM8xjrbARJJCGyvz1UdeCpeQtQPRJpFkSAjrxQa71h2162268.4694792883.340.029980382862025-01-04 11:40:30.0002025-01-07 12:32:23.0005152532.38008932281.820.20670794642025-01-10 15:00:45.0002025-01-13 07:17:04.00029398.481019.674.472778
    17
    DWAzagJzCyHF1CMLfmEdgmXhiTRakiuTYA2ii7GujFg44113283.3188323600.820.045995535612024-11-14 02:20:57.0002025-01-07 07:47:25.000698597.562237917.680.16326208722024-12-16 07:00:29.0002025-01-11 01:43:32.0004316.86119.89-528.782222
    18
    5qDJ1og6nUnmUA6fMfFuNBM4qZhe3L1SQp8wg3VinFWE6311174.98012912022.960.052584829532024-11-11 13:44:51.0002024-11-19 09:37:59.0009343105.68655429177.290.088512234982024-11-14 12:02:06.0002025-01-10 23:08:21.00017154.33142.68-117.598056
    19
    27QgdjbirX7MsR6WrQHGQJ687Kqi97gz6HYVBJw6tin4111977922.748631130157.220.086179339442025-01-02 14:04:42.0002025-01-10 04:19:20.000151358809.33485171036.280.19344850642025-01-03 15:00:43.0002025-01-11 04:58:38.00040879.0631.41-157.310278
    20
    3EPVsKep21E9xMSrUdhzZJpaCBUr45WBCszvdoyVr54u461318.05595413251.430.21700345212025-01-10 11:58:25.0002025-01-10 13:37:50.000683738.75339819691.160.2351498172025-01-11 21:42:59.0002025-01-11 21:52:05.0006439.7348.632.085833
    ...
    1518
    373KB
    52s