Haisenbergcore-metrics x time
Updated 2025-01-25
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 core-metrics @ https://flipsidecrypto.xyz/studio/queries/c063e4ce-4c72-4c14-a7b3-59aa3af8861c
-- Hourly price aggregation for APT
WITH hourly_apt_prices AS (
SELECT
hour,
AVG(price) AS avg_price
FROM aptos.price.ez_prices_hourly
WHERE SYMBOL = 'APT'
GROUP BY 1
),
-- Emoji swap transactions with price data
emoji_swaps AS (
SELECT
DATE_TRUNC('hour', BLOCK_TIMESTAMP) AS trade_hour,
tx_hash,
event_data:input_amount::FLOAT / pow(10, 8) AS token_amount,
event_data:quote_volume::FLOAT / pow(10, 8) AS apt_volume,
(event_data:quote_volume::FLOAT / pow(10, 8)) * p.avg_price AS usd_volume,
event_data:market_id AS emoji_id,
event_data:swapper AS trader_address
FROM aptos.core.fact_events e
INNER JOIN hourly_apt_prices p
ON DATE_TRUNC('hour', e.BLOCK_TIMESTAMP) = p.hour
WHERE event_module = 'emojicoin_dot_fun'
AND event_resource = 'Swap'
)
-- Final aggregation
SELECT
trade_hour,
COUNT(*) AS total_swaps,
COUNT(DISTINCT trader_address) AS unique_traders,
COUNT(DISTINCT emoji_id) AS unique_emojis,
SUM(apt_volume) AS total_volume_apt,
QueryRunArchived: QueryRun has been archived