Use Atlantic;
GO
WITH cte_Cust_Order(cust_id, cust_name, nbr_orders)
AS
(
SELECT C.customer_id
	, C.cust_firstname + ' ' + C.cust_lastname
	, (SELECT COUNT(*)
		FROM Orders AS O
		WHERE O.customer_id = C.customer_id)
	FROM Customer AS C
)
,
	cte_SellOver_500(c_id, ord_id, ord_qty, unit_price, final_sale) 
AS
(
SELECT Ord.customer_id, Ord.orders_id
	, Ord.orders_qty, Ord.orders_sell_price
	, Ord.orders_qty * Ord.orders_sell_price
	FROM Orders AS Ord
	WHERE Ord.orders_sell_price >= 499)

SELECT cust_id, ord_id, cust_name, nbr_orders, ord_qty, unit_price, final_sale
FROM cte_SellOver_500 AS SO INNER JOIN cte_Cust_Order AS CO
	ON SO.c_id = CO.cust_id
ORDER BY cust_id