1SELECT 
2    n.name,
3    COUNT(DISTINCT l.id) as listing_count,
4    COUNT(DISTINCT o.id) as offer_count,
5    MIN(l.price_adj) as current_floor,
6    MAX(o.price_adj) as best_offer
7FROM "@blt.doma_testnet_core.names" n
8LEFT JOIN "@blt.doma_testnet_core.listings" l ON n.name = l.name AND l.expires_at > CURRENT_TIMESTAMP
9LEFT JOIN "@blt.doma_testnet_core.offers" o ON n.name = o.name AND o.expires_at > CURRENT_TIMESTAMP
10GROUP BY n.name
11HAVING COUNT(DISTINCT l.id) > 0 OR COUNT(DISTINCT o.id) > 0
12ORDER BY (COUNT(DISTINCT l.id) + COUNT(DISTINCT o.id)) DESC
13LIMIT 50