21,629 orders. 21 business questions. Four of them have no honest answer — and proving that was the most valuable part of the analysis.
A complete SQL workflow over a nine-table e-commerce schema, from schema design to ranked recommendations: relational design, a reproducible load, a full data-quality audit, twenty-one analytical queries, and a transactional stored procedure. The audit came first and it changed everything downstream — ten findings constrain what the analysis is allowed to conclude, and four questions the dataset openly invites turned out to be unanswerable.
PostgreSQL 14+ · Window Functions · CTEs · PL/pgSQL · Transactions & Row Locking · Schema Design · Data Quality Auditing
Documentation — Data Quality Audit · Business Questions · Data Dictionary · Insights & Recommendations
| Metric | Value |
|---|---|
| Scale | 21,629 orders · 898 customers · 765 products · 54 sellers · 9 tables |
| Coverage | 2020-01-01 → 2024-07-30 · 4.6 years · 6 categories · 39 states with activity |
| Gross ordered value | $12,642,070.56 across all order statuses |
| Completed revenue | $10,382,848.85 — gross overstates realised revenue by 21.8% |
| Gross profit | $7,815,072.36 at a blended margin of 75.27% |
| Revenue concentration | Electronics is 89.7% of value on 34.7% of units |
| Profit concentration | Top 20 products carry 28.2% of all profit |
| Value that never converts | $2.26M — $1.67M returned, $308k cancelled, $278k stuck in progress |
| Audit findings | 10 — four of them invalidate a conclusion the data invites |
The finding that matters: every one of the 2,840 returns in this dataset belongs to the same 30 customers, holding a contiguous block of IDs, 28 of whom return 100% of what they order. The other 656 buyers return nothing across 18,742 orders. Product return rates and customer return segmentation are therefore not measuring product quality or customer behaviour — they are measuring which SKUs that cohort happened to buy. Three further artefacts of the same kind are documented in the audit, and each is disclaimed at the point of use rather than quietly reported.
The project follows the analytics workflow, and this README is organised the same way.
| Phase | Question it answers | Section |
|---|---|---|
| Ask | What does the business need to know? | 1 |
| Prepare | What data exists, and how is it modelled? | 2 |
| Process | Is it trustworthy, and what had to be fixed? | 3 |
| Analyze | How were the questions answered in SQL? | 4 |
| Share | What do the numbers actually say? | 5 |
| Act | What should be done about it? | 6 |
Twenty-one questions spanning seven stakeholder domains, scoped before any query was written:
| Domain | Questions | Representative ask |
|---|---|---|
| Sales & revenue | 4 | How is value distributed across categories, and how concentrated are we? |
| Customers | 4 | What is each customer worth, and who has never purchased? |
| Products & inventory | 3 | Which products earn the most profit, and what is running out? |
| Sellers | 2 | Who generates the most revenue, and who has gone quiet? |
| Fulfilment & payments | 5 | How fast do we dispatch, and what share of payments fails? |
| Trends & regional mix | 2 | What declined year on year, and what leads in each state? |
| Transactional integrity | 1 | Can a sale be recorded and stock reduced without half-completing? |
The full set, each with its headline answer and the SQL that produces it, is indexed in docs/BUSINESS_QUESTIONS.md.
Four questions have no answer in this data, and each is reported with the evidence behind the null rather than dropped or approximated:
| Question | Why it fails | Reported instead |
|---|---|---|
| Average delivery time | No delivery date exists in the schema | Dispatch lag, mean 3.0 days |
| Cross-sell affinity | Every order contains exactly one product | Basket-size distribution, proving the ceiling is one |
| Orders dispatched late | The maximum lag in the data is five days | The full lag distribution behind the zero |
| Customer tenure | No registration date exists in the schema | The never-purchased list, without the tenure half |
A proven null is a finding. Manufacturing an answer from a column that does not exist is not.
| Source | Nine CSV files, unmodified — data/raw/ |
| Grain | One row per order, per line item, per payment, per shipment, per SKU |
| Volume | 21,629 orders · 21,629 line items · 21,141 shipments · 765 SKUs |
| Coverage | 2020-01-01 → 2024-07-30 · 6 categories · 54 sellers · 39 states with activity |
| Target | PostgreSQL 14+ · pgAdmin 4 / psql |
The schema is built parent-first so every foreign key has a target, in four dependency levels: reference tables (category, customers, sellers) → products → orders → the four transactional children. Full DDL in sql/00_setup/02_create_schema.sql, and every column is documented in docs/DATA_DICTIONARY.md.
Design decisions worth naming:
- Money is
NUMERIC, neverFLOAT. These columns are summed across 21,629 rows and feed percentage calculations. Binary floating point introduces rounding drift that compounds at exactly the scale this dataset operates at. - VARCHAR widths are set from measured maximum lengths, not guessed.
sellers.originhas a measured maximum of 6 characters — a width under that fails the load outright, which is the kind of error that only appears at import time. - No fabricated columns. The source has no customer address, so no address column exists. A placeholder filled with dummy text would be invented data wearing the costume of a model.
- Indexes on the foreign keys the analysis actually joins on — seven of them, added at build time rather than retrofitted.
The load is client-side \copy with explicit column lists. Column lists match the order of columns in each CSV rather than their header text, which matters because customers.csv ships a header of Customer ID with a space, shipping.csv ships shipping providers plural, and several files carry a UTF-8 BOM. Because HEADER true skips the first line entirely, none of it reaches PostgreSQL.
The audit ran before the analysis, not after it. Five validation scripts profile row counts, referential integrity, grain, nulls and value cleanliness. Ten findings materially change what the data can support, and they split into two kinds.
Findings 1–6 constrain how queries must be written:
| # | Finding | Consequence |
|---|---|---|
| 1 | orders ↔ order_items is 1:1, not 1:many |
Header→line joins never fan out; COUNT(DISTINCT) used anyway so queries survive a grain change |
| 2 | Data ends 2024-07-30 | CURRENT_DATE returns zero rows; every time-relative query anchored to a fixed date |
| 3 | Order, payment and delivery status are deterministically linked | Three metrics, one signal — agreement between them confirms nothing |
| 4 | 535 of 898 customers share a name | Never group customers by name; customer_id is the only safe key |
| 5 | No discounts — price always equals list | Margin % is a static attribute, so ranking on it sorts a fixed column |
| 6 | No delivery date exists | "Average delivery time" is unanswerable, not approximable |
Findings 7–10 constrain what the results are allowed to mean, and they are the more consequential half:
| # | Finding | Invalidates |
|---|---|---|
| 7 | All 2,840 returns belong to 30 customers; 28 return 100% of their orders | Product return rates; customer return segmentation |
| 8 | Shipping provider is assigned by outcome — fedex has 14,346 orders and zero returns | Any carrier performance comparison |
| 9 | Order volume tracks customer ID — IDs 601–898 average 94.9 orders, IDs 1–400 average 3.7 | Lifetime-value rankings; regional strategy |
| 10 | Volume falls 80% after Feb 2024 while status mix holds constant at 82–83% completed | Trend and seasonality analysis |
Full evidence, with the reproducing queries, in docs/DATA_QUALITY_FINDINGS.md.
Two defects the foreign keys could not catch were found and fixed in 05_data_cleaning.sql:
- Trailing whitespace on status values.
'Refunded 'and'Returned 'carry a trailing space, soWHERE delivery_status = 'Returned'silently returns zero rows. Detected first, then trimmed. - 1:1 relationships unenforced. A foreign key stops orphans; it does not stop a second payment row for the same order.
UNIQUEconstraints were added topayments,shippingandinventoryso the database enforces what the documentation claims.order_items.order_idis deliberately left non-unique — the model permits multiple lines even though this data never exercises it.
One derived column. order_items.total_sale is materialised once as quantity × price_per_unit, so twenty-one downstream queries reference a single verified column instead of repeating the arithmetic. The transformation script ends with a check that must return zero.
Legitimate gaps, distinguished from defects: 212 customers with no orders (a real segment), 15 products never ordered (dead stock), 488 orders with no shipping row (all cancelled, correctly absent), 18,301 null return dates (not returned). Referential integrity is otherwise clean — zero orphaned keys, no duplicate primary keys.
Queries are organised by domain in sql/04_analysis/, each carrying its business question, approach, and the caveats that apply to its result.
Measure discipline is the backbone of the analysis. Four denominators are used, and every query states which one in its column names:
| Measure | Definition | Applied to |
|---|---|---|
| Gross ordered value | All statuses — $12,642,070.56 | Demand questions: what customers chose to buy |
| Completed revenue | Completed only — $10,382,848.85 |
Realised revenue, lifetime value, seller performance |
| Resolved | Completed + Returned |
Return-rate denominators, where an outcome exists |
| Decided | Completed + Cancelled |
Completion-rate denominators, where a decision was made |
Conflating the first two overstates revenue by 21.8%, and it is the single easiest mistake to make on this schema.
Techniques by question:
| Construct | Questions |
|---|---|
Window functions — DENSE_RANK, LAG, SUM() OVER () |
4, 6, 7, 9, 10, 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 |
LEFT JOIN used to prove absence |
14, 21 |
| Five-table joins | 6, 19 |
| PL/pgSQL, transactions, row-level locking | 20 |
Question 20 is a transactional stored procedure, not a query. add_sales() validates its input, locks the inventory row with FOR UPDATE, writes the order, payment and line item, then decrements stock — all inside one transaction, so stock can never be reduced without a matching order and two concurrent sales cannot both read the same stock level and oversell. The test harness covers one success and four distinct failure modes — insufficient stock, unknown product, negative quantity, duplicate order ID — then resets the database to its pre-test state and reconciles against the baseline total.
Two smaller decisions that change the answers:
- Return rates carry a twenty-unit volume floor. Without it, the top of the list is products that sold once and came back once.
- Zero-row results report their evidence. Q9 returns no late dispatches, so the full lag distribution is returned alongside to show why. Q14 uses a
LEFT JOINdeliberately — with an inner join the null check could never be true, so the zero would be guaranteed by the query rather than found in the data.
Full analysis in docs/INSIGHTS_AND_RECOMMENDATIONS.md.
Electronics is 89.7% of sales value on 34.7% of units. The gap is price, not preference — the average electronics SKU lists at $769 against $36–$61 everywhere else.
| Category | Share of value | Share of units | SKUs |
|---|---|---|---|
| electronics | 89.7% | 34.7% | 308 |
| Sports & Outdoors | 3.6% | 18.7% | 69 |
| Toys & Games | 2.8% | 15.9% | 68 |
| Pet Supplies | 2.1% | 18.2% | 82 |
| clothing | 1.1% | 7.0% | 132 |
| home & kitchen | 0.7% | 5.5% | 106 |
The five non-electronics categories hold 60% of the catalogue and return 10.3% of value between them, absorbing merchandising and inventory attention out of all proportion to what they contribute.
Blended margin is 75.27% on $7.82M of gross profit. The top twenty products carry 28.2% of all profit, and the Apple iMac Pro alone carries 5.9% of it on 126 units. Ranking by margin percentage is meaningless here — with no discounts anywhere in the data, margin is a fixed property of each SKU, so that ranking sorts a static column. Absolute profit contribution is the only version that reflects trading.
51 SKUs sit below ten units. None of them are electronics — every one is Pet Supplies, Toys & Games or Sports & Outdoors, and the highest-earning among them turned over $4,200 in the trailing twelve months. Fifteen products have never been ordered at all.
| Outcome | Orders | Value | Note |
|---|---|---|---|
| Returned | 2,840 | $1,673,279 | Refunded in full |
| Cancelled | 488 | $308,224 | Every one follows a failed payment |
| Inprogress | 499 | $277,718 | Never resolved — oldest dates to 2020-01-01 |
Cancellation and payment failure are the same 488 orders; there is no other cause of cancellation in the data. Separately, 499 orders have been sitting in a non-terminal status for up to four and a half years — these are stuck records, not a pipeline.
No order took more than five days to dispatch, and the 1–5 day spread is near uniform at ~20% per bucket. No successfully paid order is sitting unshipped. Payments succeed on 84.61% of transactions — refunds are reversals, and adding them back would inflate that to a false 97.74%.
Two sellers, Clorox and Lysol, have never recorded a single sale. That is an activation failure, not churn, and the two demand opposite responses.
| Apparent finding | Why it does not hold |
|---|---|
| "Bluedart returns 79% of shipments; fedex returns none" | Carrier is assigned by outcome, not chosen. Dispatch lag is 2.94–3.04 days across all three — indistinguishable on the one dimension actually measured |
| "These products have a return problem" | All returns come from 30 customers; the rates measure what that cohort ordered |
| "Ohio is the growth market" | Ohio holds 168 buyers and 54.4% of value; 35 of 39 states hold four or five buyers each |
| "These are our most valuable customers" | Order volume tracks customer ID — the ranking sorts the ID column |
| "Sales collapsed in 2024" | Volume falls 80% while status mix holds constant. A real collapse moves the mix; an extract ending does not |
| Priority | Action | Basis |
|---|---|---|
| 1 | Build a payment retry-and-notify flow | Addresses 100% of the $308k cancellation pool — not a fraction of it. The orders are already identified |
| 2 | Add an ageing alert on non-terminal order statuses | 499 orders, $278k, stuck up to 4.5 years with nothing surfacing them |
| 3 | Capture multi-item baskets in the order model | Unblocks attachment rate — the only way to justify or prune the 457-SKU tail |
| 4 | Add a delivery-confirmation timestamp | Delivery performance is currently unmeasurable at all |
| 5 | Add stock-level history | Enables days-of-supply on the 20 SKUs carrying 28% of profit |
| 6 | Route the two non-trading sellers to activation | Two named accounts, onboarded, never traded |
Items 3 to 5 are data-model changes rather than business actions, and that is the honest shape of the result: the operational questions this dataset answers well, it answers cleanly — and the commercially interesting ones are blocked on instrumentation that does not exist yet. Naming that gap precisely is more useful than filling it with an estimate.
My project repositories are each organised around the analytics lifecycle — Ask → Prepare → Process → Analyze → Share → Act. This one is where Prepare, Process and Analyze are strongest: relational modelling, a reproducible load, and an audit rigorous enough to overturn four conclusions the data invites before a single insight is published.
It is the deliberate counterpart to my dashboards repository, where Share and Act carry the weight. A dashboard is only as trustworthy as the model beneath it — this project is that model, built and stress-tested in the open.
One idea runs through all of it: a query that returns a number is not the same as a number that means something. Every result here is reported with the denominator it used, the status filter it applied, and the caveat that governs how far it can be pushed.
| Area | Applied in |
|---|---|
| Relational design | Nine-table normalised schema, parent-first build order, enforced foreign keys, measured VARCHAR widths, NUMERIC for money, FK indexing |
| Reproducible loading | Client-side \copy with explicit column lists, dependency-ordered loads, BOM and header-mismatch handling |
| Data quality auditing | Row counts, referential integrity, grain and duplicate profiling, null profiling, value cleanliness, cohort and distribution analysis |
| Constraint engineering | UNIQUE constraints added where the business rule is 1:1; deliberately withheld where the model permits 1:many |
| Analytical SQL | Window functions, multi-stage CTEs, conditional aggregation with FILTER, correlated subqueries, five-table joins, share-of-total via scalar subquery |
| Procedural SQL | PL/pgSQL procedure with input validation, exception raising, FOR UPDATE row locking, and transactional rollback |
| Testing | Five-case test harness covering one success and four failure modes, with a bounded reset and a reconciliation baseline |
| Analytical writing | Translating results into ranked recommendations — including four honest nulls and four disclaimed artefacts |
amazon-advanced-sql-analysis/
├── README.md # this file
├── assets/
│ ├── erd.png # entity relationship diagram
│ └── thumbnail.png # project title card
│
├── data/
│ ├── README.md # source file notes, row counts, encoding quirks
│ └── raw/ # 9 unmodified CSVs
│
├── docs/
│ ├── DATA_QUALITY_FINDINGS.md # the audit — 10 findings with reproducing queries
│ ├── DATA_DICTIONARY.md # every table, column, key, domain and measure
│ ├── BUSINESS_QUESTIONS.md # 21 questions, answers, and technique index
│ └── INSIGHTS_AND_RECOMMENDATIONS.md # the stakeholder read
│
└── sql/
├── 00_setup/ # database + schema DDL, built parent-first
├── 01_import/ # \copy load, FK dependency order
├── 02_validation/ # row counts · integrity · grain · nulls · cleaning
├── 03_transformations/ # derived total_sale column
├── 04_analysis/ # the 21 business questions, grouped by domain
└── 05_procedures/ # add_sales() + test harness and reset
Requires PostgreSQL 14 or later. Run from the repository root — the load script uses relative paths.
# 1. Create the database
psql -U postgres -f sql/00_setup/01_create_database.sql
# 2. Build the schema (parent-first, FKs enforced)
psql -U postgres -d amazon_db -f sql/00_setup/02_create_schema.sql
# 3. Load the CSVs (client-side \copy — must run from repo root)
psql -U postgres -d amazon_db -f sql/01_import/01_load_data.sql
# 4. Validate before trusting anything
psql -U postgres -d amazon_db -f sql/02_validation/01_row_counts.sql
psql -U postgres -d amazon_db -f sql/02_validation/02_referential_integrity.sql
psql -U postgres -d amazon_db -f sql/02_validation/05_data_cleaning.sql
# 5. Derive total_sale
psql -U postgres -d amazon_db -f sql/03_transformations/01_add_total_sale.sql
# 6. Run the analysis
psql -U postgres -d amazon_db -f sql/04_analysis/01_sales_and_revenue.sqlReconciliation baseline: after step 5, SELECT ROUND(SUM(total_sale),2) FROM order_items must return 12642070.56. The procedure test harness in sql/05_procedures/ restores this exact figure after running, so the demonstration is repeatable.
- The dataset is static and ends 2024-07-30. Every time-relative result is anchored to that date. Nothing here reflects a live system.
- Four questions are unanswerable — delivery time, cross-sell affinity, customer tenure and true late dispatch. Each is documented with its evidence rather than estimated.
- Four results are artefacts of data generation, not business signal: return attribution, carrier performance, customer value ranking and the 2024 trend. Acting on any of them would be worse than acting on nothing, and each is disclaimed at the point of use.
- Inventory is a single snapshot with no history, so days-of-supply and stock-turn cannot be derived.
warehouse_idis constant across all 765 rows, so there is no multi-warehouse dimension despite the column existing.- No margin analysis reflects trading behaviour, because no order in the dataset is ever discounted. Margin percentage is a fixed product attribute here.
This is a synthetic dataset used for portfolio demonstration. Product names and prices appear to be scraped from real listings; customers, orders, sellers and all transactions are generated. Nothing here describes real Amazon performance, and the recommendations are written to demonstrate how findings translate into action, not as advice to any real business.
Every figure in this repository is computed directly from the SQL that sits beside it.
Built by Shayan Bhatti · Data Analyst