OLIST E-Commerce SQL Analysis

TL;DR

Repeat purchase rate is 3%. Health and Beauty outperforms electronics. Delivery time is the strongest predictor of review score, and the bottleneck is the carrier, not the seller. Sao Paulo accounts for ~40% of revenue while northern states pay 30-40% freight premiums.

Those findings are only half of it. I came back to the project later to write the second notebook properly, working through the PostgreSQL internals and benchmarking each query (warm cache, 50 runs, median, Mann-Whitney). Running each query that many times showed most of the indexing advice did nothing on this schema, the one real win being a covering index that cut buffer reads about 140×. A few things are still unfinished, and they are the kind of thing I will come back to.

View on GitHub

The project

I built this to get better at SQL. OLIST is a Brazilian marketplace, similar to Amazon, with a dataset published on Kaggle that has enough tables and relationships to make the queries genuinely interesting, and enough rows that performance starts to matter. The goal wasn’t just to get answers from the data. It was to work through increasingly complex SQL patterns, understand why they work, and build up a set of techniques I’d actually use again.

The dataset covers ~100k real orders placed between 2016 and 2018 across 11 tables. Customers, sellers, and products are the core entities, orders is the central fact table, and order_items, order_payments, and order_reviews carry the transaction detail.

What stood out

A few results that were actually surprising. This is a selection, the full set is in the notebooks below.

From the analysis

• Repeat purchase rate is around 3%. Almost every order comes from a first-time buyer. I expected something closer to the 20-30% that’s typical for e-commerce. The cohort analysis confirms it isn’t a temporary dip. It’s consistent across every acquisition month, and retention is the biggest lever in the data by a wide margin. (practice notebook, §5 and §10)
• Health & Beauty leads revenue, not electronics. I assumed computers or phones would dominate. They don’t, and it isn’t close. H&B is a consumable category, so customers should come back, which makes a 3% repeat rate in a replenishment-heavy catalogue a contradiction worth understanding. (practice notebook, §6)
• Delivery time predicts review score more cleanly than anything else. Orders delivered in 0-3 days average 4.46 stars. Orders taking 22+ days average 3.01. The less obvious finding is that the bottleneck sits with the carrier (~9.3 days in transit) rather than the seller (~2.8 days to dispatch). Telling sellers to ship faster is the wrong fix. (practice notebook, §7)
• Sao Paulo accounts for roughly 40% of revenue. Customers in northern states (AM, PA, RR) face freight costs of 30-40% of item price because nearly all sellers are based in SP. They pay more and wait longer. It’s a supply-side problem as much as a demand-side one. (practice notebook, §4)
• At Risk customers are a R$5.7M re-engagement opportunity. The RFM segmentation flags 23,572 customers as At Risk, which is R$5.7M of revenue from people who have already bought here once. Winning them back is cheaper than finding someone new, and the data to identify them already exists. (practice notebook, §9)

From the optimisation work

• A covering index cut buffer reads about 140×. Once the query asked only for the two columns the index carries, Postgres switched to an Index Only Scan with Heap Fetches: 0 and never touched the main table. Buffer reads dropped from 104,762 to 746, and unlike raw timings that ratio holds rock solid on every run. (techniques notebook, §11)
• Most of the textbook indexing advice did nothing on this schema. Benchmarked properly (warm cache, 50 runs, median, Mann-Whitney), the foreign-key index and the expression index both left the query plan unchanged. The lesson was that buffer counts, not p-values, tell you whether an index is actually doing any work. (techniques notebook, §11)

Schema

11 tables. orders sits at the centre, linking customers to the items they bought, the payments they made, and the reviews they left. Each order_item ties an order to a specific product and seller, while customers and sellers connect out to geolocation via Brazilian zip-code prefixes. This star-like layout (transactional fact tables surrounded by descriptive dimensions) is what makes the dataset well suited to the joins, aggregations, and window functions used throughout.

Click outside the diagram or press Esc to release.

The diagram shows a small geolocation_zip_prefixes node. That one is a reference key for the shared zip-code prefix, not a base table, which is why the count is 11 tables and not 12.

The notebooks

The project is split across two notebooks. The first is the practice run through the analysis. The second is where I slowed down and worked through the PostgreSQL-specific techniques and query optimisation properly. Both are collapsed below. Click a bar to expand it.

What’s next

A few threads I left open, mostly in the second notebook, that I would pick up the same way I built the rest.

• Put the geography on an actual map. The Sao Paulo concentration only jumps out of a bar chart if you already know to look for it. A choropleth of order density across Brazilian states would make it land at a glance, and the state revenue query already returns everything the map needs.
• Bring acquisition channel into the retention story. The marketing leads table sat untouched the whole project. It carries acquisition-channel data that could tie customer segments back to where those customers came from. The 3% repeat rate reads as a product problem right now. It might turn out to be a channel problem once the source is in the picture.
• Put confidence intervals on the delivery-to-review finding. The pattern across the delivery buckets is clean, but I would want bootstrap intervals before quoting a specific number. The direction is not in doubt. The magnitude is the part still worth pinning down.
• Measure sellers on what they cancel, not just what they deliver. The current seller analysis only looks at delivered orders, which quietly excuses the sellers whose real problem is never reaching delivery at all. Cancellation and refund rates would round that out.

Stretch goal

Put the whole thing behind a small interactive dashboard. Every query here returns a clean result set, so wiring the headline ones into a lightweight dashboard would turn a page of static findings into something a reader can actually poke at. It is more plumbing than analysis, but it would make the work far easier to explore without opening a notebook.

How to run it

Development setup: hardcoded credentials, single-node Postgres, no production hardening. You’ll need Docker Desktop, plus Python 3.10+ for the notebooks.

docker-compose up -d # start PostgreSQL 15
docker-compose exec postgres psql -U postgres -d olist_db -c "\dt" # verify tables loaded

pip install -r notebook_requirements.txt
jupyter lab sql_practice_notebook.ipynb # open the analysis

Connection details:

The OLIST dataset is published under the CC BY-NC-SA 4.0 licence on Kaggle. The CSVs are downloaded separately.