SELECT 
    p.name as product_name,
    COUNT(DISTINCT t.id) as sales_count,
    SUM(tsl.quantity) as units_sold,
    SUM(tsl.unit_price_inc_tax * tsl.quantity) as revenue
FROM products p
LEFT JOIN transaction_sell_lines tsl ON p.id = tsl.product_id
LEFT JOIN transactions t ON tsl.transaction_id = t.id
WHERE t.type = "sell" AND t.status = "final"
GROUP BY p.id, p.name
ORDER BY units_sold DESC;