Question

You want to find customers who placed orders totaling more than 500, but only include those customers who have placed at least 3 orders. Which CTE-based query correctly achieves this?

Accepted Answer

WITH order_summary AS (
  SELECT customer_id, COUNT(order_id) AS order_count, SUM(amount) AS total_amount
  FROM orders
  GROUP BY customer_id
  HAVING SUM(amount) > 500 AND COUNT(order_id) >= 3
)
SELECT * FROM order_summary;

Answer

WITH order_summary AS (
  SELECT customer_id, COUNT(order_id) AS order_count
  FROM orders
  GROUP BY customer_id
  HAVING COUNT(order_id) >= 3
)
SELECT * FROM order_summary WHERE total_amount > 500;

Answer

WITH order_summary AS (
  SELECT customer_id, SUM(amount) AS total_amount
  FROM orders
  GROUP BY customer_id
  HAVING SUM(amount) > 500
)
SELECT * FROM order_summary WHERE order_count >= 3;

Answer

WITH order_summary AS (
  SELECT customer_id, SUM(amount) AS total_amount
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM order_summary WHERE total_amount > 500 AND order_count >= 3;

You want to find customers who placed orders totaling more than 500, but only include those customers who have placed at least 3 orders. Which CTE-based query correctly achieves this?

Step 1: Understand the requirements

Step 2: Check each option's logic

Final Answer:

Quick Check:

Want More Practice?