Orderbook Snapshots

(snapshot_id, coin, order_timestamp, order_id)

WITH latest AS (
  SELECT snapshot_id
  FROM hyperliquid.raw.orderbook_snapshots
  WHERE coin = 'BTC'
  QUALIFY ROW_NUMBER() OVER (ORDER BY snapshot_timestamp DESC) = 1
)
SELECT side, limit_price, size, user, order_timestamp
FROM hyperliquid.raw.orderbook_snapshots
WHERE coin = 'BTC'
  AND snapshot_id = (SELECT snapshot_id FROM latest)
ORDER BY side, limit_price::float DESC;

SELECT
  snapshot_timestamp,
  size,
  limit_price,
  trigger_condition
FROM hyperliquid.raw.orderbook_snapshots
WHERE coin = 'BTC'
  AND user = '0x...'
  AND order_id = '414287276291'
ORDER BY snapshot_timestamp;

SELECT
  snapshot_id,
  snapshot_timestamp,
  max_order_timestamp,
  DATEDIFF('second', max_order_timestamp, snapshot_timestamp) AS lag_seconds
FROM hyperliquid.raw.orderbook_snapshots
WHERE snapshot_timestamp > DATEADD(hour, -3, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3
ORDER BY 2 DESC;

-- Reconstruct BTC's book as of "now" from the latest snapshot,
-- removing orders that have since terminated, adjusting size for
-- partial fills, and adding orders placed after the snapshot.
WITH latest AS (
  SELECT snapshot_id, snapshot_timestamp, max_order_timestamp
  FROM hyperliquid.raw.orderbook_snapshots
  WHERE coin = 'BTC'
  QUALIFY ROW_NUMBER() OVER (ORDER BY snapshot_timestamp DESC) = 1
),

-- User-maintained list — keep current with Hyperliquid's status taxonomy.
terminal_statuses(status) AS (
  SELECT column1 FROM VALUES
    ('Filled'), ('Canceled'), ('MarginCanceled'), ('Liquidated'),
    ('SelfTradeCanceled'), ('ReduceOnlyCanceled'),
    ('VaultWithdrawalCanceled'), ('OpenInterestCapCanceled'),
    ('DelistedCanceled'), ('SiblingFilledCanceled'), ('ScheduledCancel'),
    ('Rejected'), ('TickRejected'), ('MinTradeNtlRejected'),
    ('PerpMarginRejected'), ('ReduceOnlyRejected'),
    ('BadAloPxRejected'), ('IocCancelRejected'),
    ('BadTriggerPxRejected'), ('MarketOrderNoLiquidityRejected')
),

-- Latest status per order since the snapshot.
order_status_since AS (
  SELECT o.user, o.order_id, o.order_timestamp,
         o.side, o.limit_price, o.size, o.status
  FROM hyperliquid.raw.orders o, latest
  WHERE o.coin = 'BTC'
    AND o.status_change_timestamp > latest.snapshot_timestamp
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY o.user, o.order_id, o.order_timestamp
    ORDER BY o.status_change_timestamp DESC
  ) = 1
),

-- Snapshot rows whose orders are still resting (didn't go terminal).
snapshot_survivors AS (
  SELECT obs.user, obs.order_id, obs.order_timestamp,
         obs.side, obs.limit_price, obs.size
  FROM hyperliquid.raw.orderbook_snapshots obs, latest
  WHERE obs.coin = 'BTC' AND obs.snapshot_id = latest.snapshot_id
    AND NOT EXISTS (
      SELECT 1 FROM order_status_since s
      WHERE s.user = obs.user
        AND s.order_id = obs.order_id
        AND s.order_timestamp = obs.order_timestamp
        AND s.status IN (SELECT status FROM terminal_statuses)
    )
),

-- Net fill size against each surviving order since the snapshot.
fills_since AS (
  SELECT user, order_id, SUM(size::number(38,10)) AS filled_size
  FROM hyperliquid.raw.fills, latest
  WHERE coin = 'BTC' AND timestamp > latest.snapshot_timestamp
  GROUP BY user, order_id
),

-- Snapshot-side resting orders, with size adjusted for partial fills.
adjusted_snapshot AS (
  SELECT s.user, s.order_id, s.order_timestamp, s.side, s.limit_price,
         GREATEST(s.size::number(38,10) - COALESCE(f.filled_size, 0), 0) AS size
  FROM snapshot_survivors s
  LEFT JOIN fills_since f USING (user, order_id)
),

-- Orders that appeared in the book after the snapshot.
new_orders AS (
  SELECT s.user, s.order_id, s.order_timestamp,
         s.side, s.limit_price, s.size
  FROM order_status_since s, latest
  WHERE s.order_timestamp > latest.max_order_timestamp
    AND s.status NOT IN (SELECT status FROM terminal_statuses)
)

SELECT * FROM adjusted_snapshot WHERE size > 0
UNION ALL
SELECT * FROM new_orders;