Olist E-commerce Marketplace: Advanced SQL Analytics

Project Overview

Brazilian E-Commerce Public Dataset by Olist

This project demonstrates advanced SQL proficiency through five strategic analyses of the Olist marketplace dataset, containing 9 interconnected tables with customer orders, products, sellers, payments, reviews, and geolocation data.

The analysis leverages MySQL 8.0+ features including Common Table Expressions (CTEs), window functions (RANK, DENSE_RANK, PERCENT_RANK, NTILE, LAG), and complex multi-table joins to extract business-critical insights.

Data Architecture & Modeling

Analysis 1: Logistics Performance by State

SQL Techniques Applied

• CTEs (Common Table Expressions): Modular query structure for calculating delivery days per order
• DATEDIFF(): Temporal analysis between purchase and delivery timestamps
• Window Functions: DENSE_RANK() OVER (ORDER BY) for bidirectional ranking
• Aggregations: AVG() and COUNT() grouped by state
• Data Quality Filters: Handling NULL timestamps and non-delivered orders

Query Architecture

-- Conceptual Structure (Simplified)
WITH delivery_per_order AS (
  -- Calculate days between purchase and delivery
  -- Join orders with customers to get state
  -- Filter only delivered orders with valid timestamps
),
avg_per_state AS (
  -- Aggregate average delivery days by state
  -- Count total orders per state
),
rankings AS (
  -- Apply DENSE_RANK for slowest (DESC)
  -- Apply DENSE_RANK for fastest (ASC)
)
SELECT * FROM rankings ORDER BY avg_delivery_days DESC;

Key Findings

Analysis 2: Revenue & Average Ticket by Category

SQL Techniques Applied

• Multi-table JOINs: order_items → products → category_translation (LEFT JOIN for missing translations)
• COALESCE(): Fallback logic for untranslated categories
• Distinct Aggregations: COUNT(DISTINCT order_id) to avoid double-counting multi-item orders
• Calculated Metrics: Ticket = SUM(price) / COUNT(DISTINCT orders)
• Dual Ranking: DENSE_RANK() by ticket AND by revenue for comparative analysis

Query Architecture

-- Conceptual Structure
WITH items_enriched AS (
  -- Join order_items with products and translations
  -- Use COALESCE for category name fallback
),
category_aggregation AS (
  -- SUM(price) as total_revenue
  -- COUNT(DISTINCT order_id) as distinct_orders
  -- Calculate average_ticket
),
rankings AS (
  -- DENSE_RANK() OVER (ORDER BY ticket DESC)
  -- DENSE_RANK() OVER (ORDER BY revenue DESC)
)
SELECT * FROM rankings ORDER BY revenue_rank, ticket_rank;

Key Findings

Analysis 3: Payment Behavior Analysis

SQL Techniques Applied

• CASE WHEN Logic: Classify payments as "cash" (installments ≤ 1) vs. "installment"
• ROW_NUMBER() Window: Identify dominant payment method per order (highest value)
• SUM() OVER (PARTITION BY): Calculate total order value across split payments
• PERCENT_RANK(): Percentile positioning of payment methods by average value
• Multi-CTE Pipeline: payments → per_order_fix → dominant_payment → aggregation

Query Architecture

-- Conceptual Structure
WITH payments_classified AS (
  -- CASE WHEN installments <= 1 THEN 'cash' ELSE 'installment'
),
order_dominant_method AS (
  -- ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY value DESC)
  -- SUM(value) OVER (PARTITION BY order_id) as total_order_value
),
final_aggregation AS (
  -- AVG(total_order_value) by payment_type and modality
  -- COUNT(*) as order_count
)
SELECT *, PERCENT_RANK() OVER (ORDER BY avg_value) FROM final_aggregation;

Key Findings

Analysis 4: Reviews vs. Delivery Delays

SQL Techniques Applied

• GREATEST() Function: Calculate delay as MAX(0, actual - estimated) to avoid negative values
• DATEDIFF() on Dates: Temporal delta between delivery and estimated dates
• NTILE(5) Window: Divide delay distribution into quintiles for performance segmentation
• JOIN with Filtering: Inner join reviews with orders (only delivered, non-null dates)
• Aggregation by Score: AVG(delay) and COUNT(*) grouped by review_score

Query Architecture

-- Conceptual Structure
WITH delay_calculation AS (
  -- GREATEST(DATEDIFF(delivered, estimated), 0) as delay_days
  -- Filter: status = 'delivered' AND dates NOT NULL
),
reviews_with_delay AS (
  -- JOIN reviews with delay_calculation on order_id
  -- Filter: review_score BETWEEN 1 AND 5
),
aggregated AS (
  -- AVG(delay_days) by review_score
  -- COUNT(*) as review_count
),
quintiles AS (
  -- NTILE(5) OVER (ORDER BY avg_delay ASC) as performance_quintile
)
SELECT * FROM quintiles ORDER BY review_score;

Key Findings

Analysis 5: Customer Loyalty & Recurrence

SQL Techniques Applied

• LAG() Window Function: Access previous order timestamp within customer partition
• PARTITION BY customer_unique_id: Calculate intervals per customer independently
• DATEDIFF() on LAG: Days between consecutive orders
• Nested Aggregations: AVG(interval) per customer, then AVG(orders) across customers
• LEFT JOIN for Intervals: Include customers with only 1 order (no interval)

Query Architecture

-- Conceptual Structure
WITH customer_orders AS (
  -- LAG(purchase_timestamp) OVER (PARTITION BY customer_unique_id ORDER BY timestamp)
  -- Join orders with customers to get unique_id
),
intervals AS (
  -- DATEDIFF(current_order, previous_order) as days_between
  -- Filter: WHERE previous_order IS NOT NULL
),
customer_aggregation AS (
  -- COUNT(*) as total_orders per customer
  -- AVG(days_between) as avg_interval per customer
)
SELECT * FROM customer_aggregation ORDER BY total_orders DESC LIMIT 50;

Key Findings

Technical Skills

Strategic Conclusions & Roadmap

Priority Actions (90-Day Roadmap)

Projected Impact (Conservative Estimates)

Methodology & Tools

Technology Stack

• Database: MySQL 8.0+ (CTEs, window functions, JSON support)
• Query Tool: MySQL Workbench / DBeaver
• Data Processing: Python (pandas, SQLAlchemy) for automation
• Version Control: Git for SQL script management
• Documentation: Markdown for query documentation and business logic