-- Dynamic binning for Stop orders based on each coin's price range
WITH coin_price_ranges AS (
    SELECT 
        COIN,
        MIN(TRIGGER_PRICE) as min_price,
        MAX(TRIGGER_PRICE) as max_price,
        (MAX(TRIGGER_PRICE) - MIN(TRIGGER_PRICE))/20 as bin_size -- Create 20 bins across the range
    FROM hyperliquid.raw.orders
    WHERE IS_TAKE_PROFIT_OR_STOP_LOSS = TRUE
    AND TYPE IN ('Stop Market', 'Stop Limit')
    AND STATUS_CHANGE_TIMESTAMP >= DATEADD(hour, -24, CURRENT_TIMESTAMP())
    GROUP BY COIN
)

SELECT 
    o.COIN,
    'Stop Orders' as order_category,
    FLOOR(o.TRIGGER_PRICE / NULLIF(r.bin_size, 0)) * r.bin_size as price_bin_start,
    (FLOOR(o.TRIGGER_PRICE / NULLIF(r.bin_size, 0)) + 1) * r.bin_size as price_bin_end,
    COUNT(*) as order_count
FROM hyperliquid.raw.orders o
JOIN coin_price_ranges r ON o.COIN = r.COIN
WHERE o.IS_TAKE_PROFIT_OR_STOP_LOSS = TRUE
AND o.TYPE IN ('Stop Market', 'Stop Limit')
AND o.STATUS_CHANGE_TIMESTAMP >= DATEADD(hour, -24, CURRENT_TIMESTAMP())
GROUP BY o.COIN, order_category, FLOOR(o.TRIGGER_PRICE / NULLIF(r.bin_size, 0)) * r.bin_size, (FLOOR(o.TRIGGER_PRICE / NULLIF(r.bin_size, 0)) + 1) * r.bin_size
ORDER BY o.COIN, price_bin_start;

-- Dynamic binning for Take Profit orders based on each coin's price range
WITH coin_price_ranges AS (
    SELECT 
        COIN,
        MIN(TRIGGER_PRICE) as min_price,
        MAX(TRIGGER_PRICE) as max_price,
        (MAX(TRIGGER_PRICE) - MIN(TRIGGER_PRICE))/20 as bin_size -- Create 20 bins across the range
    FROM hyperliquid.raw.orders
    WHERE IS_TAKE_PROFIT_OR_STOP_LOSS = TRUE
    AND TYPE IN ('Take Profit Market', 'Take Profit Limit')
    AND STATUS_CHANGE_TIMESTAMP >= DATEADD(hour, -24, CURRENT_TIMESTAMP())
    GROUP BY COIN
)

SELECT 
    o.COIN,
    'Take Profit Orders' as order_category,
    FLOOR(o.TRIGGER_PRICE / NULLIF(r.bin_size, 0)) * r.bin_size as price_bin_start,
    (FLOOR(o.TRIGGER_PRICE / NULLIF(r.bin_size, 0)) + 1) * r.bin_size as price_bin_end,
    COUNT(*) as order_count
FROM hyperliquid.raw.orders o
JOIN coin_price_ranges r ON o.COIN = r.COIN
WHERE o.IS_TAKE_PROFIT_OR_STOP_LOSS = TRUE
AND o.TYPE IN ('Take Profit Market', 'Take Profit Limit')
AND o.STATUS_CHANGE_TIMESTAMP >= DATEADD(hour, -24, CURRENT_TIMESTAMP())
GROUP BY o.COIN, order_category, FLOOR(o.TRIGGER_PRICE / NULLIF(r.bin_size, 0)) * r.bin_size, (FLOOR(o.TRIGGER_PRICE / NULLIF(r.bin_size, 0)) + 1) * r.bin_size
ORDER BY o.COIN, price_bin_start;

WITH trading_pairs AS (
    SELECT 
        BUYER_ADDRESS,
        SELLER_ADDRESS,
        COIN,
        COUNT(*) as trade_count,
        SUM(USD_AMOUNT) as total_volume
    FROM hyperliquid.dex.trades
    WHERE TIMESTAMP >= DATEADD(day, -30, CURRENT_TIMESTAMP()) -- Last 30 days
    GROUP BY BUYER_ADDRESS, SELLER_ADDRESS, COIN
    HAVING COUNT(*) >= 10 -- At least 10 trades between the same pair
),
reverse_pairs AS (
    SELECT 
        a.BUYER_ADDRESS,
        a.SELLER_ADDRESS,
        a.COIN,
        a.trade_count as forward_count,
        b.trade_count as reverse_count,
        a.total_volume as forward_volume,
        b.total_volume as reverse_volume
    FROM trading_pairs a
    JOIN trading_pairs b ON a.BUYER_ADDRESS = b.SELLER_ADDRESS 
                         AND a.SELLER_ADDRESS = b.BUYER_ADDRESS
                         AND a.COIN = b.COIN
    WHERE a.BUYER_ADDRESS < a.SELLER_ADDRESS -- To avoid duplicate pairs
)

SELECT 
    BUYER_ADDRESS,
    SELLER_ADDRESS,
    COIN,
    forward_count,
    reverse_count,
    forward_count + reverse_count as total_trades,
    forward_volume,
    reverse_volume,
    forward_volume + reverse_volume as total_volume,
    ABS(forward_count - reverse_count) / (forward_count + reverse_count) as trade_count_imbalance,
    ABS(forward_volume - reverse_volume) / (forward_volume + reverse_volume) as volume_imbalance
FROM reverse_pairs
ORDER BY total_trades DESC;

SELECT 
    SELLER_ADDRESS,
    SUM(USD_AMOUNT) as TOTAL_SOLD_USD,
    COUNT(*) as NUMBER_OF_SALES
FROM hyperliquid.dex.trades
WHERE TIMESTAMP >= DATEADD(month, -1, CURRENT_TIMESTAMP())
  AND SELLER_ADDRESS IS NOT NULL
GROUP BY SELLER_ADDRESS
ORDER BY TOTAL_SOLD_USD DESC
LIMIT 20;

SELECT 
    BUYER_ADDRESS,
    SUM(USD_AMOUNT) as TOTAL_BOUGHT_USD,
    COUNT(*) as NUMBER_OF_PURCHASES
FROM hyperliquid.dex.trades
WHERE TIMESTAMP >= DATEADD(month, -1, CURRENT_TIMESTAMP())
  AND BUYER_ADDRESS IS NOT NULL
GROUP BY BUYER_ADDRESS
ORDER BY TOTAL_BOUGHT_USD DESC
LIMIT 20;

-- First, create a CTE (Common Table Expression) with top sellers
WITH top_sellers AS (
    SELECT 
        SELLER_ADDRESS as ADDRESS
    FROM hyperliquid.dex.trades
    WHERE TIMESTAMP >= DATEADD(month, -1, CURRENT_TIMESTAMP())
      AND SELLER_ADDRESS IS NOT NULL
    GROUP BY SELLER_ADDRESS
    ORDER BY SUM(USD_AMOUNT) DESC
    LIMIT 10
),
-- Create a CTE with top buyers
top_buyers AS (
    SELECT 
        BUYER_ADDRESS as ADDRESS
    FROM hyperliquid.dex.trades
    WHERE TIMESTAMP >= DATEADD(month, -1, CURRENT_TIMESTAMP())
      AND BUYER_ADDRESS IS NOT NULL
    GROUP BY BUYER_ADDRESS
    ORDER BY SUM(USD_AMOUNT) DESC
    LIMIT 10
),
-- Combine the two lists (with possible duplicates)
top_traders AS (
    SELECT ADDRESS FROM top_sellers
    UNION
    SELECT ADDRESS FROM top_buyers
)
-- Now query the orders table for these addresses
SELECT 
    o.CLIENT_ORDER_ID,
    o.ORDER_ID,
    o.COIN,
    o.IS_TAKE_PROFIT_OR_STOP_LOSS,
    o.IS_TRIGGER,
    o.TYPE,
    o.ORIGINAL_SIZE,
    o.IS_REDUCE_ONLY,
    o.SIDE,
    o.SIZE,
    o.TIME_IN_FORCE,
    o.ORDER_TIMESTAMP,
    o.TRIGGER_CONDITION,
    o.TRIGGER_PRICE,
    o.LIMIT_PRICE,
    o.CHILDREN,
    o.STATUS,
    o.USER,
    o.STATUS_CHANGE_TIMESTAMP
FROM hyperliquid.raw.orders o
JOIN top_traders t ON o.USER = t.ADDRESS
WHERE o.STATUS = 'open'
  AND o.ORDER_TIMESTAMP >= DATEADD(hour, -24, CURRENT_TIMESTAMP())
ORDER BY o.ORDER_TIMESTAMP DESC;

WITH buyer_pnl AS (
    SELECT 
        BUYER_ADDRESS AS ADDRESS,
        SUM(CAST(PARSE_JSON(_EXTRA_FIELDS):buyer:closed_pnl as FLOAT)) AS BUYER_TOTAL_PNL
    FROM hyperliquid.dex.trades
    WHERE _EXTRA_FIELDS IS NOT NULL
      AND PARSE_JSON(_EXTRA_FIELDS):buyer:closed_pnl IS NOT NULL
      AND BUYER_ADDRESS IS NOT NULL
      AND TIMESTAMP >= DATEADD(day, -7, CURRENT_TIMESTAMP())
    GROUP BY BUYER_ADDRESS
),

seller_pnl AS (
    SELECT 
        SELLER_ADDRESS AS ADDRESS,
        SUM(CAST(PARSE_JSON(_EXTRA_FIELDS):seller:closed_pnl as FLOAT)) AS SELLER_TOTAL_PNL
    FROM hyperliquid.dex.trades
    WHERE _EXTRA_FIELDS IS NOT NULL
      AND PARSE_JSON(_EXTRA_FIELDS):seller:closed_pnl IS NOT NULL
      AND SELLER_ADDRESS IS NOT NULL
      AND TIMESTAMP >= DATEADD(day, -7, CURRENT_TIMESTAMP())
    GROUP BY SELLER_ADDRESS
),

combined_pnl AS (
    SELECT 
        COALESCE(b.ADDRESS, s.ADDRESS) AS ADDRESS,
        COALESCE(b.BUYER_TOTAL_PNL, 0) AS BUYER_TOTAL_PNL,
        COALESCE(s.SELLER_TOTAL_PNL, 0) AS SELLER_TOTAL_PNL,
        COALESCE(b.BUYER_TOTAL_PNL, 0) + COALESCE(s.SELLER_TOTAL_PNL, 0) AS TOTAL_PNL
    FROM buyer_pnl b
    FULL OUTER JOIN seller_pnl s ON b.ADDRESS = s.ADDRESS
)

SELECT 
    ADDRESS,
    BUYER_TOTAL_PNL,
    SELLER_TOTAL_PNL,
    TOTAL_PNL,
    CASE 
        WHEN TOTAL_PNL > 0 THEN 'Profitable'
        WHEN TOTAL_PNL < 0 THEN 'Loss'
        ELSE 'Breakeven'
    END AS PERFORMANCE
FROM combined_pnl
ORDER BY TOTAL_PNL DESC
LIMIT 100;

{
  "buyer": {
    "builder_fee": null,
    "closed_pnl": "0.0",
    "crossed": true,
    "dir": "Open Long",
    "fee": "0.0",
    "fee_token": "USDC",
    "liquidation": null,
    "order_id": 80667495079,
    "start_position": "99492.0"
  },
  "seller": {
    "builder_fee": null,
    "closed_pnl": "0.0",
    "crossed": false,
    "dir": "Open Short",
    "fee": "0.0",
    "fee_token": "USDC",
    "liquidation": null,
    "order_id": 80667490005,
    "start_position": "-361760.0"
  },
  "source": "api.hyperliquid.xyz/info"
}

0x0000000000000000000000000000000000000000000000000000000000000000

SELECT DISTINCT status
FROM hyperliquid.raw.orders
ORDER BY status;