datavortexweekly
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 tx_hash) AS weekly_swap_count,
SUM(amount_in_usd) AS weekly_swap_volume_usd,
COUNT(DISTINCT origin_from_address) AS weekly_active_users
FROM kaia.defi.ez_dex_swaps
WHERE platform = 'capybara'
GROUP BY week_start
)
SELECT
week_start,
weekly_swap_count,
weekly_swap_volume_usd,
weekly_active_users,
SUM(weekly_swap_count) OVER (ORDER BY week_start) AS "cumulative swap count",
SUM(weekly_swap_volume_usd) OVER (ORDER BY week_start) AS "cumulative swap volume usd",
SUM(weekly_active_users) OVER (ORDER BY week_start) AS "cumulative active users"
FROM weekly_data
ORDER BY week_start;
/*
WITH stablecoins AS (
SELECT DISTINCT
symbol_in AS stable_token
FROM kaia.defi.ez_dex_swaps
WHERE symbol_in IN (
'oUSDT', 'USDC.e', 'KDAI', 'USDC', 'CUSDT', 'mUSDC', 'mUSDT', 'oUSDC', 'oWBTC', 'USDT'
)
),
swaps AS (
SELECT
tx_hash,
COALESCE(symbol_in, '') AS symbol_in, -- Handle null symbol_in
COALESCE(symbol_out, '') AS symbol_out, -- Handle null symbol_out
COALESCE(amount_in_usd, 0) AS amount_in_usd, -- Handle null amounts
QueryRunArchived: QueryRun has been archived