Ericmoore_11cet
Updated 2024-11-18
999
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 swap_data AS (
-- Base data for all subsequent queries (filtered for the last 7 days)
SELECT
DATE_TRUNC('day', block_timestamp) AS date,
tx_hash,
swapper,
symbol_in,
symbol_out,
COALESCE(amount_in_usd, 0) AS amount_in_usd,
COALESCE(amount_out_usd, 0) AS amount_out_usd,
(COALESCE(amount_in_usd, 0) + COALESCE(amount_out_usd, 0)) AS total_volume_usd
FROM aptos.defi.ez_dex_swaps
WHERE platform = 'cetus'
AND block_timestamp >= CURRENT_DATE - INTERVAL '7 days'
),
-- CTE for Daily Active Users (DAU)
daily_active_users AS (
SELECT
date,
COUNT(DISTINCT swapper) AS dau
FROM swap_data
GROUP BY date
),
-- CTE for Top Trading Pairs by Volume (last 7 days)
top_trading_pairs AS (
SELECT
DATE_TRUNC('day', block_timestamp) AS date,
COALESCE(symbol_in, '') || ' - ' || COALESCE(symbol_out, '') AS pairs,
SUM((COALESCE(amount_in_usd, 0) + COALESCE(amount_out_usd, 0)) / 2) AS volume
FROM aptos.defi.ez_dex_swaps
WHERE platform = 'cetus'
AND block_timestamp >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY date, pairs
HAVING SUM((COALESCE(amount_in_usd, 0) + COALESCE(amount_out_usd, 0)) / 2) > 0
QueryRunArchived: QueryRun has been archived