datavortexweekly new and returning
Updated 2024-12-16
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
›
⌄
⌄
/*
SELECT
DATE_TRUNC('week', block_timestamp) AS week,
COUNT(DISTINCT subaccount) AS weekly_active_traders
FROM
(
SELECT
topics[2] AS subaccount,
block_timestamp
FROM
arbitrum.core.fact_event_logs
WHERE
topics[0] = '0x224253ad5cda2459ff587f559a41374ab9243acbd2daff8c13f05473db79d14c'
UNION ALL
SELECT
topics[3] AS subaccount,
block_timestamp
FROM
arbitrum.core.fact_event_logs
WHERE
topics[0] = '0x7c57459d6f4f0fb2fc5b1e298c8c0eb238422944964aa1e249eaa78747f0cca9'
) AS subaccounts
GROUP BY week
ORDER BY week DESC;
*/
WITH trader_activity AS (
SELECT
DATE_TRUNC('week', block_timestamp) AS activity_week,
CASE
WHEN topics[0] = '0x224253ad5cda2459ff587f559a41374ab9243acbd2daff8c13f05473db79d14c' THEN topics[2]
WHEN topics[0] = '0x7c57459d6f4f0fb2fc5b1e298c8c0eb238422944964aa1e249eaa78747f0cca9' THEN topics[3]
END AS trader_account,
MIN(DATE_TRUNC('week', block_timestamp)) OVER (PARTITION BY
CASE
QueryRunArchived: QueryRun has been archived