connorhalUSD Flows
    Updated 2021-03-24
    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