Before writing any analysis I profiled all nine source files and stress-tested the
assumptions the dataset invites. Ten findings materially change how the data can be
queried and what conclusions it can support. Each is reproducible from
sql/02_validation/.
Findings 1 to 6 constrain how queries must be written. Findings 7 to 10 constrain what the results are allowed to mean, and they are the more consequential half: each one invalidates a conclusion the data openly invites.
order_item_id = order_id for all 21,629 rows
max line items per order: 1
The schema models a classic order-header / order-line relationship, and the
obvious assumption is that a customer can buy several products in one order. In
this data that never happens: order_item_id is a literal copy of order_id.
Why it matters. Joining orders to order_items produces no row
multiplication, so COUNT(*) after the join equals the order count. That makes
several otherwise-risky aggregates safe by accident. Any query written for this
dataset should still use COUNT(DISTINCT order_id) so it survives a grain change.
Query: sql/02_validation/03_grain_and_duplicates.sql (a), (b)
order_date range: 2020-01-01 → 2024-07-30
Any query written as WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
returns zero rows when run today, and a "sellers inactive in the last 6
months" query returns every seller.
Resolution. All time-relative analysis is anchored to a fixed reference date
of 2024-07-30 (the maximum order date), stated explicitly in each query.
Query: sql/02_validation/04_null_profile.sql
| order_status | payment_status | delivery_status | rows |
|---|---|---|---|
| Completed | Payment Successed | Delivered | 17,802 |
| Returned | Refunded | Returned | 2,840 |
| Inprogress | Payment Successed | Shipped | 499 |
| Cancelled | Payment Failed | (no shipping row) | 488 |
Each order status maps to exactly one payment and delivery status, so the columns are
deterministically linked — though not strictly one-to-one: Payment Successed covers
both Completed and Inprogress. Knowing an order's status tells you the other two
with certainty.
A payment success rate (84.61%) and a return rate (13.13%) are distinct measurements,
but they are not independent evidence: both are determined by order_status, so
agreement between them confirms nothing about payments or logistics.
Why it matters. These are not independent signals, so correlations between them are artefacts of how the data was generated. They are reported here as descriptive counts, not as findings about payment or logistics behaviour.
Query: sql/02_validation/04_null_profile.sql
distinct duplicate-name groups: 177
customers affected: 535
name groups merging >1 real customer in orders: 128
There are five distinct customers named "Alicia Green", four of them in Ohio — so
grouping by state + name does not disambiguate them either.
Why it matters. Any per-customer metric grouped on name silently merges
different people, inflating order counts, lifetime value and return counts. All
customer aggregates in this project group on customer_id.
Query: sql/02_validation/03_grain_and_duplicates.sql (e)
rows where order_items.price_per_unit <> products.price: 0 of 21,629
gross margin range across 765 products: 55% – 95%
Every sale is transacted at exactly list price. Consequently
SUM(revenue - cogs*qty) / SUM(revenue) per product returns precisely
(price - cogs) / price — a fixed product property, not an emergent result of
trading.
Why it matters. Ranking products by margin percentage ranks a static column. The analytically interesting version is absolute profit contribution (unit margin × volume), which is reported alongside it.
Query: sql/02_validation/04_null_profile.sql
shipping contains shipping_date and return_date but no delivery date.
A metric built as return_date - shipping_date measures the return window, and
because it is null for the 17,802 delivered orders it is computed over returned
orders only.
Resolution. "Average delivery time" is reported as unanswerable. Two measurable substitutes are used instead:
avg_dispatch_lag_days=shipping_date - order_date(range 1–5, mean 3.0)avg_days_to_return=return_date - shipping_date, filtered to returns (range 7–14, mean 10.4)
customers with at least one return: 30 (IDs 669–698, contiguous)
returns from those customers: 2,840 (100% of all returns)
returns from the other 656 buyers: 0 across 18,742 orders
customers returning 100% of what they order: 28
Returns are not distributed across the customer base at a rate. They are assigned to a single contiguous block of customer IDs, and for 28 of those 30 customers every order they ever placed was returned.
Why it matters. This invalidates two questions rather than complicating them. Product-level
return rates (Q13) do not measure product quality — they measure which products the cohort
happened to buy, so a 40% return rate on a polka dot dress reflects sampling, not defects.
Customer segmentation by return behaviour (Q16) has no gradient to segment on: the population
is bimodal at 0% and 100%, with almost nothing between. Both are reported as descriptive counts
and explicitly disclaimed in INSIGHTS_AND_RECOMMENDATIONS.md.
Query: sql/02_validation/03_grain_and_duplicates.sql, extended by cohort
| Provider | Completed | Returned | Inprogress | Return rate | Mean dispatch lag |
|---|---|---|---|---|---|
| fedex | 14,346 | 0 | 0 | 0.00% | 3.01 days |
| dhl | 3,455 | 948 | 0 | 21.53% | 2.94 days |
| bluedart | 1 | 1,892 | 499 | 79.10% | 3.04 days |
fedex handles 14,346 orders and not one of them is ever returned. bluedart handles exactly one completed order. The provider column is a relabelling of the outcome, so a "provider return rate" is the assignment rule read backwards.
Why it matters. Any carrier comparison built on returns or delivery status is circular. The one dimension the providers can be compared on honestly — dispatch lag — separates them by 0.1 days across 21,141 orders, which is no separation at all. Q18 reports value handled and dispatch lag only, and states that carrier performance is not assessable.
Query: sql/02_validation/04_null_profile.sql, extended by provider
customer IDs 1–400: 3.7 orders per customer on average
customer IDs 401–600: 31.5
customer IDs 601–898: 94.9
211 customers (50+ orders) account for 19,835 of 21,629 orders — 91.7%
Geography follows the same pattern. Of 49 states in customers, only 39 appear against an
order, and 35 of those hold four or five buyers each. Ohio holds 168 buyers and 54.4% of all
sales value; Texas holds 50 and a further 22.1%. Of the 211 heaviest buyers, 176 are in Ohio
or Texas.
Why it matters. Lifetime-value rankings (Q7) and top-customer-per-state rankings (Q17) sort a generated ID, not a behaviour. Regional analysis (Q6) has no market structure underneath it — for the 35 five-buyer states, "top five customers" returns every customer in the state. The queries are correct and the aggregates are right; the conclusions a reader would naturally draw from them are not available.
Query: sql/02_validation/03_grain_and_duplicates.sql, extended by ID band
| Period | Orders per month | Completed share |
|---|---|---|
| 2023 average | 482 | 82.8% |
| Jan–Feb 2024 | 316 | — |
| Mar–Jul 2024 | 104 | 83.3% |
Monthly order volume falls by roughly 80% after February 2024 and stays flat at that level until the data stops on 30 July. Across the same collapse the status mix does not move: 82–83% of orders complete in every year from 2020 through 2024, and the return share holds between 12.7% and 13.7%.
A genuine demand collapse changes composition — cancellations rise, mix shifts, return rates move. Here every proportion holds constant while only the count falls, which is the signature of an extract tapering off rather than a business declining.
Why it matters. Q4 asks for month-over-month growth and the honest answer is that the trailing twelve months are dominated by this taper, not by seasonality or performance. The figures are reported as they compute, with the taper stated alongside them.
Query: sql/02_validation/01_row_counts.sql, extended by month
| Observation | Count | Interpretation |
|---|---|---|
| Customers with no orders | 212 | Registered, never purchased — a real segment |
| Products never ordered | 15 | Dead stock — a real finding |
| Orders with no shipping row | 488 | All Cancelled; correctly absent |
return_date null |
18,301 | Order was not returned |
Referential integrity is otherwise clean: zero orphaned foreign keys, no duplicate primary keys, one inventory row per product.
Synthetic dataset distributed for SQL training. Product names and prices appear to be scraped from real listings; customer names and all transactions are generated. No conclusion here describes real Amazon performance.