par_rnEMOJI 04
Updated 2024-12-13
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 BASE AS (
select
DISTINCT tx_hash,
event_data:market_metadata:emoji_bytes AS emoji_hex,
event_data:market_metadata:market_address AS address,
event_data:market_metadata:market_id AS id
FROM
aptos.core.fact_events
WHERE
event_module = 'emojicoin_dot_fun'
AND event_resource = 'MarketRegistration'
),
price AS (
SELECT
hour :: date AS datetime,
AVG(price) AS price
FROM
aptos.price.ez_prices_hourly
WHERE
symbol = 'APT'
GROUP BY
1
)
SELECT
TRY_HEX_DECODE_STRING(SUBSTR(emoji_hex, 3)) AS "Emoji",
COUNT(DISTINCT EVENT_DATA:swapper) AS "Swappers",
SUM(EVENT_DATA:quote_volume / pow(10, 8)) AS "Volume APT",
SUM((EVENT_DATA:quote_volume / pow(10, 8)) * price) AS "Volume USD"
FROM
aptos.core.fact_events
JOIN price on block_timestamp :: date = datetime
LEFT JOIN base on EVENT_DATA:market_id = id
WHERE
event_module = 'emojicoin_dot_fun'
AND event_resource = 'Swap'
AND LENGTH(TRY_HEX_DECODE_STRING(SUBSTR(emoji_hex, 3))) <= 3
QueryRunArchived: QueryRun has been archived