Técnica

Semi-join

Las filas que tienen al menos una correspondencia, cada una conservada una sola vez.

SELECT p.*
FROM products p
WHERE EXISTS (SELECT 1 FROM order_lines l WHERE l.product_id = p.product_id);

Un JOIN simple repetiría un producto una vez por venta.

SELECT COUNT(*) AS products,
       COUNT(*) FILTER (WHERE EXISTS (SELECT 1 FROM order_lines l WHERE l.product_id = p.product_id)) AS sold
FROM products p;

products	sold
80	74

products

sold

Probarlo en Randori

Practicar:Customers who ordered