amelia-leeCopy of distr
Updated 2022-12-01
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
›
⌄
WITH
PRICE AS (SELECT DATE_TRUNC ('HOUR',HOUR) AS TIME, AVG (PRICE) AS PRICE
FROM ethereum.core.fact_hourly_token_prices WHERE TOKEN_ADDRESS = '0x44108f0223a3c3028f5fe7aec7f9bb2e66bef82f' GROUP BY 1)
SELECT
DATE_TRUNC ('HOUR',BLOCK_TIMESTAMP) AS DATE,
COUNT (DISTINCT TX_HASH) AS TXN,
COUNT (DISTINCT TO_ADDRESS) AS USERS,
SUM (RAW_AMOUNT / 1e18) AS VOLUME,
SUM ((RAW_AMOUNT / 1e18) * PRICE ) AS USD,
CASE
WHEN (RAW_AMOUNT / 1e18) < 50 THEN 'Less than 50 ACX'
WHEN (RAW_AMOUNT / 1e18) BETWEEN 50 and 100 THEN '50 and 100 ACX'
WHEN (RAW_AMOUNT / 1e18) BETWEEN 100 and 500 THEN '100 and 500 ACX'
WHEN (RAW_AMOUNT / 1e18) BETWEEN 500 and 1000 THEN '500 and 1K ACX'
WHEN (RAW_AMOUNT / 1e18) BETWEEN 1000 and 10000 THEN '1K and 10K ACX'
WHEN (RAW_AMOUNT / 1e18) BETWEEN 10000 and 50000 THEN '10K and 50K ACX'
WHEN (RAW_AMOUNT / 1e18) BETWEEN 50000 and 100000 THEN '50K and 100K ACX'
ELSE 'More than 100K ACX' END AS TYPE,
SUM (TXN) OVER (PARTITION BY TYPE ORDER BY DATE) AS CUM_TXN,
SUM (USERS) OVER (PARTITION BY TYPE ORDER BY DATE) AS CUM_USERS,
SUM (VOLUME) OVER (PARTITION BY TYPE ORDER BY DATE) AS CUM_VOLUME
FROM ethereum.core.ez_token_transfers LEFT JOIN PRICE ON DATE_TRUNC ('HOUR',BLOCK_TIMESTAMP) = TIME
WHERE CONTRACT_ADDRESS = '0x44108f0223a3c3028f5fe7aec7f9bb2e66bef82f' AND FROM_ADDRESS = '0xe50b2ceac4f60e840ae513924033e753e2366487'
GROUP BY 1,TYPE
Run a query to Download Data