Answers and explanations · Use AI as a checked SQL assistant

Exercise 12a

Move the completion condition into the left join ON clause to preserve nonbuyers, and count o.order_id so unmatched placeholder rows count as zero. The correct result retains eight customers.

SELECT c.customer_id,c.customer_name,COUNT(o.order_id) AS completed_orders
FROM aabha_sql.customers AS c
LEFT JOIN aabha_sql.orders AS o
 ON o.customer_id=c.customer_id AND o.status='completed'
GROUP BY c.customer_id,c.customer_name ORDER BY c.customer_id;

Expected output:

Runnable answer: sql/solutions/12a.sql

Exercise 12b

The average is 5645 / 11 = 513.18. Averaging 540, 525 and 490 gives each month equal weight despite different order counts, so it answers a different question.

WITH line_totals AS (
 SELECT order_id,SUM(quantity*unit_price-line_discount) AS line_sales
 FROM aabha_sql.order_items GROUP BY order_id
), refund_totals AS (
 SELECT order_id,SUM(amount) AS refunded
 FROM aabha_sql.refunds WHERE refund_date < DATE '2025-04-01' GROUP BY order_id
), order_net AS (
 SELECT o.order_id,o.customer_id,o.order_date,o.channel,
 l.line_sales,COALESCE(r.refunded,0) AS refunded,
 l.line_sales-COALESCE(r.refunded,0) AS net_sales
 FROM aabha_sql.orders AS o
 JOIN line_totals AS l ON l.order_id=o.order_id
 LEFT JOIN refund_totals AS r ON r.order_id=o.order_id
 WHERE o.status='completed' AND o.order_date >= DATE '2025-01-01'
   AND o.order_date < DATE '2025-04-01'
)
SELECT SUM(net_sales) AS net_sales,COUNT(*) AS completed_orders,
 ROUND(SUM(net_sales)/NULLIF(COUNT(*),0),2) AS net_average_order_value FROM order_net;

Expected output:

Runnable answer: sql/solutions/12b.sql