Updated 2025-02-06
999
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 bridge_contracts AS (
SELECT DISTINCT
contract_address,
contract_name,
event_name,
origin_to_address as bridge_router,
origin_function_signature
FROM swell.core.ez_decoded_event_logs
WHERE event_name IN ('Deposit', 'Transfer')
AND block_timestamp >= DATEADD('day', -30, CURRENT_TIMESTAMP())
),
bridge_stats AS (
SELECT
DATE_TRUNC('day', e.block_timestamp) as date,
e.contract_address,
e.contract_name,
e.event_name,
e.origin_to_address as bridge_router,
e.origin_function_signature,
COUNT(DISTINCT e.origin_from_address) as unique_bridgers,
COUNT(DISTINCT e.tx_hash) as total_transactions,
SUM(CASE
WHEN e.decoded_log:amount is not null THEN e.decoded_log:amount::float/1e18
WHEN e.decoded_log:value is not null THEN e.decoded_log:value::float/1e18
ELSE 0
END) as total_amount_bridged
FROM swell.core.ez_decoded_event_logs e
INNER JOIN bridge_contracts b
ON e.contract_address = b.contract_address
AND e.event_name IN ('Deposit', 'Transfer')
WHERE e.block_timestamp >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1,2,3,4,5,6
)
QueryRunArchived: QueryRun has been archived