commanderjoseph-761gwXBurrBear Dex Swap Daily
Updated 2024-12-11
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
total
AS
(
SELECT
block_timestamp,
origin_from_address AS swapper,
'0x'||SUBSTR(topics[2], 27, 40) AS token_in,
'0x'||SUBSTR(topics[3], 27, 40) AS token_out,
utils.udf_hex_to_int(SUBSTR(data, 3, 64))/POW(10, 18) AS token_in_amount,
utils.udf_hex_to_int(SUBSTR(data, 67, 64))/POW(10, 18) AS token_out_amount,
tx_hash
FROM
berachain.testnet.fact_event_logs
WHERE
topics[0] = '0x2170c741c41531aec20e7c107c24eecfdd15e69c9bb0a8dd37b1840b9e0b207b'
AND
origin_to_address = '0xfdb2925ae2d3e2eacfe927611305e5e56aa5f832'
--AND
--block_timestamp > current_timestamp - interval '1 day'
AND
tx_succeeded = 'TRUE'
)
SELECT
DATE_TRUNC('day', block_timestamp) AS transaction_date,
COUNT(DISTINCT swapper) AS daily_swapper,
COUNT(DISTINCT tx_hash) AS daily_swaps,
COUNT(DISTINCT token_out) AS daily_token_traded
FROM
total
GROUP
BY
transaction_date
ORDER
BY
transaction_date
QueryRunArchived: QueryRun has been archived