WITH suppliers AS ( SELECT user_address, SUM(amount_usd) as total_supplied FROM ethereum.erc20_balances WHERE contract_label = 'compound' AND balance_date = CURRENT_DATE - 1 AND amount_usd <> 0 GROUP BY 1 ), borrowers AS ( SELECT user_address, IFF(total_borrowed < 0, 0, total_borrowed) as total_borrowed FROM ( SELECT CASE origin_function_name WHEN 'borrow' THEN to_address WHEN 'repayBorrow' THEN from_address ELSE '' END as user_address, SUM( CASE origin_function_name WHEN 'borrow' THEN amount_usd WHEN 'repayBorrow' THEN -amount_usd ELSE 0 END ) as total_borrowed FROM ethereum.udm_events WHERE (from_label = 'compound' OR to_label = 'compound') AND (from_label_subtype = 'pool' OR to_label_subtype = 'pool') AND (origin_function_name IN ('borrow', 'repayBorrow')) GROUP BY 1 ) ), sandb AS ( SELECT NVL(a.user_address, b.user_address) as user_address, NVL(total_supplied, 0) as total_supplied, NVL(total_borrowed, 0) as total_borrowed, NVL(total_supplied,0) + NVL(total_borrowed,0) as net_yield_generating_amount FROM suppliers a LEFT JOIN borrowers b ON a.user_address = b.user_address ) SELECT CASE WHEN pct < 1 THEN '0% < roi < 1%' WHEN pct >= 1 AND pct < 10 THEN '1% < roi < 10%' WHEN pct >= 10 AND pct < 100 THEN '10% < roi < 100%' ELSE '>= 100%' END AS description, SUM(claimed_comp) as total_claimed, SUM(claimed_usd) as total_claimed_usd, MEDIAN(net_yield_generating_amount) as med_yield_generating_amount FROM ( SELECT block_timestamp as claim_date, tx_id, user_address, amount as claimed_comp, amount_usd as claimed_usd, total_supplied as supplied_usd, total_borrowed as borrowed_usd, net_yield_generating_amount, (amount_usd / ( CASE WHEN net_yield_generating_amount = 0 THEN 1 ELSE net_yield_generating_amount END )) * 100 as pct --this works cause if they repay/redeem it'll be automatically collected FROM ethereum.udm_events a JOIN sandb b ON a.to_address = b.user_address WHERE from_address = '0x3d9819210a31b4961b30ef54be2aed79b9c9cd3b' --comptroller AND contract_address = '0xc00e94cb662c3520282e6f5717214004a7f26888' --comp AND block_timestamp >= CURRENT_DATE - 1 ) GROUP BY 1

Compound Claim Bug Stats