Technical case study · 03

Olist E-Commerce Analytics

A self-directed PostgreSQL study of marketplace fulfilment, seller-associated KPIs and customer-review outcomes, built around careful joins and reproducible business definitions.

PostgreSQL 17CTEsWindow functionsData qualityQuery plans

Independent analysis, not commissioned work. I used the historical public Olist dataset (2016–2018). Findings describe this sample; they are not production improvements or evidence of causation.

Analytical questions and data grain

I investigated how often eligible deliveries arrived after their estimated date, how seller-level metrics can avoid duplicate attribution, and how delivery performance relates to customer review scores.

Source tablesOrders · items · sellers · reviews

Reliable grainDistinct orders · seller-order grouping · selected review

AnalysisDelay rates · trends · review comparisons

Grain is critical: joining orders to order_items multiplies order rows. Seller metrics use an intermediate seller-order grain; review comparisons select one review record per order before joining.

Validated SQL findings

Indicator	Observed result
Orders imported	99,441
Eligible delivered orders	96,470
Late delivered orders	6,534 · 6.77%
Mean delay among late deliveries	10.62 calendar days
Orders with multiple sellers	1,278 · 1.30% of orders with items
Reviewed eligible deliveries	95,824 / 96,470

On-time or early deliveries had a 9.27% low-score (1–2 star) review share; 8–14-day-late deliveries had 80.15%. This is an observed association, not proof of a causal relationship.

SQL: weighted rolling late-delivery rate

Rather than averaging monthly percentages, I computed a three-month trailing rate using window sums of the underlying eligible-order counts and late-order counts.

SUM(eligible_orders) OVER (
    ORDER BY order_month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_3m_orders,
SUM(late_orders) OVER (
    ORDER BY order_month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_3m_late_orders

The complete query includes a defined eligibility filter (delivered orders with both dates) and excludes incomplete three-month windows.

Data quality and performance evidence

Five final integrity checks returned zero exceptions in the tested categories.
Two seller KPI formulations agreed for 3,095 sellers with no metric mismatches.
A local three-run query-plan comparison showed lower median time for the retained two-stage aggregation (313.7 ms vs 840.5 ms); this is not a general performance guarantee.

Interpretation limits

Review coverage was not identical across delivery groups, seller-associated lateness does not establish seller responsibility, and the public dataset does not isolate carriers, platform effects or product issues. Summed product item prices are not audited revenue or profit.

For reproducibility, I publish SQL scripts but not the source CSV files or raw review text.