bobby_danieltotal 🌐 stats copy
Updated 2025-03-07Copy Reference Fork
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
›
⌄
-- forked from mr_d / total 🌐 stats @ https://flipsidecrypto.xyz/mr_d/q/FOJINf2r4Ilu/total-stats
WITH APT_price AS (
SELECT
hour,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS price
FROM aptos.price.ez_prices_hourly
WHERE symbol = 'APT'
GROUP BY hour
),
transactions_data AS (
SELECT
t1.block_timestamp,
t1.TX_HASH,
t1.EVENT_DATA:swapper AS trader,
CASE
WHEN t1.EVENT_DATA:is_sell = FALSE
THEN CAST(t1.EVENT_DATA:input_amount AS FLOAT) / 1e8
ELSE CAST(t1.EVENT_DATA:quote_volume AS FLOAT) / 1e8
END AS volume_apt,
(CAST(t1.EVENT_DATA:input_amount AS FLOAT) / 1e8) * hp.price AS volume_usd,
TRY_HEX_DECODE_STRING(SUBSTRING(t2.EVENT_DATA:market_metadata:emoji_bytes, 3)) AS emoji,
t2.EVENT_DATA:market_metadata:market_address AS market,
CAST(t2.event_data:instantaneous_stats:market_cap AS FLOAT) / 1e8 AS market_cap_apt,
(CAST(t2.event_data:instantaneous_stats:market_cap AS FLOAT) / 1e8) * hp.price AS market_cap_usd,
CAST(t2.event_data:instantaneous_stats:total_value_locked AS FLOAT) / 1e8 AS tvl_apt,
(CAST(t2.event_data:instantaneous_stats:total_value_locked AS FLOAT) / 1e8) * hp.price AS tvl_usd,
CAST(t2.event_data:last_swap:avg_execution_price_q64 AS FLOAT) / POWER(2, 64) AS swap_price,
CAST(t2.event_data:cumulative_stats:n_swaps AS INT) AS total_swaps,
CAST(t2.event_data:cumulative_stats:integrator_fees AS FLOAT) / 1e8 AS integrator_fees_apt,
(CAST(t2.event_data:cumulative_stats:integrator_fees AS FLOAT) / 1e8) * hp.price AS integrator_fees,
CAST(t2.event_data:cumulative_stats:pool_fees_quote AS FLOAT) / 1e8 AS pool_fees_apt,
(CAST(t2.event_data:cumulative_stats:pool_fees_quote AS FLOAT) / 1e8) * hp.price AS pool_fees,
ROW_NUMBER() OVER (PARTITION BY emoji, market ORDER BY block_timestamp DESC) AS row_num
FROM aptos.core.fact_events t1
QueryRunArchived: QueryRun has been archived