algo_ahab-4V8aSZSOL_MINTS
Updated 2024-07-06
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 hourly_transactions AS (
SELECT
DATE_TRUNC('hour', swaps.BLOCK_TIMESTAMP) AS HOUR,
COUNT(*) AS TOTAL_TRANSACTIONS,
COUNT(DISTINCT swaps.SWAPPER) AS UNIQUE_HOLDERS,
SUM(swaps.SWAP_FROM_AMOUNT_USD + swaps.SWAP_TO_AMOUNT_USD) AS TOTAL_TRANSACTION_VALUE,
AVG((swaps.SWAP_FROM_AMOUNT_USD + swaps.SWAP_TO_AMOUNT_USD) / 2) AS AVERAGE_TRANSACTION_VALUE
FROM
solana.defi.ez_dex_swaps AS swaps
WHERE
swaps.SWAP_FROM_MINT = '3B5wuUrMEi5yATD7on46hKfej3pfmd7t1RKgrsN3pump'
OR swaps.SWAP_TO_MINT = '3B5wuUrMEi5yATD7on46hKfej3pfmd7t1RKgrsN3pump'
GROUP BY
DATE_TRUNC('hour', swaps.BLOCK_TIMESTAMP)
),
hourly_prices AS (
SELECT
DATE_TRUNC('hour', prices.INSERTED_TIMESTAMP) AS HOUR,
AVG(prices.PRICE) AS AVERAGE_HOURLY_PRICE
FROM
solana.price.ez_prices_hourly AS prices
WHERE
prices.TOKEN_ADDRESS = '3B5wuUrMEi5yATD7on46hKfej3pfmd7t1RKgrsN3pump'
GROUP BY
DATE_TRUNC('hour', prices.INSERTED_TIMESTAMP)
)
SELECT
ht.HOUR,
ht.TOTAL_TRANSACTIONS,
ht.UNIQUE_HOLDERS,
ht.TOTAL_TRANSACTION_VALUE,
ht.AVERAGE_TRANSACTION_VALUE,
hp.AVERAGE_HOURLY_PRICE
FROM
hourly_transactions AS ht
LEFT JOIN