Penta Limited TeamWeekly Optimism 5.2
Updated 2024-12-09
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 weekly_swap_stats AS (
SELECT
date_trunc('week', BLOCK_TIMESTAMP) AS week,
platform,
COUNT(DISTINCT TX_HASH) AS num_swap_transactions,
COUNT(DISTINCT SENDER) AS num_swap_users,
SUM(AMOUNT_OUT_USD) AS total_amount_swapped,
LAG(num_swap_transactions) OVER (partition by platform ORDER BY week) AS num_swap_transactions_last_week,
LAG(num_swap_users) OVER (partition by platform ORDER BY week) AS num_swap_users_last_week,
LAG(total_amount_swapped) OVER (partition by platform ORDER BY week) AS total_amount_swapped_last_week
FROM
optimism.defi.ez_dex_swaps
WHERE
BLOCK_TIMESTAMP >= current_date - INTERVAL '12 WEEKS'
and date_trunc('week',block_timestamp) < date_trunc('week',current_date)
GROUP BY 1,2
)
SELECT
week, platform,
num_swap_transactions,
num_swap_transactions_last_week,
(num_swap_transactions - num_swap_transactions_last_week)/num_swap_transactions_last_week * 100 AS num_transactions_diff,
num_swap_users,
num_swap_users_last_week,
(num_swap_users - num_swap_users_last_week)/num_swap_users_last_week * 100 AS num_users_diff,
total_amount_swapped,
total_amount_swapped_last_week,
(total_amount_swapped - total_amount_swapped_last_week) / total_amount_swapped_last_week * 100 AS amount_swapped_diff_percentage
FROM
weekly_swap_stats
ORDER BY
week DESC, platform
QueryRunArchived: QueryRun has been archived