SELECT c.name,COUNT(t.id) purchases,COUNT(DISTINCT p.id) products_bought,COUNT(DISTINCT t2.contact_id) referred_customers FROM contacts c JOIN transactions t ON c.id=t.contact_id JOIN transaction_sell_lines tsl ON t.id=tsl.transaction_id JOIN products p ON tsl.product_id=p.id LEFT JOIN transactions t2 ON t.transaction_date<t2.transaction_date WHERE t.type="sell" GROUP BY c.id,c.name ORDER BY referred_customers DESC;