connorhAlchemix Flows
Updated 2021-03-24
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
›
⌄
with recursive eth_recurse
-- Column names for the "view"/CTE
(root, from_address, to_address, degree_out,usd_transfered,n_txn)
as
-- Common Table Expression
(
-- Anchor
SELECT a.from_address AS root,a.from_address, a.to_address, 1 AS degree_out, SUM(a.amount_usd) AS usd_transfered, COUNT(DISTINCT tx_id) AS n_txn
FROM ethereum.udm_events a
WHERE a.from_address IN ('0xc21d353ff4ee73c572425697f4f5aad2109fe35b','0xc3f279090a47e80990fe3a9c30d24cb117ef91a8',
'0xdbdb4d16eda451d0503b854cf79d55697f90c8df','0x014de182c147f8663589d77eadb109bf86958f13',
'0x8392f6669292fa56123f71949b52d883ae57e225','0xab7a49b971afdc7ee26255038c82b4006d122086',
'0x869d1b8610c038a6c4f37bd757135d4c29ae8917','0xab8e74017a8cc7c15ffccd726603790d26d7deca',
'0xbc6da0fe9ad5f3b0d58160288917aa56653660e9') -- All alchemix contracts
AND a.block_timestamp >= CURRENT_DATE - 2
GROUP BY 1,2,3,4
union all
-- Recursive bit
SELECT b.root AS root,a.from_address, a.to_address, b.degree_out + 1 AS degree_out, a.usd_transfered,a.n_txn
FROM eth_recurse b
LEFT JOIN (
SELECT from_address, to_address, SUM(amount_usd) AS usd_transfered, COUNT(DISTINCT tx_id) AS n_txn
FROM ethereum.udm_events
WHERE block_timestamp >= CURRENT_DATE - 2
GROUP BY 1,2
) a ON a.from_address = b.to_address
WHERE degree_out <= 2 -- cutoff that keeps it from being an infinite loop
)
-- SELECT anything from the recursive query that is 4 degrees out
select * FROM eth_recurse
LIMIT 25000
;
Run a query to Download Data