SELECT

SPLIT(SPLIT(TO_ADDRESS, ':')[9], '\n')[0] AS USER_ADDRESS,

max(block_timestamp) AS MAX_BLOCK_TIMESTAMP

FROM flipside_prod_db.thorchain.bond_events

WHERE memo IS NOT NULL

GROUP BY USER_ADDRESS

SELECT

count(*) AS NODES_NUM,

SUM(CAST(SPLIT(SPLIT(TO_ADDRESS, ':')[7], '\n')[0] AS INT) / POW(10, 8)) AS AMOUNT_SUM,

AVG(CAST(SPLIT(SPLIT(TO_ADDRESS, ':')[7], '\n')[0] AS INT) / POW(10, 8)) AS AMOUNT_AVG,

MEDIAN(CAST(SPLIT(SPLIT(TO_ADDRESS, ':')[7], '\n')[0] AS INT) / POW(10, 8)) AS AMOUNT_MEDIAN

FROM flipside_prod_db.thorchain.bond_events

JOIN WALLETS ON MAX_BLOCK_TIMESTAMP = BLOCK_TIMESTAMP

WHERE USER_ADDRESS = SPLIT(SPLIT(TO_ADDRESS, ':')[9], '\n')[0]