First independent analysis · Advanced beginner · Lesson 27 of 99 · about 3 weeks
Project: e-commerce analysis from a spec (Olist)
After this you have a complete, independently produced SQL analysis and dashboard of a real multi-table dataset.
Why it matters
Spec. Dataset: the Brazilian E-Commerce Public Dataset by Olist on Kaggle (free account required): about 100,000 orders from 2016 to 2018 across nine linked CSV files (orders, order items, payments, reviews, customers, sellers, products, geolocation and a category-name translation). Load it into PostgreSQL or DuckDB. Answer these six questions: 1. How did monthly order volume and revenue change over the dataset's period? Exclude incomplete months and say which ones you excluded and why. 2. Which ten product categories generate the most revenue, and how does their average review score compare to the overall average? 3. What share of orders are delivered later than the estimated delivery date, and how does lateness relate to review score? Quantify it, and say explicitly whether the relationship is causal. 4. What proportion of customers place more than one order? (Read the data dictionary: customer_id is not the customer.) 5. Which states have the longest median delivery time, and is that driven by distance from sellers? 6. One additional question of your own that a marketplace operations manager would care about. Rules: no tutorial for this dataset. Use window functions for at least one question and CTEs for at least two. Put every query in a .sql file. Build the dashboard in the BI tool you learned. Commit at least eight times with meaningful messages. Common traps: payments has multiple rows per order (instalments and vouchers), so summing it after joining to order items double-counts; customer_unique_id versus customer_id; orders with status other than 'delivered'; and Portuguese category names that need the translation table.
How to use this resource
The dataset and its data dictionary. Read the schema diagram and column descriptions before writing a query; half the traps in this spec are documented there.