dannyamahVertex Weekly Trading Volume
Updated 2024-08-08
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 Vertex AS (
SELECT
date_trunc(week, block_timestamp) AS date,
trader,
tx_hash,
amount_usd,
fee_amount,
'Perp Trade' AS type
FROM arbitrum.vertex.ez_perp_trades
WHERE is_taker = TRUE
UNION ALL
SELECT
date_trunc(week, block_timestamp) AS date,
trader,
tx_hash,
amount_usd,
fee_amount,
'Spot Trade' AS type
FROM arbitrum.vertex.ez_spot_trades
WHERE is_taker = TRUE
)
SELECT
date,
COUNT(DISTINCT CASE WHEN type = 'Perp Trade' THEN trader END) AS "Perp Trader",
COUNT(DISTINCT CASE WHEN type = 'Spot Trade' THEN trader END) AS "Spot Trader",
COUNT(DISTINCT tx_hash) AS "Total Trades",
SUM("Total Trades") over(ORDER BY date) AS "Cumulative Trades",
SUM(CASE WHEN type = 'Perp Trade' THEN amount_usd ELSE 0 END) AS "Total Perp Trading Volume",
SUM(CASE WHEN type = 'Spot Trade' THEN amount_usd ELSE 0 END) AS "Total Spot Trading Volume",
SUM(amount_usd) AS "Total Trading Volume",
SUM("Total Trading Volume") over(ORDER BY date) AS "Cumulative Trading Volume",
SUM(CASE WHEN type = 'Perp Trade' THEN fee_amount ELSE 0 END) AS "Total Perp Fee Paid",
SUM(CASE WHEN type = 'Spot Trade' THEN fee_amount ELSE 0 END) AS "Total Spot Fee Paid",
QueryRunArchived: QueryRun has been archived