Crazzy_SidDeFi Activity Overview
Updated 2024-10-08
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 price_data AS (
SELECT
token_address,
DATE_TRUNC('day', MODIFIED_TIMESTAMP) AS day,
AVG(price) AS avg_price_usd
FROM aptos.price.ez_prices_hourly
WHERE MODIFIED_TIMESTAMP >= DATEADD(day, -30, CURRENT_TIMESTAMP)
GROUP BY token_address, day
),
dex_activities AS (
SELECT
DATE_TRUNC('day', block_timestamp) AS day,
token_in AS token_address,
SUM(amount_in_USD) AS total_dex_in_usd,
SUM(amount_out_USD) AS total_dex_out_usd
FROM aptos.defi.ez_dex_swaps
WHERE block_timestamp >= DATEADD(day, -30, CURRENT_TIMESTAMP)
GROUP BY day, token_in
),
bridge_activities AS (
SELECT
DATE_TRUNC('day', block_timestamp) AS day,
token_address,
SUM(CASE WHEN direction = 'in' THEN amount_unadj / POW(10, 18) ELSE 0 END) AS total_bridge_in_native,
SUM(CASE WHEN direction = 'out' THEN amount_unadj / POW(10, 18) ELSE 0 END) AS total_bridge_out_native
FROM aptos.defi.fact_bridge_activity
WHERE block_timestamp >= DATEADD(day, -30, CURRENT_TIMESTAMP)
GROUP BY day, token_address
)
SELECT
COALESCE(dex_activities.token_address, bridge_activities.token_address) AS token_address,
SUM(COALESCE(dex_activities.total_dex_in_usd, 0) + COALESCE(bridge_activities.total_bridge_in_native * COALESCE(p.avg_price_usd, 0), 0)) AS total_in_usd,
SUM(COALESCE(dex_activities.total_dex_out_usd, 0) + COALESCE(bridge_activities.total_bridge_out_native * COALESCE(p.avg_price_usd, 0), 0)) AS total_out_usd,
SUM(
(COALESCE(dex_activities.total_dex_in_usd, 0) + COALESCE(bridge_activities.total_bridge_in_native * COALESCE(p.avg_price_usd, 0), 0)) -
(COALESCE(dex_activities.total_dex_out_usd, 0) + COALESCE(bridge_activities.total_bridge_out_native * COALESCE(p.avg_price_usd, 0), 0))