datavortexrubber-coffee
Updated 2024-11-18
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 first_bridge AS (
SELECT
destination_address,
MIN(block_timestamp) AS "First Bridge Timestamp"
FROM
near.defi.ez_bridge_activity
WHERE
destination_chain = 'near'
GROUP BY
destination_address
),
bridge_activity AS (
SELECT
lb.tx_hash,
lb.destination_address,
lb.block_timestamp,
fb."First Bridge Timestamp",
CASE
WHEN lb.block_timestamp = fb."First Bridge Timestamp" THEN 'New Wallet'
WHEN lb.block_timestamp > fb."First Bridge Timestamp" THEN 'Existing Wallet'
END AS wallet_status
FROM
near.defi.ez_bridge_activity AS lb
INNER JOIN
first_bridge AS fb
ON lb.destination_address = fb.destination_address
WHERE
lb.destination_chain = 'near'
AND lb.block_timestamp BETWEEN '2023-01-01' AND '2023-12-31'
)
SELECT
wallet_status AS "Wallet Status",
COUNT(DISTINCT tx_hash) AS "Total Bridge Transactions"
FROM
bridge_activity
QueryRunArchived: QueryRun has been archived