SQL course solutions

Attempt each exercise before reading this separate answer key. Equivalent queries are acceptable when they preserve the metric contract. Expected-result CSVs are in validation/expected_outputs.

Exercise 01a

Both counts are 14. This supports unique non-null order identifiers in the observed table. It does not prove that every real-world order was loaded or that the selected population is correct.

SELECT COUNT(*) AS rows,COUNT(DISTINCT order_id) AS distinct_orders FROM aabha_sql.orders;

Expected output:

Runnable answer: sql/solutions/01a.sql

Exercise 01b

There are 19 lines for 14 orders because some baskets contain two product lines. A count at line grain is not an order count.

SELECT COUNT(*) AS order_lines FROM aabha_sql.order_items;

Expected output:

Runnable answer: sql/solutions/01b.sql

Exercise 02a

Bag and Guide are the most expensive catalogue products. Sorting by product_id after price makes any future ties deterministic.

SELECT product_id,product_name,list_price FROM aabha_sql.products ORDER BY list_price DESC,product_id LIMIT 2;

Expected output:

Runnable answer: sql/solutions/02a.sql

Exercise 02b

The result contains app and web once each. DISTINCT applies to the one selected column.

SELECT DISTINCT channel FROM aabha_sql.orders ORDER BY channel;

Expected output:

Runnable answer: sql/solutions/02b.sql

Exercise 03a

Order 1007 is pending and falls inside February. Cancelled orders elsewhere in the quarter are outside this question.

SELECT order_id,status FROM aabha_sql.orders WHERE status<>'completed'
 AND order_date>=DATE '2025-02-01' AND order_date<DATE '2025-03-01' ORDER BY order_id;

Expected output:

Runnable answer: sql/solutions/03a.sql

Exercise 03b

Customers 1, 4 and 8 are in Pune; customer 6 has an unknown city. IS NULL includes the unknown case deliberately.

SELECT customer_id,customer_name FROM aabha_sql.customers
WHERE city='Pune' OR city IS NULL ORDER BY customer_id;

Expected output:

Runnable answer: sql/solutions/03b.sql

Exercise 04a

Two mugs at 250 give 500 before discount. The line discount is 50 total, leaving 450. It is not 50 per mug.

SELECT quantity*unit_price AS before_discount,line_discount,
 quantity*unit_price-line_discount AS line_sales FROM aabha_sql.order_items WHERE order_id=1005;

Expected output:

Runnable answer: sql/solutions/04a.sql

Exercise 04b

Order 1008 is in February and receives 0; 1009 and 1012 are in March and receive 1. The next-month upper bound prevents overlap.

SELECT order_id,CASE WHEN order_date>=DATE '2025-03-01'
 AND order_date<DATE '2025-04-01' THEN 1 ELSE 0 END AS in_march
FROM aabha_sql.orders WHERE order_id IN (1008,1009,1012) ORDER BY order_id;

Expected output:

Runnable answer: sql/solutions/04b.sql

Exercise 05a

The counts 2 cancelled, 11 completed and 1 pending sum to all 14 orders. No status filter belongs in this all-status question.

SELECT status,COUNT(*) AS orders FROM aabha_sql.orders GROUP BY status ORDER BY status;

Expected output:

Runnable answer: sql/solutions/05a.sql

Exercise 05b

Customers 1, 2, 3 and 4 each have at least two completed orders. WHERE removes noncompleted rows before HAVING checks the customer aggregate.

SELECT customer_id,COUNT(*) AS completed_orders FROM aabha_sql.orders
WHERE status='completed' GROUP BY customer_id HAVING COUNT(*)>=2 ORDER BY customer_id;

Expected output:

Runnable answer: sql/solutions/05b.sql

Exercise 06a

The order has Notebook and Pen lines, each with quantity 2. The product key supplies a description without changing the historical price definition.

SELECT i.order_id,i.line_no,p.product_name,i.quantity
FROM aabha_sql.order_items AS i JOIN aabha_sql.products AS p ON p.product_id=i.product_id
WHERE i.order_id=1001 ORDER BY i.line_no;

Expected output:

Runnable answer: sql/solutions/06a.sql

Exercise 06b

Only Gita, customer 7, has no order at all. Hari has a cancelled order, so he does not belong in this result.

SELECT c.customer_id,c.customer_name FROM aabha_sql.customers AS c
LEFT JOIN aabha_sql.orders AS o ON o.customer_id=c.customer_id
WHERE o.order_id IS NULL ORDER BY c.customer_id;

Expected output:

Runnable answer: sql/solutions/06b.sql

Exercise 07a

Two order lines multiply two successful payments into four pairs. The true successful total 840 is doubled to 1680. The failed attempt must remain excluded.

SELECT i.order_id,COUNT(*) AS joined_rows,SUM(p.amount) AS inflated_payments
FROM aabha_sql.order_items AS i JOIN aabha_sql.payments AS p ON p.order_id=i.order_id
WHERE i.order_id=1006 AND p.status='succeeded' GROUP BY i.order_id;

Expected output:

Runnable answer: sql/solutions/07a.sql

Exercise 07b

Each summary has one order row before joining. Line sales and paid amount are both 840. This avoids accidental replication of either measure.

SELECT i.order_id,i.line_sales,p.paid_amount FROM (
 SELECT order_id,SUM(quantity*unit_price-line_discount) AS line_sales
 FROM aabha_sql.order_items WHERE order_id=1006 GROUP BY order_id
) AS i JOIN (
 SELECT order_id,SUM(amount) AS paid_amount FROM aabha_sql.payments
 WHERE order_id=1006 AND status='succeeded' GROUP BY order_id
) AS p ON p.order_id=i.order_id;

Expected output:

Runnable answer: sql/solutions/07b.sql

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

Exercise 09a

Ben has 1890 and Chen 1270. ROW_NUMBER produces exactly two display positions; DENSE_RANK would express a different tie policy.

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'
), customer_sales AS (
 SELECT customer_id,SUM(net_sales) AS net_sales FROM order_net GROUP BY customer_id
), ranked AS (
 SELECT customer_id,net_sales,ROW_NUMBER() OVER (ORDER BY net_sales DESC,customer_id) AS position
 FROM customer_sales
)
SELECT customer_id,net_sales,position FROM ranked WHERE position<=2 ORDER BY position;

Expected output:

Runnable answer: sql/solutions/09a.sql

Exercise 09b

February changes by -45, or -2.78%; March by 875, or 55.56%. January has no prior period and remains null. This fixture has no missing month; sparse calendars need additional handling.

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'
), monthly AS (
 SELECT date_trunc('month',order_date)::date AS month,SUM(net_sales) AS net_sales FROM order_net GROUP BY 1
), compared AS (
 SELECT month,net_sales,LAG(net_sales) OVER (ORDER BY month) AS previous_sales FROM monthly
)
SELECT month::text AS month,net_sales-previous_sales AS absolute_change,
 ROUND(100.0*(net_sales-previous_sales)/NULLIF(previous_sales,0),2) AS percent_change
FROM compared ORDER BY month;

Expected output:

Runnable answer: sql/solutions/09b.sql

Exercise 10a

Raw row 4 refers to customer 99, who is not present. Raw row 7 has no identifier at all and is intentionally excluded from this unknown-reference query.

SELECT r.batch_row,r.customer_id FROM aabha_sql.raw_orders AS r
LEFT JOIN aabha_sql.customers AS c ON c.customer_id=r.customer_id
WHERE r.customer_id IS NOT NULL AND c.customer_id IS NULL ORDER BY r.batch_row;

Expected output:

Runnable answer: sql/solutions/10a.sql

Exercise 10b

Rows 2 and 8 survive. Row 1 is an older version, and rows 3–7 fail one or more acceptance rules. Preserve excluded raw rows and their reasons; do not call the remaining result complete real-world truth.

WITH ranked AS (
 SELECT r.*,ROW_NUMBER() OVER (PARTITION BY source_order_id ORDER BY loaded_at DESC,batch_row DESC) AS position
 FROM aabha_sql.raw_orders AS r
)
SELECT r.batch_row,r.source_order_id,lower(trim(r.status_text)) AS status,r.amount
FROM ranked AS r JOIN aabha_sql.customers AS c ON c.customer_id=r.customer_id
WHERE r.position=1 AND r.source_order_id IS NOT NULL AND r.amount>=0
 AND lower(trim(r.status_text)) IN ('completed','pending','cancelled')
 AND r.order_date_text IS NOT NULL AND pg_input_is_valid(r.order_date_text,'date')
ORDER BY r.source_order_id;

Expected output:

Runnable answer: sql/solutions/10b.sql

Exercise 11a

Six customers registered before 1 March. Gita registered on the cutoff and Hari after it, so both are excluded.

SELECT COUNT(*) AS eligible_customers FROM aabha_sql.customers
WHERE signup_date<DATE '2025-03-01';

Expected output:

Runnable answer: sql/solutions/11a.sql

Exercise 11b

Six rows and six distinct identifiers support one-row-per-customer grain. A real system can still leak future completion status or late-arriving corrections into past features. Past order dates alone do not prove past availability.

WITH features AS (
WITH eligible_orders AS (
 SELECT o.order_id,o.customer_id,o.order_date,
 SUM(i.quantity*i.unit_price-i.line_discount) AS sales_before_refunds
 FROM aabha_sql.orders AS o JOIN aabha_sql.order_items AS i ON i.order_id=o.order_id
 WHERE o.status='completed' AND o.order_date >= DATE '2025-01-01'
   AND o.order_date < DATE '2025-03-01'
 GROUP BY o.order_id,o.customer_id,o.order_date
), customer_features AS (
 SELECT customer_id,COUNT(*) AS completed_orders,
 SUM(sales_before_refunds) AS sales_before_refunds,MAX(order_date) AS last_order_date
 FROM eligible_orders GROUP BY customer_id
)
SELECT c.customer_id,COALESCE(f.completed_orders,0) AS completed_orders,
 COALESCE(f.sales_before_refunds,0) AS sales_before_refunds,
 DATE '2025-03-01'-f.last_order_date AS recency_days
FROM aabha_sql.customers AS c LEFT JOIN customer_features AS f ON f.customer_id=c.customer_id
WHERE c.signup_date < DATE '2025-03-01'
ORDER BY c.customer_id
)
SELECT COUNT(*) AS rows,COUNT(DISTINCT customer_id) AS distinct_customers FROM features;

Expected output:

Runnable answer: sql/solutions/11b.sql

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

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.