​
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 
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 
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 
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 
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 
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

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.)
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.
  • Limit losses levels
    • The query below give you levels where traders are more likely to sell to limit their losses.
-- 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.
-- 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
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;

​
HyperCore <> HyperEVM

​
Transfers

​
HyperCore -> HyperEVM

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 
SELECT *
FROM hyperliquid.raw.transactions
WHERE
  action:type = 'SystemSpotSendAction'
AND
  action:destination = '0x6b00f08f81d81fec5154b6e807acd4613cd16795'
LIMIT 2;

​
HyperEVM -> HyperCore

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. 
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.