connorhalUSD 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
36
›
⌄
with recursive eth_recurse
-- Column names for the "view"/CTE
(root, from_address, to_address, degree_out,amount_transfered,n_txn)
as
-- Common Table Expression
(
-- Anchor
SELECT a.from_address_name AS root,
CASE WHEN a.from_address_name IS NULL THEN 'Users'
ELSE a.from_address_name END AS from_address,
CASE WHEN a.to_address_name IS NULL THEN 'Users'
ELSE a.to_address_name END AS to_address,
1 AS degree_out, SUM(a.amount) AS amount_transfered, COUNT(DISTINCT tx_id) AS n_txn
FROM ethereum.udm_events a
WHERE a.from_address = '0x0000000000000000000000000000000000000000'
AND contract_address = '0xbc6da0fe9ad5f3b0d58160288917aa56653660e9' -- any alUSD minted
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.amount_transfered,a.n_txn
FROM eth_recurse b
LEFT JOIN (
SELECT
CASE WHEN from_address_name IS NULL THEN 'Users'
ELSE from_address_name END AS from_address,
CASE WHEN to_address_name IS NULL THEN 'Users'
ELSE to_address_name END AS to_address,
SUM(amount) AS amount_transfered, COUNT(DISTINCT tx_id) AS n_txn
FROM ethereum.udm_events
WHERE block_timestamp >= CURRENT_DATE - 2
AND contract_address = '0xbc6da0fe9ad5f3b0d58160288917aa56653660e9'
GROUP BY 1,2
) a ON a.from_address = b.to_address
Run a query to Download Data