BUSINESS_QUESTIONS.md

Business Questions

Twenty-one questions answered against the amazon_db schema, grouped by the stakeholder who would ask them. Each row links to the SQL that answers it and states the headline result.

All figures are anchored to the end of the data, 2024-07-30. "Value" means gross ordered value across all statuses; "revenue" means completed orders only. The distinction is defined in the data dictionary.

---

Sales and revenue — 01_sales_and_revenue.sql

#	Question	Answer
1	Which ten products generate the highest sales value, and on what volume?	Apple iMac Pro leads at $629,999 on 126 units. All ten are electronics; nine are computers or cameras
2	How is sales value distributed across categories?	Electronics is 89.7% of value on 34.7% of units. No other category exceeds 3.7%
3	Among customers who buy regularly, what is an order worth?	290 customers place more than five orders, averaging $591 per order and carrying 94.2% of all sales value
4	How has sales value moved month to month over the past year?	Down sharply. The twelve months to July 2024 fall from $365k to $26k, with the decline concentrated after February

Customers — 02_customer_analysis.sql

#	Question	Answer
5	Which registered customers have never ordered?	212 of 898 — 23.6% of the base has never transacted
7	What is each customer's completed lifetime value?	520 customers have completed revenue. The top decile holds 34.1% of it; the top quintile holds 61.6%
16	Which customers return more than five orders?	30 customers, and they account for every return in the dataset. See Finding 7
17	Who are the top five customers in each state?	190 rows across 39 states — five states have fewer than five buying customers

Products and inventory — 03_product_and_inventory.sql

#	Question	Answer
8	Which products are below ten units of stock?	51 SKUs, the lowest at one unit. None are electronics; none are at zero
12	Which products earn the most profit, and which carry the highest margin?	Blended margin is 75.3% on $7.82M of gross profit. The two rankings barely overlap — margin is a fixed product attribute here, profit is not
13	Which products are returned most often as a share of units sold?	Women's Polka Dot Dress at 40.9% of resolved units, against an overall rate of 13.8%

Sellers — 04_seller_performance.sql

#	Question	Answer
11	Which sellers generate the most completed revenue, and how reliably do their orders close?	Five sellers hold 65.6% of revenue at $1.30M–$1.40M each. All close above 96% of decided orders
21	Which sellers have made no sale in the six months to 2024-07-30?	Two — Clorox and Lysol. Neither has ever recorded a sale, which is an onboarding failure rather than churn

Fulfilment and payments — 05_operations.sql

#	Question	Answer
9	Which orders took more than seven days to dispatch?	None. The longest lag is five days and the 1–5 day spread is near uniform at ~20% per bucket
10	What share of payments succeeded, failed or were refunded?	84.61% succeeded, 13.13% refunded, 2.26% failed
14	Which successfully paid orders have no shipping record?	None. All 488 unshipped orders were cancelled after a failed payment
15	Which products are frequently bought together?	Not answerable. No order contains more than one product, so no pair co-occurs
18	How much value does each shipping provider handle, and how fast do they dispatch?	fedex carries 66% of shipped value. Dispatch lag is 2.94–3.04 days across all three — indistinguishable

Trends and regional mix — 06_revenue_trends.sql

#	Question	Answer
6	Which category leads in each state?	Electronics wins all 39 states, which reflects category mix rather than regional taste
19	Which products lost the most share of value between 2022 and 2023?	Unisex Sports Cap, down 93.8%. 282 of 680 products sold in both years declined

Transactional integrity — 05_procedures/

#	Question	Answer
20	Can a sale be recorded and stock reduced in one operation that cannot half-complete?	Yes. add_sales() validates input, locks the inventory row, writes order, payment and line item, then decrements stock. Five tests cover one success and four failure modes; each failure rolls back cleanly

---

Questions the data cannot answer

Four questions returned no answer. Each is reported with its evidence rather than dropped, because a null result that is proven is a finding.

Question	Why it fails	What was reported instead
Average delivery time	No delivery date exists in the schema	Dispatch lag, mean 3.0 days
Cross-sell affinity (Q15)	Every order contains exactly one product	Basket size distribution, proving the ceiling is one
Orders dispatched late (Q9)	The maximum lag in the data is five days	The full lag distribution behind the zero
Time since registration (Q5)	No registration date exists in the schema	The never-purchased list, without the tenure half

Q14 also returns zero rows, but that zero is a genuine clean bill of health rather than a schema limitation: no paid order is stuck unshipped.

---

Technique index

For readers scanning for specific SQL constructs.

Construct	Questions
Window functions (DENSE_RANK, LAG)	4, 6, 7, 12, 17, 19
FILTER (WHERE ...) conditional aggregation	11, 12, 13, 16, 18, 19
CTEs, including multi-stage	4, 6, 11, 12, 13, 15, 16, 17, 19
Correlated subqueries / NOT EXISTS	5, 21
Scalar subquery for share-of-total	2
LEFT JOIN used to prove absence	14, 21
Five-table joins	6, 19
PL/pgSQL, transactions, row-level locking	20