datavortexWeekly Changes
Updated 2024-12-17
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_data AS (
SELECT
DATE_TRUNC('week', block_timestamp) AS week_start,
COUNT(DISTINCT origin_from_address) AS weekly_users,
COUNT(DISTINCT tx_hash) AS weekly_swaps,
SUM(amount_in_usd) AS weekly_volume_usd
FROM kaia.defi.ez_dex_swaps
WHERE platform = 'capybara'
GROUP BY week_start
),
lagged_data AS (
SELECT
week_start,
weekly_users,
weekly_swaps,
weekly_volume_usd,
LAG(weekly_users) OVER (ORDER BY week_start) AS prev_week_users,
LAG(weekly_swaps) OVER (ORDER BY week_start) AS prev_week_swaps,
LAG(weekly_volume_usd) OVER (ORDER BY week_start) AS prev_week_volume,
ROW_NUMBER() OVER (ORDER BY week_start) AS week_number
FROM weekly_data
),
weekly_changes AS (
SELECT
week_start,
ROUND((weekly_users - prev_week_users) * 100.0 / NULLIF(prev_week_users, 0), 2) AS user_change_percent,
ROUND((weekly_swaps - prev_week_swaps) * 100.0 / NULLIF(prev_week_swaps, 0), 2) AS swap_change_percent,
ROUND((weekly_volume_usd - prev_week_volume) * 100.0 / NULLIF(prev_week_volume, 0), 2) AS volume_change_percent,
week_number
FROM lagged_data
)
SELECT
week_start,
user_change_percent,
swap_change_percent,
volume_change_percent
QueryRunArchived: QueryRun has been archived