Deliver a checked analysis capstone

Estimated study time: 120–180 minutes. Read, predict, run, explain, then attempt the independent work.

You are the analyst for the fictional retailer. The manager needs a concise quarter review before deciding what to investigate next. Your job is to produce reproducible SQL evidence and a cautious explanation. The dataset does not contain marketing spend, profit, inventory, website traffic or an experiment, so your report cannot attribute changes to a campaign or explain causal drivers.

Start by writing a metric contract in your own words. Use completed orders placed from 1 January 2025 inclusive to 1 April exclusive. Historical line prices and total line discounts define before-refund sales. Subtract refunds recorded before 1 April, assigning each refund to the original order month. The population includes all eight registered customers when reporting customer contribution, even if their completed-order count is zero. The source is a fixed synthetic snapshot.

Build the report in an inspectable sequence

First verify the source counts and keys. Next build a one-row-per-order intermediate result using independent line and refund summaries. Check that the 11 completed orders remain 11 distinct order identifiers. Reconcile the line-sales total, refunds and net sales. Only then aggregate to months and customer contribution. This sequence lets a reviewer locate a wrong result rather than inspect a single opaque query.

Your monthly output must contain month, completed_orders, buying_customers, sales_before_refunds, refunds, net_sales and net_average_order_value. Count buyers distinctly inside each month. A customer can buy in several months, so adding monthly buyer counts does not yield quarter-unique customers. The customer output must include all customers with identifier, synthetic name, completed-order count and net sales, sorted by sales descending and identifier as a tie-breaker.

The worked report below is a review target. For an assessed attempt, finish your own SQL and written findings before opening the separate solutions. If you are self-studying, use the checkpoint totals to diagnose a mismatch, but explain the cause of the mismatch rather than copying the answer. A correct number with an incorrect method will fail when the data changes.

Read the pattern without inventing a cause

The monthly net sales are 1620, 1575 and 2450. March has more completed orders than February, but a lower net average order value. Those two observations can coexist: total sales increased while the average value per completed order decreased. The data supports describing that arithmetic pattern; it does not establish why customers behaved differently.

Ben contributes the most quarter net sales in this fixture. That is a descriptive ranking of six purchasing customers, not evidence for targeting or treating real people differently. Gita and Hari remain in the customer output with zero completed sales for different reasons. Preserve those distinctions when proposing follow-up questions.

A useful summary says what changed, how the measure was defined, and what evidence is missing. For example, ask whether the order mix changed, but do not claim that a specific campaign caused the increase without campaign data and a suitable analysis. The best next question is one the current evidence motivates and the next data collection could actually answer.

Submit a reproducible package

Submit your two SQL queries, a brief metric contract, an audit note with source counts and reconciliation checks, and a 150–250 word manager summary. Include one chart or a simple monthly table, with INR units and the as-of cutoff. Also include your module 11 feature table and a short leakage-risk note so the analysis-to-AI connection remains visible.

Use the rubric in ASSESSMENT.md. Correct grain, coverage, date boundaries and safety are required for a pass; attractive charts cannot compensate for duplicated money. The reference implementation and explanation are in SOLUTIONS.md and sql/solutions. No certificate, external credit or learner progress is automatically issued by this package.

Worked example 13 monthly

Use this as a review target after an independent attempt. It keeps order counts and buyer counts distinct and calculates the average from monthly net sales divided by monthly completed orders.

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 result from the bundled dataset:

The runnable file is sql/examples/13_monthly.sql.

Worked example 13 customer

The population starts from customers, so zero-purchase customers survive. Ordering uses both amount and identifier for a stable result.

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 result from the bundled dataset:

The runnable file is sql/examples/13_customer.sql.

Independent practice

13a

Produce the monthly capstone report from the brief, retaining the exact seven output columns and chronological order. Include a written explanation of the refund attribution rule.

Hint: Test one order, then the order-grain intermediate result, then the monthly report.

13b

Produce the eight-row customer contribution report. Write a 150–250 word manager summary, one defensible next question, and one explicit claim the data cannot support.

Hint: A larger March total and a lower March average can both be correct. Distinguish description from explanation.

Check before moving on

The work is complete when another learner can reproduce the outputs, follow your metric definitions, inspect your checks and see the limits of the interpretation. Use the assessment rubric rather than file count as the finish line.