Zanyar_98Number of Sushiswap users and Number of new Optimism users - Weekly chart
Updated 2023-04-06Copy Reference Fork
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 New_Users_First_Transaction_TimeStamp AS (
SELECT MIN(BLOCK_TIMESTAMP) AS "First Transaction", ORIGIN_FROM_ADDRESS "User Address"
FROM optimism.core.fact_event_logs
GROUP BY "User Address"
HAVING "First Transaction"::DATE < CURRENT_DATE - 1
ORDER BY "First Transaction"
),
New_Users_Transactions AS (
SELECT Block_Timestamp, TX_HASH, "User Address"
FROM New_Users_First_Transaction_TimeStamp JOIN optimism.core.fact_event_logs ON ("User Address" = ORIGIN_FROM_ADDRESS)
GROUP BY Block_Timestamp, TX_HASH, "User Address"
ORDER BY Block_Timestamp
),
Rank_Transactions AS (
SELECT BLOCK_TIMESTAMP, TX_HASH, "User Address",
RANK() OVER (Partition By "User Address" ORDER by BLOCK_TIMESTAMP ASC ROWS BETWEEN unbounded preceding AND CURRENT ROW) "Transactions Order"
FROM New_Users_Transactions ORDER BY BLOCK_TIMESTAMP ASC
),
New_Users_First_Two_Transactions AS (
SELECT BLOCK_TIMESTAMP, TX_HASH, "User Address", "Transactions Order"
FROM Rank_Transactions
WHERE "Transactions Order" < 3
),
SWAPS AS (
SELECT BLOCK_TIMESTAMP, TX_HASH, ORIGIN_FROM_ADDRESS AS "User Address", 'Swap' AS TYPE
FROM optimism.sushi.ez_swaps
GROUP BY BLOCK_TIMESTAMP, TX_HASH, ORIGIN_FROM_ADDRESS
ORDER BY BLOCK_TIMESTAMP
),
XSWAPS AS (
SELECT BLOCK_TIMESTAMP,TX_HASH, ORIGIN_FROM_ADDRESS AS "User Address", 'xSwap' AS TYPE
FROM optimism.core.fact_event_logs WHERE ORIGIN_TO_ADDRESS = '0x8b396ddf906d552b2f98a8e7d743dd58cd0d920f'
Run a query to Download Data