Answers and explanations · Build readable analysis with subqueries and CTEs

Exercise 08a

Asha and Divya have at least one refunded order. EXISTS retains each customer once even when there are multiple refunds.

SELECT c.customer_id,c.customer_name FROM aabha_sql.customers AS c
WHERE EXISTS (SELECT 1 FROM aabha_sql.orders AS o JOIN aabha_sql.refunds AS r ON r.order_id=o.order_id
 WHERE o.customer_id=c.customer_id) ORDER BY c.customer_id;

Expected output:

Runnable answer: sql/solutions/08a.sql

Exercise 08b

App contributes 2625 and web 3020, reconciling to 5645. This is a net-sales comparison, not a causal assessment of channel performance.

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 channel,SUM(net_sales) AS net_sales FROM order_net GROUP BY channel ORDER BY channel;

Expected output:

Runnable answer: sql/solutions/08b.sql