bergTop 20 Events Users Done 1 Day Before and After Using LI.FI
Updated 2024-07-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 tab AS (
SELECT
origin_from_address AS user,
block_timestamp,
tx_hash
FROM ethereum.core.ez_decoded_event_logs
WHERE origin_to_address = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae'
AND decoded_log:integrator = 'jumper.exchange'
AND event_name = 'LiFiGenericSwapCompleted'
AND tx_status = 'SUCCESS'
),
tab2 AS (
SELECT
c.label,
a.event_name,
a.event_name || ' x ' || c.label as name,
a.tx_hash,
a.origin_from_address AS user,
IFF(a.block_timestamp < b.block_timestamp, 'Before', 'After') AS type
FROM ethereum.core.ez_decoded_event_logs a
JOIN ethereum.core.dim_labels c
ON a.origin_to_address = c.address
JOIN tab b ON a.tx_hash != b.tx_hash AND a.origin_from_address = b.user AND ABS(DATEDIFF('seconds', a.block_timestamp, b.block_timestamp)) <= 86400
WHERE tx_status = 'SUCCESS'
AND label_type != 'token'
AND label_subtype != 'token_contract'
AND event_name NOT IN ('Approval', 'Transfer')
)
SELECT
type,
name,
COUNT(DISTINCT tx_hash) AS txns,
COUNT(DISTINCT user) AS users
FROM tab2
QueryRunArchived: QueryRun has been archived