HOUR | SALES_USD | TOKEN_SOLD | SALES | WALLETS | CUMULATIVE_SALES_USD | CUMULATIVE_TOKEN_SOLD | CUMULATIVE_SALES | CUMULATIVE_WALLETS | |
|---|---|---|---|---|---|---|---|---|---|
1 | 2024-10-14 00:00:00.000 | 11.545436676 | 769.695778409 | 1 | 1 | 11.545436676 | 769.695778409 | 1 | 1 |
2 | 2024-10-14 01:00:00.000 | 4.7 | 313.333333333 | 1 | 1 | 16.245436676 | 1083.029111743 | 2 | 2 |
3 | 2024-10-15 12:00:00.000 | 3081166.62609986 | 205411108.406657 | 1339 | 1245 | 3081182.87153653 | 205412191.435769 | 1341 | 1247 |
4 | 2024-10-15 13:00:00.000 | 2134088.09905058 | 142272539.936706 | 1962 | 1799 | 5215270.97058712 | 347684731.372475 | 3303 | 3046 |
5 | 2024-10-15 14:00:00.000 | 8266.585152553 | 551105.676836837 | 6 | 6 | 5223537.55573967 | 348235837.049311 | 3309 | 3052 |
6 | 2024-10-15 15:00:00.000 | 259560.998144963 | 17304066.5429975 | 170 | 167 | 5483098.55388463 | 365539903.592309 | 3479 | 3219 |
7 | 2024-10-15 16:00:00.000 | 1653429.67138618 | 110228644.759079 | 635 | 583 | 7136528.22527081 | 475768548.351388 | 4114 | 3802 |
8 | 2024-10-15 17:00:00.000 | 587922.997704729 | 39194866.5136486 | 452 | 428 | 7724451.22297554 | 514963414.865036 | 4566 | 4230 |
9 | 2024-10-15 18:00:00.000 | 277466.433161359 | 18497762.2107573 | 498 | 449 | 8001917.6561369 | 533461177.075794 | 5064 | 4679 |
10 | 2024-10-15 19:00:00.000 | 298437.922317984 | 19895861.4878656 | 327 | 311 | 8300355.57845489 | 553357038.563659 | 5391 | 4990 |
11 | 2024-10-15 20:00:00.000 | 284101.880121275 | 18940125.3414183 | 435 | 403 | 8584457.45857616 | 572297163.905078 | 5826 | 5393 |
12 | 2024-10-15 21:00:00.000 | 231439.462344951 | 15429297.4896634 | 386 | 355 | 8815896.92092111 | 587726461.394741 | 6212 | 5748 |
13 | 2024-10-15 22:00:00.000 | 179048.240026039 | 11936549.3350693 | 367 | 332 | 8994945.16094715 | 599663010.72981 | 6579 | 6080 |
14 | 2024-10-15 23:00:00.000 | 362721.084896007 | 24181405.6597338 | 592 | 522 | 9357666.24584316 | 623844416.389544 | 7171 | 6602 |
15 | 2024-10-16 00:00:00.000 | 475795.780749134 | 31719718.7166089 | 464 | 421 | 9833462.02659229 | 655564135.106153 | 7635 | 7023 |
16 | 2024-10-16 01:00:00.000 | 258798.767205575 | 17253251.1470383 | 367 | 338 | 10092260.7937979 | 672817386.253191 | 8002 | 7361 |
17 | 2024-10-16 02:00:00.000 | 229557.868342311 | 15303857.8894874 | 266 | 239 | 10321818.6621402 | 688121244.142679 | 8268 | 7600 |
18 | 2024-10-16 03:00:00.000 | 194355.364668225 | 12957024.311215 | 316 | 295 | 10516174.0268084 | 701078268.453894 | 8584 | 7895 |
19 | 2024-10-16 04:00:00.000 | 177430.368351696 | 11828691.2234464 | 302 | 281 | 10693604.3951601 | 712906959.67734 | 8886 | 8176 |
20 | 2024-10-16 05:00:00.000 | 134955.250595346 | 8997016.70635637 | 274 | 251 | 10828559.6457554 | 721903976.383697 | 9160 | 8427 |
Updated 2025-03-21
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
›
⌄
WITH buytx AS (
SELECT
tx_hash
FROM ethereum.core.fact_event_logs
WHERE contract_address = lower('0xe217E15b3C19cC0427F9492dC3bcfe8220aFAD10') -- buy contract
AND block_timestamp > '2024-10-10'
)
SELECT
DATE_TRUNC('hour', t.block_timestamp) AS hour,
SUM(raw_amount / POWER(10, 18)) * 0.015 AS sales_usd, --1WIFI=$0.015
SUM(raw_amount / POWER(10, 18)) AS token_sold,
COUNT(DISTINCT t.tx_hash) AS sales,
COUNT(DISTINCT t.origin_from_address) AS wallets,
-- Cumulative metrics
SUM(SUM(raw_amount / POWER(10, 18)) * 0.015)
OVER (ORDER BY DATE_TRUNC('hour', t.block_timestamp)) AS cumulative_sales_usd,
SUM(SUM(raw_amount / POWER(10, 18)))
OVER (ORDER BY DATE_TRUNC('hour', t.block_timestamp)) AS cumulative_token_sold,
SUM(COUNT(DISTINCT t.tx_hash))
OVER (ORDER BY DATE_TRUNC('hour', t.block_timestamp)) AS cumulative_sales,
SUM(COUNT(DISTINCT t.origin_from_address))
OVER (ORDER BY DATE_TRUNC('hour', t.block_timestamp)) AS cumulative_wallets
FROM ethereum.core.fact_token_transfers t
JOIN buytx ON t.tx_hash = buytx.tx_hash
WHERE t.contract_address = lower('0xdA5e1988097297dCdc1f90D4dFE7909e847CBeF6') -- token contract
AND t.block_timestamp > '2024-10-10'
GROUP BY 1
ORDER BY hour;
Last run: 24 days ago
...
2216
234KB
12s