Crazzy_SidTransaction Analysis
Updated 2024-08-03
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
›
⌄
WITH SymbolAggregates AS (
SELECT
SYMBOL,
SUM(AMOUNT_RAW) AS total_amount_raw,
AVG(TOKEN_PRICE) AS avg_token_price
FROM near.core.ez_token_transfers
WHERE BLOCK_TIMESTAMP >= '2024-07-01' AND BLOCK_TIMESTAMP <= CURRENT_DATE()
GROUP BY SYMBOL
)
-- Aggregates total USD transferred and distinct blocks per day
, DailyAggregates AS (
SELECT
DATE(BLOCK_TIMESTAMP) AS transfer_date,
COUNT(DISTINCT BLOCK_ID) AS distinct_block_count,
SUM(AMOUNT_USD) AS total_usd_transferred
FROM near.core.ez_token_transfers
WHERE BLOCK_TIMESTAMP >= '2024-07-01' AND BLOCK_TIMESTAMP <= CURRENT_DATE()
GROUP BY DATE(BLOCK_TIMESTAMP)
)
-- Final result combining daily aggregates with symbol aggregates
SELECT
d.transfer_date,
d.distinct_block_count,
d.total_usd_transferred,
s.SYMBOL,
s.total_amount_raw,
s.avg_token_price
FROM DailyAggregates d
CROSS JOIN SymbolAggregates s
ORDER BY d.transfer_date, s.SYMBOL;
QueryRunArchived: QueryRun has been archived