WITH -- Active LPs is defined as any BTC LP that has an BTC Bech32 Segwit asset address deposit -- that has a deposit event (stake_events) that occurred after the last full LP withdrawal -- (10,000 basis points), or if there was no full withdrawal event. lp AS ( WITH d AS ( SELECT LOWER(ASSET_ADDRESS) AS address, MAX(e.BLOCK_TIMESTAMP) AS last_timestamp FROM thorchain.defi.fact_stake_events AS e JOIN thorchain.core.dim_block AS b ON e.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP WHERE UPPER(POOL_NAME) = 'BTC.BTC' AND LOWER(ASSET_ADDRESS) LIKE 'bc1%' AND ( CASE WHEN '{{block_height}}' = '' THEN TRUE ELSE b.BLOCK_ID <= '{{block_height}}' END ) AND ( CASE WHEN '{{date}}' = '' THEN TRUE ELSE b.BLOCK_TIMESTAMP < '{{date}}'::DATE + INTERVAL '1 DAY' END ) GROUP BY 1 ) , w AS ( SELECT LOWER(TO_ADDRESS) AS address, MAX(e.BLOCK_TIMESTAMP) AS last_timestamp FROM thorchain.defi.fact_withdraw_events AS e JOIN thorchain.core.dim_block AS b ON e.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP WHERE UPPER(POOL_NAME) = 'BTC.BTC' AND LOWER(TO_ADDRESS) LIKE 'bc1%' AND ( CASE WHEN '{{block_height}}' = '' THEN TRUE ELSE b.BLOCK_ID <= '{{block_height}}' END ) AND ( CASE WHEN '{{date}}' = '' THEN TRUE ELSE b.BLOCK_TIMESTAMP < '{{date}}'::DATE + INTERVAL '1 DAY' END ) AND BASIS_POINTS = 10000 GROUP BY 1 ) SELECT d.address FROM d LEFT JOIN w ON d.address = w.address WHERE (w.last_timestamp IS NULL OR d.last_timestamp > w.last_timestamp) ) -- Active Savers is defined as any BTC Savers that has an BTC Bech32 Segwit asset address deposit -- that has a deposit event (stake_events) that occurred after the last full Savers withdrawal -- (10,000 basis points), or if there was no full withdrawal event. , savers AS ( WITH d AS ( SELECT LOWER(ASSET_ADDRESS) AS address, MAX(e.BLOCK_TIMESTAMP) AS last_timestamp FROM thorchain.defi.fact_stake_events AS e JOIN thorchain.core.dim_block AS b ON e.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP WHERE UPPER(POOL_NAME) = 'BTC/BTC' AND LOWER(ASSET_ADDRESS) LIKE 'bc1%' AND ( CASE WHEN '{{block_height}}' = '' THEN TRUE ELSE b.BLOCK_ID <= '{{block_height}}' END ) AND ( CASE WHEN '{{date}}' = '' THEN TRUE ELSE b.BLOCK_TIMESTAMP < '{{date}}'::DATE + INTERVAL '1 DAY' END ) GROUP BY 1 ) , w AS ( SELECT LOWER(e.TO_ADDRESS) AS address, MAX(e.BLOCK_TIMESTAMP) AS last_timestamp FROM thorchain.defi.fact_withdraw_events AS e JOIN thorchain.core.dim_block AS b ON e.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP WHERE UPPER(POOL_NAME) = 'BTC/BTC' AND LOWER(TO_ADDRESS) LIKE 'bc1%' AND ( CASE WHEN '{{block_height}}' = '' THEN TRUE ELSE b.BLOCK_ID <= '{{block_height}}' END ) AND ( CASE WHEN '{{date}}' = '' THEN TRUE ELSE b.BLOCK_TIMESTAMP < '{{date}}'::DATE + INTERVAL '1 DAY' END ) AND BASIS_POINTS = 10000 GROUP BY 1 ) SELECT d.address FROM d LEFT JOIN w ON d.address = w.address WHERE (w.last_timestamp IS NULL OR d.last_timestamp > w.last_timestamp) ) -- Active Lending is defined as any BTC Bech32 Segwitt owner address that has a loan open event -- that occurred after the last full loan repayment event, or has no -- full loan repayment event at all. , lending AS ( WITH d AS ( SELECT LOWER(OWNER) AS address, MAX(e.BLOCK_TIMESTAMP) AS last_timestamp FROM thorchain.defi.fact_loan_open_events AS e JOIN thorchain.core.dim_block AS b ON e.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP WHERE UPPER(COLLATERAL_ASSET) = 'BTC.BTC' AND LOWER(OWNER) LIKE 'bc1%' AND ( CASE WHEN '{{block_height}}' = '' THEN TRUE ELSE b.BLOCK_ID <= '{{block_height}}' END ) AND ( CASE WHEN '{{date}}' = '' THEN TRUE ELSE b.BLOCK_TIMESTAMP < '{{date}}'::DATE + INTERVAL '1 DAY' END ) GROUP BY 1 ) , w AS ( SELECT LOWER(OWNER) AS address, MAX(e.BLOCK_TIMESTAMP) AS last_timestamp FROM thorchain.defi.fact_loan_repayment_events AS e JOIN thorchain.core.dim_block AS b ON e.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP WHERE UPPER(COLLATERAL_ASSET) = 'BTC.BTC' AND LOWER(OWNER) LIKE 'bc1%' AND ( CASE WHEN '{{block_height}}' = '' THEN TRUE ELSE b.BLOCK_ID <= '{{block_height}}' END ) AND ( CASE WHEN '{{date}}' = '' THEN TRUE ELSE b.BLOCK_TIMESTAMP < '{{date}}'::DATE + INTERVAL '1 DAY' END ) AND COLLATERAL_WITHDRAWN > 0 GROUP BY 1 ) SELECT d.address FROM d LEFT JOIN w ON d.address = w.address WHERE (w.last_timestamp IS NULL OR d.last_timestamp > w.last_timestamp) ) , u AS ( SELECT * FROM lp UNION SELECT * FROM savers UNION SELECT * FROM lending ) SELECT * FROM u ORDER BY address

Active THORChain BTC Addresses (LPs, Savers, Lending)