DATE | TX_COUNT | CUM_TX | TOTAL_USD | CUM_USD | TOTAL_BRIDGERS | |
|---|---|---|---|---|---|---|
1 | 2025-02-07 00:00:00.000 | 13 | 5002 | 496.7170675 | 21402124.2351204 | 13 |
2 | 2025-02-06 00:00:00.000 | 45 | 4989 | 2786863.57362795 | 21401627.5180529 | 43 |
3 | 2025-02-05 00:00:00.000 | 47 | 4944 | 2215.392562354 | 18614763.9444249 | 46 |
4 | 2025-02-04 00:00:00.000 | 49 | 4897 | 5411.116546715 | 18612548.5518626 | 48 |
5 | 2025-02-03 00:00:00.000 | 22 | 4848 | 171099.190066667 | 18607137.4353158 | 18 |
6 | 2025-02-02 00:00:00.000 | 72 | 4826 | 64367.09156421 | 18436038.2452492 | 66 |
7 | 2025-02-01 00:00:00.000 | 76 | 4754 | 82746.411049746 | 18371671.153685 | 70 |
8 | 2025-01-31 00:00:00.000 | 78 | 4678 | 178011.434631829 | 18288924.7426352 | 77 |
9 | 2025-01-30 00:00:00.000 | 104 | 4600 | 559544.037485213 | 18110913.3080034 | 99 |
10 | 2025-01-29 00:00:00.000 | 112 | 4496 | 1145910.32696499 | 17551369.2705182 | 106 |
11 | 2025-01-28 00:00:00.000 | 127 | 4384 | 1290243.04352026 | 16405458.9435532 | 119 |
12 | 2025-01-27 00:00:00.000 | 61 | 4257 | 245941.728257092 | 15115215.9000329 | 40 |
13 | 2025-01-26 00:00:00.000 | 155 | 4196 | 28512.010789992 | 14869274.1717758 | 149 |
14 | 2025-01-25 00:00:00.000 | 173 | 4041 | 8495.600737315 | 14840762.1609858 | 165 |
15 | 2025-01-24 00:00:00.000 | 120 | 3868 | 5282.981567951 | 14832266.5602485 | 117 |
16 | 2025-01-23 00:00:00.000 | 188 | 3748 | 29786.920815175 | 14826983.5786806 | 183 |
17 | 2025-01-22 00:00:00.000 | 303 | 3560 | 33133.315125428 | 14797196.6578654 | 292 |
18 | 2025-01-21 00:00:00.000 | 12 | 3257 | 15756.02221875 | 14764063.34274 | 11 |
19 | 2025-01-20 00:00:00.000 | 7 | 3245 | 2551.0368925 | 14748307.3205212 | 7 |
20 | 2025-01-19 00:00:00.000 | 10 | 3238 | 133158.827495313 | 14745756.2836287 | 6 |
Specterswell trend
Updated 2025-02-07
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 EthPrice AS (
SELECT
TRUNC(hour, 'day') AS date,
AVG(price) AS price_usd
FROM ethereum.price.ez_prices_hourly
WHERE symbol = 'WETH'
GROUP BY date
),
DecodedData AS (
SELECT
DATE_TRUNC('day', el.Block_timestamp) AS date,
el.tx_hash,
regexp_substr_all(SUBSTR(el.data, 3), '.{64}') AS segmented,
'0x' || SUBSTR(segmented[0], 25, 40) AS bridgers, -- Extract Ethereum address
CAST(livequery.utils.udf_hex_to_int(segmented[1]) AS DOUBLE) / 1e18 AS eth_amount -- Convert from Wei to ETH
FROM swell.core.fact_event_logs AS el
WHERE el.origin_from_address = '0xf7ba9ef12995defc5ff47ec0e13252f0e690486a'
AND el.origin_to_address = '0x4200000000000000000000000000000000000007'
AND el.topics[0] = '0xb0444523268717a02698be47d0803aa7468c00acbed2f8bd93a0459cde61dd89'
AND el.Block_timestamp > '2024-11-27'
AND el.tx_succeeded = TRUE
)
, Aggregated AS (
SELECT
d.date, -- Grouping by day
COUNT(DISTINCT d.tx_hash) AS tx_count, -- Total number of transactions
SUM(d.eth_amount * p.price_usd) AS total_usd, -- Total USD value
COUNT(DISTINCT d.bridgers) AS total_bridgers -- Total unique bridgers
FROM DecodedData d
LEFT JOIN EthPrice p
ON d.date = p.date -- Ensuring date match
GROUP BY d.date
)
Last run: 3 months ago
69
5KB
5s