WITH ordercounts(product_id, ordercount)
     AS (SELECT product_id,
                COUNT(*)
         FROM   ORDER
         GROUP  BY product_id)
SELECT *
FROM   product
       JOIN ordercounts
         ON product.product_id = ordercounts.product_id
WHERE  ordercount < 10
       AND available_from > Dateadd(DAY, Getdate(), -30) --less than one month is poorly defined, so I've picked this