Answers and explanations · Deliver a checked analysis capstone

Exercise 13a

January, February and March net sales are 1620, 1575 and 2450. Their order counts are 3, 3 and 5. Refunds follow original order month under the before-1-April cutoff, not the refund event month as a cash-flow metric.

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 date_trunc('month',order_date)::date::text AS month,
 COUNT(*) AS completed_orders,COUNT(DISTINCT customer_id) AS buying_customers,
 SUM(line_sales) AS sales_before_refunds,SUM(refunded) AS refunds,SUM(net_sales) AS net_sales,
 ROUND(SUM(net_sales)/NULLIF(COUNT(*),0),2) AS net_average_order_value
FROM order_net GROUP BY 1 ORDER BY month;

Expected output:

Runnable answer: sql/solutions/13a.sql

Exercise 13b

The result retains all eight customers, including Gita and Hari at zero completed sales. A good summary distinguishes the March rise in total sales from the fall in average order value and avoids causal claims without supporting data.

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'
), totals AS (
 SELECT customer_id,COUNT(*) AS completed_orders,SUM(net_sales) AS net_sales
 FROM order_net GROUP BY customer_id
)
SELECT c.customer_id,c.customer_name,COALESCE(t.completed_orders,0) AS completed_orders,
 COALESCE(t.net_sales,0) AS net_sales
FROM aabha_sql.customers AS c LEFT JOIN totals AS t ON t.customer_id=c.customer_id
ORDER BY net_sales DESC,c.customer_id;

Expected output:

Runnable answer: sql/solutions/13b.sql

Example capstone manager summary

The synthetic retailer recorded INR 5645 in net sales from 11 completed orders during January to March 2025. This measure uses historical line prices after total line discounts and subtracts refunds recorded before 1 April, attributed to the original order month. It excludes pending and cancelled orders, tax, shipping and costs.

Monthly net sales were INR 1620 in January, INR 1575 in February and INR 2450 in March. March exceeded February by INR 875, or 55.56%. Completed orders increased from three to five, while net average order value fell from INR 525 to INR 490. The higher total therefore coincided with more orders and a lower average per order. These are descriptive changes; the dataset does not establish why they occurred.

Ben contributed the largest customer net-sales total, INR 1890. Two customers had no completed sales, and both remain visible in the customer report. The order-grain result reconciles INR 5860 before refunds minus INR 215 in refunds to the net total, without joining raw payment and line records together.

A useful next question is whether the March product mix explains the lower average order value. Campaign effectiveness or profitability cannot be concluded because marketing exposure and cost data are absent. The small synthetic sample is suitable for learning and verification, not a real commercial recommendation.