-
Notifications
You must be signed in to change notification settings - Fork 36
/
shortest-path-via-traces.sql
63 lines (61 loc) · 2.01 KB
/
shortest-path-via-traces.sql
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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
# This query will fail if there are too many traces.
# There is a workaround here:
# https://console.cloud.google.com/bigquery?sq=749871510730:082d50190dc04d79aaf7ee4d5c6f9d00
DECLARE start_address STRING DEFAULT LOWER('0x47068105c5feff69e44520b251b9666d4b512a70');
DECLARE end_address STRING DEFAULT LOWER('0x2604afb5a64992e5abbf25865c9d3387ade92bad');
WITH traces_0 AS (
SELECT *
FROM `bigquery-public-data.crypto_ethereum.traces`
WHERE from_address = start_address
),
traces_1_hop AS (
SELECT
1 AS hops,
traces_1.from_address,
traces_1.to_address,
traces_1.trace_address,
traces_1.block_timestamp,
concat(traces_0.from_address, ' -> ', traces_0.to_address, ' -> ', traces_1.to_address) as path
FROM `bigquery-public-data.crypto_ethereum.traces` AS traces_1
INNER JOIN traces_0
ON traces_0.to_address = traces_1.from_address
AND traces_0.block_timestamp <= traces_1.block_timestamp
),
traces_2_hops AS (
SELECT
2 AS hops,
traces_2.from_address,
traces_2.to_address,
traces_2.trace_address,
traces_2.block_timestamp,
concat(path, ' -> ', traces_2.to_address) AS path
FROM `bigquery-public-data.crypto_ethereum.traces` AS traces_2
INNER JOIN traces_1_hop
ON traces_1_hop.to_address = traces_2.from_address
AND traces_1_hop.block_timestamp <= traces_2.block_timestamp
),
traces_3_hops AS (
SELECT
3 AS hops,
traces_3.from_address,
traces_3.to_address,
traces_3.trace_address,
traces_2_hops.block_timestamp,
concat(path, ' -> ', traces_3.to_address) AS path
FROM `bigquery-public-data.crypto_ethereum.traces` AS traces_3
INNER JOIN traces_2_hops
ON traces_2_hops.to_address = traces_3.from_address
AND traces_2_hops.block_timestamp <= traces_3.block_timestamp
WHERE traces_3.to_address = end_address
),
traces_all_hops AS (
SELECT * FROM traces_1_hop
UNION ALL
SELECT * FROM traces_2_hops
UNION ALL
SELECT * FROM traces_3_hops
)
SELECT *
FROM traces_all_hops
WHERE hops = 3
LIMIT 100