How has Hyperliquid open interest changed relative to perpetual trading volume?

-- Recurring Hyperliquid first-perp-DEX series. HIP-3 builder-deployed perp
-- DEXs are not present in hyperliquid.market_data and remain excluded.
-- Anchor on the latest observed 23:59 UTC snapshot, not current_date: the
-- source can lag wall-clock time. day_ntl_vlm is trailing-24h and is never
-- summed across intraday snapshots.
WITH latest_complete_day AS (
  SELECT CAST(date_trunc('day', MAX(time)) AS date) AS end_day
  FROM hyperliquid.market_data
  WHERE hour(time) = 23
    AND minute(time) = 59
),
params AS (
  SELECT
    CAST(end_day - INTERVAL '179' DAY AS date) AS start_day,
    end_day
  FROM latest_complete_day
),
date_spine AS (
  SELECT day
  FROM params
  CROSS JOIN UNNEST(sequence(start_day, end_day, INTERVAL '1' DAY)) AS t(day)
),
scoped AS (
  SELECT
    time,
    CAST(date_trunc('day', time) AS date) AS day,
    coin,
    open_interest,
    mark_px,
    day_ntl_vlm
  FROM hyperliquid.market_data
  CROSS JOIN params
  WHERE time >= CAST(start_day AS timestamp)
    AND time < CAST(end_day + INTERVAL '1' DAY AS timestamp)
),
daily_quality AS (
  SELECT
    day,
    COUNT(DISTINCT time) AS snapshot_count,
    MAX(time) AS latest_snapshot_time
  FROM scoped
  GROUP BY 1
),
usable_daily_snapshot AS (
  SELECT day, latest_snapshot_time
  FROM daily_quality
  WHERE hour(latest_snapshot_time) = 23
    AND minute(latest_snapshot_time) = 59
),
daily_metrics AS (
  SELECT
    u.day,
    u.latest_snapshot_time AS snapshot_time,
    COUNT(DISTINCT s.coin) AS market_count,
    SUM(s.day_ntl_vlm) AS trailing_24h_volume_usd,
    SUM(s.open_interest * s.mark_px) AS open_interest_usd
  FROM usable_daily_snapshot u
  JOIN scoped s ON s.time = u.latest_snapshot_time
  GROUP BY 1, 2
)
SELECT
  d.day,
  m.snapshot_time,
  COALESCE(q.snapshot_count, 0) AS snapshot_count,
  q.snapshot_count = 1440 AND m.snapshot_time IS NOT NULL AS data_complete,
  m.snapshot_time IS NOT NULL AS data_usable,
  CASE
    WHEN q.day IS NULL THEN 'missing_day'
    WHEN m.snapshot_time IS NULL THEN 'incomplete_day_end'
    WHEN q.snapshot_count <> 1440 THEN 'usable_with_intraday_gap'
    ELSE 'complete'
  END AS data_status,
  m.market_count,
  m.trailing_24h_volume_usd,
  m.open_interest_usd,
  m.open_interest_usd / NULLIF(m.trailing_24h_volume_usd, 0) AS oi_to_volume_ratio
FROM date_spine d
LEFT JOIN daily_quality q ON q.day = d.day
LEFT JOIN daily_metrics m ON m.day = d.day
ORDER BY d.day ASC