DATE | TOKEN_SYMBOL | DAILY_USD_VOLUME | AVG_TOKEN_PRICE_USD | |
|---|---|---|---|---|
1 | 2024-02-02 00:00:00.000 | APT | 1319275.37175556 | 0 |
2 | 2024-02-02 00:00:00.000 | BTC | 8437823.18496102 | 0 |
3 | 2024-02-02 00:00:00.000 | SOL | 33270880.5944394 | 39.885689337 |
4 | 2024-02-02 00:00:00.000 | ARB | 4397886.3504167 | 0.0008050731727 |
5 | 2024-02-02 00:00:00.000 | UNDEFINED | 1009662.49947643 | 0 |
6 | 2024-02-02 00:00:00.000 | ETH | 8163684.95791914 | 2301.099145663 |
7 | 2024-02-03 00:00:00.000 | APT | 1200978.83010078 | 0 |
8 | 2024-02-03 00:00:00.000 | BTC | 7328486.99131679 | 0 |
9 | 2024-02-03 00:00:00.000 | ETH | 7078707.28340914 | 2307.560575941 |
10 | 2024-02-03 00:00:00.000 | UNDEFINED | 907020.108570557 | 0 |
11 | 2024-02-03 00:00:00.000 | ARB | 3424344.130802 | 0.0007852692837 |
12 | 2024-02-03 00:00:00.000 | SOL | 27304061.5240054 | 39.553875346 |
13 | 2024-02-04 00:00:00.000 | BTC | 7903323.42977498 | 0 |
14 | 2024-02-04 00:00:00.000 | JTO | 5577945.89594315 | 1.876082372 |
15 | 2024-02-04 00:00:00.000 | UNDEFINED | 35953.085627891 | 0 |
16 | 2024-02-04 00:00:00.000 | PYTH | 4025365.63341548 | 0.4943890218 |
17 | 2024-02-04 00:00:00.000 | APT | 1018280.41824948 | 0 |
18 | 2024-02-04 00:00:00.000 | ONEMBONK | 5517048.59283392 | 0 |
19 | 2024-02-04 00:00:00.000 | ETH | 7630836.32911417 | 2297.307753113 |
20 | 2024-02-04 00:00:00.000 | JUP | 10916309.1602593 | 0.5564621496 |
MetaLightZeta Markets Volume
Updated 2025-01-22
99
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
›
⌄
WITH zeta_transactions AS (
SELECT
INSERTED_TIMESTAMP::date AS date,
ARRAY_TO_STRING(OBJECT_KEYS(PARSE_JSON(DECODED_ARGS:"asset")), '') AS token_symbol,
CAST(DECODED_ARGS:"price" AS FLOAT) / 1e6 AS price, -- Convert price scaling
CAST(DECODED_ARGS:"size" AS FLOAT) / 1e6 AS size -- Convert size scaling
FROM solana.core.ez_events_decoded
WHERE PROGRAM_ID = 'ZETAxsqBRek56DhiGXrn75yj2NHU3aYUnxvHXpkf3aD'
AND INSERTED_TIMESTAMP BETWEEN '2024-02-02' AND '2024-07-31'
AND EVENT_TYPE = 'placePerpOrderV4'
),
parsed_transactions AS (
SELECT
date,
token_symbol,
price,
size,
price * size AS transaction_usd_volume
FROM zeta_transactions
),
price_data AS (
SELECT
DATE_TRUNC('day', HOUR) AS date,
SYMBOL AS token_symbol,
AVG(PRICE) AS avg_token_price_usd
FROM solana.price.ez_prices_hourly
WHERE DATE_TRUNC('day', HOUR) BETWEEN '2024-01-01' AND '2024-07-31'
GROUP BY DATE_TRUNC('day', HOUR), SYMBOL
)
SELECT
pt.date,
pt.token_symbol,
SUM(pt.transaction_usd_volume) AS daily_usd_volume,
COALESCE(pd.avg_token_price_usd, 0) AS avg_token_price_usd
FROM parsed_transactions pt
LEFT JOIN price_data pd
Last run: 3 months ago
...
2532
145KB
61s