19 SQL queries against 96,470 delivered orders from the Olist Brazilian marketplace, covering Sept 2016 – Aug 2018 (23 months). Every figure below comes directly from query output — nothing is estimated.
Total revenue
R$15.42M
Delivered orders
96,470
Unique customers
93,350
Avg. order value
R$159.83
Q02 · Q09 · Q10
Revenue trajectory
Monthly revenue with a 3-month moving average to smooth noise, plus the cumulative running total showing overall growth.
Monthly revenue3-month moving avg
Cumulative revenue
Insight: revenue scaled roughly 8x between Q1 2017 and its Nov 2017 peak (R$1.15M — a Black Friday-driven spike), then plateaued at ~R$1.0-1.1M/month through mid-2018, suggesting the platform reached a mature, stable growth phase rather than continued exponential growth.
Q08
Month-over-month growth
Growth rate computed with LAG(). The first four months (Sept 2016 – Jan 2017) are excluded from the chart — near-zero base volume during platform ramp-up produces distorted percentages (e.g. +32,306% in Oct 2016) that would compress the rest of the chart.
Q04 · Q15 · Q19
Delivery performance & satisfaction
On-time vs. late delivery outcomes, the full distribution of delivery times by decile, and the measured correlation with review scores.
On-timeLate
−0.334
Correlation: delivery days ↔ review score (Q19)
A moderate negative correlation — as delivery takes longer, satisfaction measurably drops. Combined with the 1.72-point gap between on-time and late review scores, this quantifies delivery speed as a direct driver of customer satisfaction, not just an operational metric.
Delivery time deciles (Q15) — days from purchase to delivery, split into 10 equal-sized buckets. The gap between decile 9 and 10 (17.4 → 209.6 days) shows a long tail of severely delayed orders hidden by the platform-wide average.
Q03 · Q11 · Q12
Customer retention & concentration
Retention is the weakest link in the funnel — these three metrics quantify exactly where and how much.
3.0%
Repeat customer rate
Only 3 in 100 customers place a second order. Highest-leverage retention opportunity in the dataset.
46.71%
Revenue from top 15% of customers
Pareto-style concentration (Q11) — a disproportionate share of revenue comes from a small, valuable customer segment.
81.2 days
Avg. gap between 1st and 2nd purchase
The natural window for a win-back campaign (Q12) — re-engagement should target customers around day 60-80 post-purchase.
Q05
Revenue by state
Top 10 of 27 Brazilian states by revenue (DENSE_RANK()). São Paulo alone accounts for 37% of total platform revenue.
Q06 · Q13
Seller landscape
Top 10 sellers by revenue. Despite this spread, the top 10 sellers represent only 13.27% of total platform revenue (Q13, RANK()) — a healthily diversified seller base, not a single-point-of-failure risk.
Q07
Payment mix
Credit card dominates by value; boleto (Brazilian bank slip) is the clear second channel.
SQL techniques
What this project demonstrates
Window functions
LAG, RANK, DENSE_RANK, NTILE, ROW_NUMBER, and a 3-row moving average with ROWS BETWEEN.
Correlated subqueries
Per-customer installment comparison (Q16) — inner query re-evaluates against each outer row.
Data cleaning
Deduplicated late-arriving review records via partitioned ROW_NUMBER() before joining into the fact table.
Reusable views
fact_orders and vw_monthly_kpis act as a single source of truth for every downstream query and this dashboard.
Performance tuning
Added indexes on join/filter columns and verified index usage with EXPLAIN QUERY PLAN (Q18).
Statistical analysis
Pearson correlation (delivery days vs. review score) computed in pandas from raw query output.