> ## Documentation Index
> Fetch the complete documentation index at: https://docs.allium.so/llms.txt
> Use this file to discover all available pages before exploring further.

# Sample Queries

> Sample queries for Hyperliquid

# General

Sample queries for Hyperliquid historical data. This queries are applicable to Hyperliquid data on Snowflake dat awarehouse.

**Get total monthly DEX volume on Hyperliquid over time**

<Frame>
  <img src="https://mintcdn.com/allium-e770e2b7/M3SuvBIUs-0g-3vo/images/image-historical-hyperliquid.avif?fit=max&auto=format&n=M3SuvBIUs-0g-3vo&q=85&s=bc0c9ec13383e4d806dbe0c3237caf11" alt="" width="768" height="456" data-path="images/image-historical-hyperliquid.avif" />
</Frame>

```sql theme={null}
SELECT
    DATE_TRUNC('MONTH', timestamp) AS month,
    token_a_symbol,
    SUM(usd_amount) AS volume_usd
FROM hyperliquid.dex.trades
GROUP BY ALL
```

**Get all available trades for a given user**

```sql theme={null}
select
    *
from hyperliquid.dex.trades
where 1=1
    and (buyer_address = <address_to_search> or seller_address = <address_to_search>)
```

**Get all trades of BTC in the last 24 hours**

```sql theme={null}
select
    *
from hyperliquid.dex.trades
where 1=1
    and token_a_symbol = 'BTC'
    and timestamp >= current_timestamp - interval '24 hours'
```

**Get a list of tokens traded in the last 24 hours, with a breakdown of spot vs perpertuals volume, ordered by total USD volume traded**

```sql theme={null}
select
    token_a_symbol,
    sum(case when market_type = 'spot' then usd_amount else 0 end) as total_spot_usd_amount,
    sum(case when market_type = 'perpetuals' then usd_amount else 0 end) as total_perpetuals_usd_amount,
    sum(usd_amount) as total_usd_amount,
from hyperliquid.dex.trades
where 1=1
    and timestamp >= current_timestamp - interval '24 hours'
group by all
order by total_usd_amount desc
```

**Get daily activity metrics, aggregated by token, for the last 7 days**

```sql theme={null}
select
    date(timestamp) as day,
    token_a_symbol,
    count(distinct buyer_address) as num_buyers,
    count(distinct seller_address) as num_sellers,
    sum(usd_amount) as total_usd_amount,
from hyperliquid.dex.trades
where 1=1
    and timestamp >= current_timestamp - interval '7 days'
group by all
order by 1 asc
```

# By Use Case

<Tabs>
  <Tab title="Market Structure Insights">
    We can use some simple queries to view market resistance levels for the last 24 hours. (For more sophisticated clustering consider exporting the data and usind statiscal tools to identify clusters.)

    <Note>
      Please be advised that this data is currently incomplete as open orders are still missing. We are working to complete this data set. However this will give you some sense of the levels.
    </Note>

    * Limit losses levels
      * The query below give you levels where traders are more likely to sell to limit their losses.

    ```sql theme={null}
    -- 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;
    ```

    * Take Profit Levels
      * This will give you the levels where traders are more likely to take profits.

    ```sql theme={null}
    -- 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;
    ```

    * Circular Trading - two addresses trading back and forth

    ```sql theme={null}
    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;
    ```
  </Tab>

  <Tab title="Behaviour Analysis">
    ## **Monitor big movements**

    ### **Predict what is happening in the market**

    * Identify top sellers in the last month

    ```sql theme={null}
    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;
    ```

    * Identify Top Buyers

    ```sql theme={null}
    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;
    ```

    * Monitor the above addresses for open trades (coming soon)

    <Info>
      For now you can view what these whales have been doing by removing the o.STATUS = 'open', which will show you all the order status changes, so will capture a lot of the movement, but any orders that were placed and have not been cancelled, modified or filled, will not be visible.
    </Info>

    ```sql theme={null}
    -- 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;
    ```

    * Most profitable traders

    ```sql theme={null}
    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;
    ```
  </Tab>

  <Tab title="Liquidity Movement Patterns">
    ## Builder Fees

    * Average fee by builder address

    ```sql theme={null}
    -- avg fee by builder address
    SELECT 
        action:builder:b as builder_address,
        COUNT(*) as transaction_count,
        AVG(action:builder:f) as average_fee
    FROM hyperliquid.raw.transactions
    WHERE action:builder:b IS NOT NULL
    GROUP BY action:builder:b
    ORDER BY average_fee DESC;
    ```

    * **Min, Max and Avg - Max Builder Fee per builder builder**

    ```sql theme={null}
    SELECT 
        action:builder as builder_address,
       
        MIN(
            TRY_TO_DECIMAL(
                REPLACE(
                    REPLACE(action:maxFeeRate::string, '%', ''),
                    ' ', ''
                )
            ) / 100
        ) as min_max_fee_rate,
        
        MAX(
            TRY_TO_DECIMAL(
                REPLACE(
                    REPLACE(action:maxFeeRate::string, '%', ''),
                    ' ', ''
                )
            ) / 100
        ) as max_max_fee_rate,
        
        AVG(
            TRY_TO_DECIMAL(
                REPLACE(
                    REPLACE(action:maxFeeRate::string, '%', ''),
                    ' ', ''
                )
            ) / 100
        ) as avg_max_fee_rate,
        
        COUNT(*) as approval_count,
        MIN(action:nonce) as first_approval,
        MAX(action:nonce) as latest_approval
    FROM hyperliquid.raw.transactions
    WHERE action:type::string = 'approveBuilderFee'
      AND action:maxFeeRate IS NOT NULL
    GROUP BY builder_address
    ORDER BY avg_max_fee_rate DESC;
    ```

    ## Vaults

    * Transfers, deposits by vault and user

    ```
    SELECT 
        user,
        action:vaultAddress AS vault_address,
        SUM(CASE WHEN action:isDeposit = true THEN action:usd ELSE 0 END) AS total_deposits,
        SUM(CASE WHEN action:isDeposit = false THEN action:usd ELSE 0 END) AS total_withdrawals
    FROM hyperliquid.raw.transactions
    WHERE action:type = 'vaultTransfer'
    GROUP BY user, vault_address
    ORDER BY user, vault_address;
    ```

    * Transfers and deposits by vault

    ```
    SELECT 
        action:vaultAddress AS vault_address,
        SUM(CASE WHEN action:isDeposit = true THEN action:usd ELSE 0 END) AS total_deposits,
        SUM(CASE WHEN action:isDeposit = false THEN action:usd ELSE 0 END) AS total_withdrawals,
        COUNT(DISTINCT user) AS users
    FROM hyperliquid.raw.transactions
    WHERE action:type = 'vaultTransfer'
    GROUP BY vault_address
    ORDER BY total_deposits DESC;
    ```
  </Tab>
</Tabs>

# HyperCore \<> HyperEVM

## Transfers

### HyperCore -> HyperEVM

```shellscript theme={null}
SELECT *
FROM hyperliquid.raw.transactions
WHERE
  action:type = 'SystemSpotSendAction'
LIMIT 2;
```

The `action:destination` will be the user address it is going to.

So to filter by user you would do

```shellscript theme={null}
SELECT *
FROM hyperliquid.raw.transactions
WHERE
  action:type = 'SystemSpotSendAction'
AND
  action:destination = '0x6b00f08f81d81fec5154b6e807acd4613cd16795'
LIMIT 2;
```

### HyperEVM -> HyperCore

```shellscript theme={null}
SELECT *
FROM hyperliquid.raw.transactions
WHERE
  action:type = 'spotSend'
AND
(
  action:destination like '0x20%'
  OR
  action:destination = '0x2222222222222222222222222222222222222222'
)
LIMIT 2;
```

> Every token has a system address on the Core, which is the address with first byte `0x20` and the remaining bytes all zeros, except for the token index encoded in big-endian format.
>
> The exception is HYPE, which has a system address of `0x2222222222222222222222222222222222222222` .

To filter by user, use the user column, as the token will be deposited in the address of the user who intiated the transaction.

```shellscript theme={null}
SELECT *
FROM hyperliquid.raw.transactions
WHERE
  action:type = 'spotSend'
AND
(
  action:destination like '0x20%'
  OR
  action:destination = '0x2222222222222222222222222222222222222222'
)
AND
  user = '0xc0617cf0557378d4d53fd17320ae4e6e2c27e468'
LIMIT 2;
```

### Mapping tokens

Coming soon.