hyperliquid-stats/tables.sql at main · thunderhead-labs/hyperliquid-stats · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
CREATE TABLE IF NOT EXISTS public.liquidations
(
    "time" timestamp with time zone NOT NULL,
    "user" character varying(255) COLLATE pg_catalog."default" NOT NULL,
    liquidated_ntl_pos double precision NOT NULL,
    liquidated_account_value double precision NOT NULL,
    leverage_type character varying(255) COLLATE pg_catalog."default" NOT NULL
);

CREATE TABLE IF NOT EXISTS public.non_mm_ledger_updates
(
    "time" timestamp with time zone NOT NULL,
    "user" character varying(255) COLLATE pg_catalog."default" NOT NULL,
    delta_usd double precision NOT NULL
);

CREATE TABLE IF NOT EXISTS public.non_mm_trades
(
    "time" timestamp with time zone NOT NULL,
    "user" character varying(255) COLLATE pg_catalog."default" NOT NULL,
    coin character varying(255) COLLATE pg_catalog."default" NOT NULL,
    side character varying(255) COLLATE pg_catalog."default" NOT NULL,
    px double precision NOT NULL,
    sz double precision NOT NULL,
    crossed boolean NOT NULL,
    special_trade_type character varying(255) COLLATE pg_catalog."default" NOT NULL
);

-- create indexes for liquidations table
CREATE INDEX IF NOT EXISTS idx_liquidations_time ON public.liquidations ("time");
CREATE INDEX IF NOT EXISTS idx_liquidations_user ON public.liquidations ("user");

-- create indexes for non_mm_ledger_updates table
CREATE INDEX IF NOT EXISTS idx_ledger_updates_time ON public.non_mm_ledger_updates ("time");
CREATE INDEX IF NOT EXISTS idx_ledger_updates_user ON public.non_mm_ledger_updates ("user");

-- create indexes for non_mm_trades table
CREATE INDEX IF NOT EXISTS idx_trades_time ON public.non_mm_trades ("time");
CREATE INDEX IF NOT EXISTS idx_trades_user ON public.non_mm_trades ("user");

CREATE TABLE IF NOT EXISTS public.non_mm_trades_cache
(
    "time" timestamp NOT NULL,
    "user" character varying(255) COLLATE pg_catalog."default" NOT NULL,
    coin character varying(255) COLLATE pg_catalog."default" NOT NULL,
    side character varying(255) COLLATE pg_catalog."default" NOT NULL,
    crossed boolean NOT NULL,
    special_trade_type character varying(255) COLLATE pg_catalog."default" NOT NULL,
    mean_px double precision NOT NULL,
    sum_sz double precision NOT NULL,
    usd_volume double precision NOT NULL,
    group_count integer NOT NULL
);

CREATE INDEX idx_non_mm_trades_cache
ON public.non_mm_trades_cache ("time", "user", coin, side, crossed);


CREATE TABLE IF NOT EXISTS public.non_mm_ledger_updates_cache
(
    "time" timestamp NOT NULL,
    "user" character varying(255) COLLATE pg_catalog."default" NOT NULL,
    sum_delta_usd double precision NOT NULL
);

CREATE INDEX idx_non_mm_ledger_updates_cache
ON public.non_mm_ledger_updates_cache ("time", "user");


CREATE TABLE IF NOT EXISTS public.liquidations_cache
(
    "time" timestamp NOT NULL,
    "user" character varying(255) COLLATE pg_catalog."default" NOT NULL,
    leverage_type character varying(255) COLLATE pg_catalog."default" NOT NULL,
    sum_liquidated_ntl_pos double precision NOT NULL,
    sum_liquidated_account_value double precision NOT NULL
);

CREATE INDEX idx_liquidations_cache
ON public.liquidations_cache ("time", "user", leverage_type);

CREATE TABLE account_values (
    "time" TIMESTAMP WITH TIME ZONE NOT NULL,
    "user" VARCHAR(255) NOT NULL,
    is_vault BOOLEAN NOT NULL,
    account_value FLOAT NOT NULL,
    cum_vlm FLOAT NOT NULL,
    cum_ledger FLOAT NOT NULL
);

CREATE INDEX idx_userdata_time ON account_values ("time");
CREATE INDEX idx_userdata_user ON account_values ("user");

CREATE TABLE IF NOT EXISTS public.account_values_cache
(
    "time" timestamp NOT NULL,
    "user" character varying(255) COLLATE pg_catalog."default" NOT NULL,
    is_vault BOOLEAN NOT NULL,
    last_account_value double precision NOT NULL,
    last_cum_vlm double precision NOT NULL,
    last_cum_ledger double precision NOT NULL
);

CREATE INDEX idx_account_values_cache
ON public.account_values_cache ("time", "user", is_vault);

CREATE TABLE funding (
    "time" TIMESTAMP WITH TIME ZONE NOT NULL,
    coin character varying(255) COLLATE pg_catalog."default" NOT NULL,
    funding FLOAT NOT NULL,
    premium FLOAT NOT NULL
);

CREATE INDEX idx_assetdata_time ON funding ("time");
CREATE INDEX idx_assetdata_asset ON funding (coin);

CREATE TABLE IF NOT EXISTS public.funding_cache
(
    "time" timestamp NOT NULL,
    coin character varying(255) COLLATE pg_catalog."default" NOT NULL,
    sum_funding double precision NOT NULL,
    sum_premium double precision NOT NULL
);

CREATE INDEX idx_funding_cache
ON public.funding_cache ("time", coin);

CREATE TABLE IF NOT EXISTS public.asset_ctxs
(
    "time" timestamp with time zone NOT NULL,
    "coin" character varying(255) COLLATE pg_catalog."default" NOT NULL,
    "funding" double precision NOT NULL,
    "open_interest" double precision NOT NULL,
    "prev_day_px" double precision NOT NULL,
    "day_ntl_vlm" double precision NOT NULL,
    "premium" double precision NOT NULL,
    "oracle_px" double precision NOT NULL,
    "mark_px" double precision NOT NULL,
    "mid_px" double precision NOT NULL,
    "impact_bid_px" double precision NOT NULL,
    "impact_ask_px" double precision NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_asset_ctxs_time ON public.asset_ctxs ("time");
CREATE INDEX IF NOT EXISTS idx_asset_ctxs_coin ON public.asset_ctxs ("coin");

CREATE TABLE IF NOT EXISTS public.asset_ctxs_cache
(
    "time" timestamp NOT NULL,
    "coin" character varying(255) COLLATE pg_catalog."default" NOT NULL,
    sum_funding double precision NOT NULL,
    avg_open_interest double precision NOT NULL,
    avg_prev_day_px double precision NOT NULL,
    sum_day_ntl_vlm double precision NOT NULL,
    avg_premium double precision NOT NULL,
    avg_oracle_px double precision NOT NULL,
    avg_mark_px double precision NOT NULL,
    avg_mid_px double precision NOT NULL,
    avg_impact_bid_px double precision NOT NULL,
    avg_impact_ask_px double precision NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_asset_ctxs_cache_time ON public.asset_ctxs_cache ("time");
CREATE INDEX IF NOT EXISTS idx_asset_ctxs_cache_coin ON public.asset_ctxs_cache ("coin");

CREATE TABLE market_data (
    time TIMESTAMP WITH TIME ZONE NOT NULL,
    ver_num INTEGER NOT NULL,
    channel VARCHAR(255) NOT NULL,
    coin VARCHAR(255) NOT NULL,
    raw_time BIGINT NOT NULL,
    liquidity double precision NOT NULL,
    levels JSON NOT NULL
);

CREATE INDEX idx_market_data_time ON market_data(time);
CREATE INDEX idx_market_data_channel ON market_data(channel);
CREATE INDEX idx_market_data_coin ON market_data(coin);

CREATE TABLE market_data_cache (
    time DATE NOT NULL,
    coin VARCHAR(255) NOT NULL,
    mid_price DOUBLE PRECISION NOT NULL,
    median_liquidity DOUBLE PRECISION NOT NULL,
    median_slippage_0 DOUBLE PRECISION NOT NULL,
    median_slippage_1000 DOUBLE PRECISION NOT NULL,
    median_slippage_3000 DOUBLE PRECISION NOT NULL,
    median_slippage_10000 DOUBLE PRECISION NOT NULL
);

CREATE INDEX idx_market_data_cache_time ON market_data_cache(time);
CREATE INDEX idx_market_data_cache_coin ON market_data_cache(coin);