Analyze E-Commerce Sales with SQL

Load a 100k-order dataset into PostgreSQL and write CTE and window function queries.

Introduction

30 Second Summary

Every online purchase you make generates data. The companies behind those stores use that data to answer critical questions like "which products make us the most money?" and "where do deliveries get stuck?"

In this project, you will load a real Brazilian e-commerce dataset into a local PostgreSQL database and write SQL queries that answer the kind of business questions a manager would actually ask.

What You'll Build

You will run polished SQL queries against 100,000 real orders and see revenue rankings, seller growth trends, and delivery pipeline bottlenecks appear in your results.

By the end of this project, you'll have:

A local PostgreSQL database where you can query 100k+ real e-commerce orders across 9 interconnected tables spanning 2016 to 2018.
SQL queries that answer real business questions using CTEs, window functions, and multi-table JOINs to surface top revenue categories, fastest-growing sellers, and delivery bottlenecks.
A reusable VS Code + PostgreSQL setup you can open anytime to write and run SQL against local data.
Secret Mission: Add a delivery performance analysis that identifies which states have the worst late-delivery rates and calculates the average delay in days.

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, WHERE, simple JOINs). No PostgreSQL experience is required.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Set Up PostgreSQL and Your SQL Environment

To analyze e-commerce data with SQL, you need two things: a database to hold the data and a tool to write and run queries against it.

In this step, you will install PostgreSQL 17 on your Mac and connect it to VS Code so you can write SQL without switching between apps.

In this step, get ready to:

Install PostgreSQL 17 on your Mac.
Create the project database.
Connect VS Code to PostgreSQL.

Install PostgreSQL 17 via Homebrew

PostgreSQL is an open-source relational database. You will use Homebrew, the macOS package manager, to install it.

Open your terminal by pressing Cmd+Space to open Spotlight, typing Terminal, and pressing Enter.
Install PostgreSQL 17 by running this command:

brew install postgresql@17

What does this command do?

Homebrew downloads and installs PostgreSQL version 17 onto your Mac. The @17 suffix tells Homebrew you want the specific major version 17 formula, not the latest default.

This formula is "keg-only", meaning Homebrew installs it but does not automatically put it on your system PATH. You will fix that in the next command.

Seeing 'brew: command not found'?

This means Homebrew is not installed on your Mac. Install it by running this command in your terminal:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

The script will explain what it does and ask for your confirmation before proceeding. After it finishes, close your terminal and open a new one, then retry brew install postgresql@17.

Help me install Homebrew on my Mac

Add PostgreSQL to your PATH so your terminal can find it by running these commands:

echo 'export PATH="/opt/homebrew/opt/postgresql@17/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

Why do I need to update my PATH?

Since PostgreSQL 17 is keg-only, Homebrew installs it in a private location (/opt/homebrew/opt/postgresql@17/bin). The first line adds that location to your shell configuration so your terminal knows where to find psql and other PostgreSQL tools.

The second line reloads your shell configuration so the change takes effect immediately.

Start the PostgreSQL service by running:

brew services start postgresql@17

You should see a message confirming the service started successfully.

Before you run the next command, what output do you expect to see if PostgreSQL installed correctly?

Verify the installation by running:

psql --version

✔️ I see psql (PostgreSQL) 17.10

PostgreSQL 17 is installed and on your PATH. You are ready to create your database.

ⓧ I see an older version

You have a previous PostgreSQL installation that is taking priority on your PATH. The fix is to ensure the PostgreSQL 17 bin directory comes first.

Open your ~/.zshrc file and make sure the line export PATH="/opt/homebrew/opt/postgresql@17/bin:$PATH" appears at the very end of the file (after any other PATH lines).
Close your terminal completely and open a new one.
Run psql --version again. You should now see 17.10.

Help me fix my PostgreSQL PATH issue

ⓧ Command not found

Your terminal cannot find the psql binary. This usually means the PATH update did not take effect.

Confirm you are using zsh by running echo $SHELL. You should see /bin/zsh.
If you see /bin/bash instead, run the echo command targeting ~/.bash_profile instead of ~/.zshrc.
Close your terminal completely and open a fresh one.
Run psql --version again.

Help me troubleshoot psql command not found

Create the project database

PostgreSQL is running, but it does not yet have a database for your project. You need a dedicated database to hold the e-commerce tables you will load in the next step.

Create a new empty database by running:

createdb olist_ecommerce

Connect to the database to confirm it works by running:

psql olist_ecommerce

You should see a prompt that reads olist_ecommerce=#. This confirms the database exists and you are connected.

Type \q and press Enter to exit the PostgreSQL prompt.

Seeing 'createdb: error: connection refused'?

This means the PostgreSQL service is not running. Start it with:

brew services start postgresql@17

Then retry the createdb olist_ecommerce command.

Help me fix the connection refused error

Install the PostgreSQL VS Code extension

You could run all your queries in the terminal, but VS Code gives you syntax highlighting, code completion, and the ability to save your queries in .sql files. The PostgreSQL extension adds a database explorer and inline query execution.

Switch to VS Code.
Open the Extensions panel by pressing Cmd+Shift+X.
Search for PostgreSQL and find the extension by Chris Kolkman (extension ID: ckolkman.vscode-postgres).
Click Install.

Now connect the extension to your database.

Open the Command Palette by pressing Cmd+Shift+P.
Type PostgreSQL: Add Connection and select it.
Enter localhost for the hostname.
Enter olist_ecommerce for the database name.
Enter 5432 for the port.
Enter your macOS username for the user field (this is the name you see in your terminal prompt).
Leave the password field empty and press Enter.
Choose a display name when prompted (e.g. olist_ecommerce).

Look at the left sidebar in VS Code. You should see a PostgreSQL explorer icon (looks like a database cylinder). Click it and confirm your olist_ecommerce connection appears in the panel.

Connection not showing in the explorer?

Make sure you completed all the prompts in the Add Connection wizard. If you accidentally dismissed it, open the Command Palette again (Cmd+Shift+P) and re-run PostgreSQL: Add Connection.

If the connection shows a red icon or an error, confirm that the PostgreSQL service is still running by switching to your terminal and running brew services list. You should see postgresql@17 with a status of started.

Help me connect VS Code to my PostgreSQL database

Your SQL environment is ready. You have a running database and a VS Code workspace that can talk to it. Next up, you will load real e-commerce data into this empty database.

Load the E-Commerce Dataset

Your database is created and ready, but it is empty. To practice real-world SQL analysis, you need multi-table data with messy timestamps, foreign key relationships, and enough volume to make aggregations meaningful.

In this step, you will download a real Brazilian e-commerce dataset from Kaggle, create matching tables in PostgreSQL, and load over 100,000 rows of order data from CSV files. Loading CSVs into a relational database is a fundamental data analyst skill you will use in nearly every role.

In this step, get ready to:

Download the Olist e-commerce dataset from Kaggle.
Create 7 database tables matching the CSV structure.
Load data from CSV files into PostgreSQL and verify it loaded correctly.

Download the Olist dataset

The Olist dataset contains 100,000 real orders from a Brazilian e-commerce marketplace, spread across 9 related CSV files. It is free, publicly available, and a standard recommendation for data analyst portfolios.

Open your browser and navigate to the Olist dataset page:
https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce
Sign in or create a free Kaggle account if prompted. Kaggle requires an account to download datasets.
Click the Download button near the top of the page to download the zip archive (about 126 MB).

What is the Olist dataset?

This is real (not synthetic) data from a Brazilian e-commerce marketplace called Olist. It covers orders from 2016 to 2018, including customer info, order details, payments, product data, and seller information. The multiple related tables make it ideal for practicing JOINs.

Once the download completes, unzip the archive into a folder called olist-data in your home directory by running these commands:

mkdir ~/olist-data
unzip ~/Downloads/archive.zip -d ~/olist-data

Confirm the CSV files are there by running:

ls ~/olist-data

You should see 9 CSV files listed, including olist_orders_dataset.csv, olist_customers_dataset.csv, and olist_products_dataset.csv.

Don't see 9 CSV files?

If the zip file has a different name, check your Downloads folder for the actual filename and adjust the unzip command accordingly.
Some browsers may auto-unzip the archive. Look for a folder in Downloads containing the CSV files and copy them into ~/olist-data/ instead.
help me find and unzip my Kaggle download

Create the database tables and load the data

Now you will write a SQL setup script that creates tables matching the CSV column structure, then loads the data using \copy commands. The \copy command runs client-side, so it can read any file on your machine without permission issues.

In VS Code, create a new file called setup.sql by pressing Cmd+N and then Cmd+S to save it with the name setup.sql on your Desktop.
Add the following code to create the customers and orders tables:

-- Create tables for the Olist e-commerce dataset

CREATE TABLE customers (
    customer_id VARCHAR(32) PRIMARY KEY,
    customer_unique_id VARCHAR(32),
    customer_zip_code_prefix VARCHAR(5),
    customer_city VARCHAR(100),
    customer_state VARCHAR(2)
);

CREATE TABLE orders (
    order_id VARCHAR(32) PRIMARY KEY,
    customer_id VARCHAR(32) REFERENCES customers(customer_id),
    order_status VARCHAR(20),
    order_purchase_timestamp TIMESTAMP,
    order_approved_at TIMESTAMP,
    order_delivered_carrier_date TIMESTAMP,
    order_delivered_customer_date TIMESTAMP,
    order_estimated_delivery_date TIMESTAMP
);

What does this code do?

CREATE TABLE defines the column names and data types for each table.
VARCHAR(32) stores text strings up to 32 characters (used for IDs).
REFERENCES customers(customer_id) creates a foreign key linking orders to customers. This enforces that every order belongs to a valid customer.
TIMESTAMP stores date and time values for the order lifecycle stages.

Below the orders table, add the products and sellers tables:

CREATE TABLE products (
    product_id VARCHAR(32) PRIMARY KEY,
    product_category_name VARCHAR(100),
    product_name_lenght INTEGER,
    product_description_lenght INTEGER,
    product_photos_qty INTEGER,
    product_weight_g INTEGER,
    product_length_cm INTEGER,
    product_height_cm INTEGER,
    product_width_cm INTEGER
);

CREATE TABLE sellers (
    seller_id VARCHAR(32) PRIMARY KEY,
    seller_zip_code_prefix VARCHAR(5),
    seller_city VARCHAR(100),
    seller_state VARCHAR(2)
);

What does this code do?

The products table stores product dimensions, weight, and category. Notice product_name_lenght is intentionally misspelled. This matches the original CSV headers from the dataset.
The sellers table stores each seller's location info. You will JOIN this with order_items later to analyze seller performance.

Save setup.sql (Cmd+S).
Next, add the remaining three tables below the sellers table:

CREATE TABLE order_items (
    order_id VARCHAR(32) REFERENCES orders(order_id),
    order_item_id INTEGER,
    product_id VARCHAR(32) REFERENCES products(product_id),
    seller_id VARCHAR(32) REFERENCES sellers(seller_id),
    shipping_limit_date TIMESTAMP,
    price NUMERIC(10,2),
    freight_value NUMERIC(10,2),
    PRIMARY KEY (order_id, order_item_id)
);

CREATE TABLE order_payments (
    order_id VARCHAR(32) REFERENCES orders(order_id),
    payment_sequential INTEGER,
    payment_type VARCHAR(20),
    payment_installments INTEGER,
    payment_value NUMERIC(10,2)
);

CREATE TABLE product_category_name_translation (
    product_category_name VARCHAR(100) PRIMARY KEY,
    product_category_name_english VARCHAR(100)
);

What does this code do?

order_items links orders to products and sellers. It has a composite primary key (order_id + order_item_id) because one order can contain multiple items.
NUMERIC(10,2) stores monetary values with 2 decimal places. This avoids the rounding issues that come with floating-point types.
product_category_name_translation maps Portuguese category names to English translations.

Finally, add the data loading commands at the bottom of setup.sql:

-- Load data from CSV files

\copy customers FROM '~/olist-data/olist_customers_dataset.csv' WITH (FORMAT csv, HEADER true);
\copy orders FROM '~/olist-data/olist_orders_dataset.csv' WITH (FORMAT csv, HEADER true);
\copy products FROM '~/olist-data/olist_products_dataset.csv' WITH (FORMAT csv, HEADER true);
\copy sellers FROM '~/olist-data/olist_sellers_dataset.csv' WITH (FORMAT csv, HEADER true);
\copy order_items FROM '~/olist-data/olist_order_items_dataset.csv' WITH (FORMAT csv, HEADER true);
\copy order_payments FROM '~/olist-data/olist_order_payments_dataset.csv' WITH (FORMAT csv, HEADER true);
\copy product_category_name_translation FROM '~/olist-data/product_category_name_translation.csv' WITH (FORMAT csv, HEADER true);

Why \copy instead of COPY?

The SQL COPY command requires the PostgreSQL server process to have read access to the file, which causes permission errors on macOS. The \copy meta-command runs client-side through psql and can read any file your user account has access to.

HEADER true tells PostgreSQL to skip the first row of each CSV because it contains column names, not data.

Save setup.sql (Cmd+S).
Run the setup script against your database by running this command in your terminal:

psql olist_ecommerce -f setup.sql

You should see output showing each table being created (CREATE TABLE) followed by COPY lines with row counts for each CSV file loaded.

Seeing permission or file-not-found errors?

If you see No such file or directory, double-check that your CSV files are in ~/olist-data/ and that the filenames match exactly. Run ls ~/olist-data/ to verify.
If you see relation already exists, the tables were partially created from a previous run. Connect with psql olist_ecommerce, run DROP SCHEMA public CASCADE; CREATE SCHEMA public;, type \q to exit, then re-run the setup command.
help me fix errors when running my setup.sql file

✔️ Awesome, I've got everything!

Great. Make sure you have saved setup.sql and that the terminal output shows all 7 tables created and data loaded.

ⓧ I'd like to double check the full code

Your setup.sql should contain these sections in order. Scroll up and confirm each is present:

A comment line: -- Create tables for the Olist e-commerce dataset
CREATE TABLE customers (5 columns).
CREATE TABLE orders (8 columns, references customers).
CREATE TABLE products (9 columns).
CREATE TABLE sellers (4 columns).
CREATE TABLE order_items (7 columns + composite PK, references orders/products/sellers).
CREATE TABLE order_payments (5 columns, references orders).
CREATE TABLE product_category_name_translation (2 columns).
A comment line: -- Load data from CSV files
Seven \copy commands loading from ~/olist-data/ with FORMAT csv, HEADER true.

Verify the data loaded correctly

Before you move on, you need to confirm the data actually made it into your tables. A quick row count across key tables tells you whether the load succeeded.

Switch back to VS Code. Right-click your olist_ecommerce connection in the PostgreSQL explorer and select New Query.
Before you run the next query, predict: how many rows do you think are in the orders table, given this is a dataset of 100k orders?
Run this query to check row counts across your key tables:

SELECT 'orders' AS table_name, COUNT(*) FROM orders
UNION ALL
SELECT 'order_items', COUNT(*) FROM order_items
UNION ALL
SELECT 'customers', COUNT(*) FROM customers;

You should see 99441 for orders, 112650 for order_items (some orders have multiple items), and 99441 for customers.

Why more order_items than orders?

A single order can contain multiple products. Each product in an order gets its own row in the order_items table. This is a one-to-many relationship that you will exploit with JOINs in the next steps.

Seeing 0 rows or an error?

If the query returns 0 for all tables, the \copy commands did not run. Re-run psql olist_ecommerce -f setup.sql in your terminal and check the output for errors.
If you see relation "orders" does not exist, the VS Code extension may be connected to a different database. Check that your connection in the PostgreSQL explorer points to olist_ecommerce.
help me verify my Olist data loaded correctly

You now have a real e-commerce database with nearly 100,000 orders spread across 7 interconnected tables. Next up, you will explore this data with basic queries to understand its shape before writing advanced analysis.

Explore the Data with Basic Queries

Your database is loaded with 99,000+ real orders. You have seven tables sitting ready to query.

Before writing complex analysis queries, you need to understand what the data actually looks like. What values exist in each column? How do the tables connect to each other? Jumping straight to advanced queries without exploring first leads to wrong answers.

In this step, get ready to:

Run exploratory queries on the orders table to see sample rows, statuses, and date ranges.
Write a multi-table JOIN to understand how orders, items, and products connect.
Discover a data quality issue that would silently corrupt your revenue analysis.

Explore the orders table

The orders table is the heart of this dataset. Every analysis you write will start here or join to it. You need to see what a row looks like before you can write meaningful queries.

In VS Code, right-click your olist_ecommerce connection in the PostgreSQL explorer sidebar.
Select New Query to open a fresh query editor.
See your first 10 orders by pasting and running this query (press F5 to execute):

SELECT * FROM orders LIMIT 10;

You should see 10 rows with columns including order_id, order_status, and several timestamp columns.

Find out what order statuses exist by running this query:

SELECT DISTINCT order_status FROM orders;

You should see a list of statuses including delivered, shipped, canceled, and others. Keep these in mind for later.

Check the time range of the dataset by running:

SELECT MIN(order_purchase_timestamp), MAX(order_purchase_timestamp) FROM orders;

You should see dates ranging from 2016 to 2018. This confirms you are working with about two years of real e-commerce data.

Query not executing?

Make sure your cursor is inside the query text before pressing F5. The extension runs whichever query your cursor is positioned in.

If you see a connection error, right-click the connection in the PostgreSQL explorer and select Connect to re-establish it.

Why isn't my PostgreSQL query running in VS Code?

Understand the relationships between tables

A single table tells you very little on its own. The real insights come from connecting tables together. The orders table has order-level info (status, timestamps), but the prices live in order_items and the category names live in products.

Write a three-table JOIN that pulls order details, prices, and product categories together by running this query:

-- Connect orders to their items and product categories
SELECT
    o.order_id,
    o.order_status,
    oi.price,
    oi.freight_value,
    p.product_category_name
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
LIMIT 20;

What does this query do?

orders o is the starting table. The o is an alias so you can type o.order_id instead of orders.order_id.
The first JOIN connects each order to its line items using order_id as the shared key.
The second JOIN connects each item to its product details using product_id.
LIMIT 20 keeps the result small so you can read it. Remove the limit when you need the full dataset.

You should see 20 rows with order_id, order_status, price, freight_value, and product_category_name all appearing together. This three-table JOIN pattern is the foundation for every analysis query you will write next.

Seeing zero rows or an error?

If you see zero rows, check that your data loaded in Step 2. Run SELECT COUNT(*) FROM order_items; to confirm. If it returns 0, re-run your setup.sql file.

If you see a column name error, double-check that you typed the aliases (o, oi, p) correctly in both the SELECT and JOIN lines.

Help me fix my multi-table JOIN query

Check for data quality issues

You saw several different statuses when you ran the DISTINCT query earlier. Now the question is: how many orders are in each status? This matters because if you calculate total revenue across all orders, you might be counting canceled orders that never generated real money.

Before you run the next query, predict: what percentage of orders do you think are in delivered status versus other statuses?
Check the distribution of order statuses by running:

-- See how orders are distributed across statuses
SELECT order_status, COUNT(*)
FROM orders
GROUP BY order_status
ORDER BY COUNT(*) DESC;

You should see that the vast majority of orders are delivered. But there are also orders marked canceled, unavailable, and other non-completed statuses mixed in.

Why does this matter?

If you calculated total revenue from all orders without filtering, you would include canceled orders that never actually generated revenue. Your numbers would be wrong, and you might not notice.

This is exactly the kind of mistake that happens when analysts skip exploration and jump straight to complex queries. In the next step, you will filter to WHERE order_status = 'delivered' to make sure your revenue numbers are accurate.

You now understand the shape of your data, how the tables connect, and the critical filter you need to apply. Next up, you will write production-quality queries using CTEs and window functions to answer real business questions.

Answer Business Questions with CTEs and Window Functions

You now understand how the tables connect and what the data looks like. The real value of SQL shows up when you use it to answer questions a manager would actually ask.

Basic JOINs and GROUP BYs are table stakes. CTEs and window functions are what separate "I know SQL" from "I can do the job." In this step, you will write three queries that answer real business questions using these techniques.

In this step, get ready to:

Write a CTE to find the top revenue-generating product categories.
Use a window function to calculate month-over-month seller growth.
Identify where orders get stuck in the delivery pipeline.

Write a CTE to find top revenue-generating product categories

A CTE (Common Table Expression) lets you name a subquery and reference it like a table. This makes complex queries readable by separating the "calculate" step from the "filter and rank" step.

In VS Code, create a new file called analysis.sql in the same folder as your setup.sql.
Start the file with a header comment by pasting the following:

-- ============================================
-- E-Commerce Sales Analysis
-- Dataset: Olist Brazilian E-Commerce (2016-2018)
-- ============================================

Below the header, add your first query. This CTE calculates total revenue per product category, then selects the top 10:

-- ============================================
-- Query 1: Top Revenue-Generating Product Categories
-- Uses: CTE, multi-table JOIN, aggregation
-- ============================================

WITH category_revenue AS (
    SELECT
        COALESCE(t.product_category_name_english, p.product_category_name) AS category,
        SUM(oi.price) AS total_revenue,
        COUNT(DISTINCT o.order_id) AS total_orders
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    LEFT JOIN product_category_name_translation t
        ON p.product_category_name = t.product_category_name
    WHERE o.order_status = 'delivered'
    GROUP BY COALESCE(t.product_category_name_english, p.product_category_name)
)
SELECT
    category,
    total_revenue,
    total_orders,
    ROUND(total_revenue / total_orders, 2) AS avg_order_value
FROM category_revenue
ORDER BY total_revenue DESC
LIMIT 10;

What does this query do?

The WITH category_revenue AS (...) block is the CTE. It joins four tables (orders, order_items, products, and the translation table) to calculate revenue per category. The WHERE o.order_status = 'delivered' filter ensures you only count revenue from orders that actually reached the customer.

The outer SELECT then references category_revenue like a regular table, ranks by revenue, and grabs the top 10. COALESCE picks the English translation if one exists, otherwise falls back to the Portuguese name.

Save analysis.sql.
To run just this query, select the entire Query 1 text (from WITH down to the ;), then press F5.

Running selected queries

In Postgres language files, F5 runs whatever is selected. If nothing is selected, it runs the entire file. Since your file will eventually have multiple queries, always select the specific query you want to run before pressing F5.

You should see a results table with 10 rows showing category names, revenue totals, order counts, and average order values.

No results or seeing an error?

Make sure your file language is set to PostgreSQL. Click the language indicator in the bottom-right of VS Code and select PostgreSQL.
Confirm your connection is active by checking the PostgreSQL explorer in the sidebar shows your olist_ecommerce database.
Check that you selected the entire query from WITH through the final ; before pressing F5.

Still stuck? Help me debug my Query 1 CTE results

Write a window function query to calculate month-over-month seller growth

Window functions let you compare each row to other rows in its group without collapsing the result set. The LAG() function looks at the previous row's value, which is exactly what you need to calculate growth over time.

In analysis.sql, below Query 1, add the first part of your growth query. This CTE groups each seller's revenue by month:

-- ============================================
-- Query 2: Month-over-Month Seller Revenue Growth
-- Uses: Window function (LAG), DATE_TRUNC, CTE
-- ============================================

WITH monthly_seller_revenue AS (
    SELECT
        oi.seller_id,
        DATE_TRUNC('month', o.order_purchase_timestamp) AS month,
        SUM(oi.price) AS monthly_revenue
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY oi.seller_id, DATE_TRUNC('month', o.order_purchase_timestamp)
),

What does this CTE do?

The monthly_seller_revenue CTE uses DATE_TRUNC('month', ...) to collapse timestamps into month buckets. It then sums each seller's revenue per month. This gives you the raw data that the window function will compare against.

Notice the trailing comma after the closing parenthesis. That signals another CTE follows in the same WITH block.

Directly below (no blank line between them), add the second CTE and the final SELECT that applies the window function:

seller_growth AS (
    SELECT
        seller_id,
        month,
        monthly_revenue,
        LAG(monthly_revenue) OVER (
            PARTITION BY seller_id ORDER BY month
        ) AS prev_month_revenue
    FROM monthly_seller_revenue
)
SELECT
    seller_id,
    month,
    monthly_revenue,
    prev_month_revenue,
    ROUND(
        (monthly_revenue - prev_month_revenue) / prev_month_revenue * 100, 1
    ) AS growth_pct
FROM seller_growth
WHERE prev_month_revenue IS NOT NULL
    AND prev_month_revenue > 0
ORDER BY growth_pct DESC
LIMIT 20;

How does LAG() work here?

LAG(monthly_revenue) OVER (PARTITION BY seller_id ORDER BY month) looks at each seller's rows ordered by month and grabs the previous month's revenue. The PARTITION BY ensures each seller's history is treated independently.

The final SELECT calculates the percentage change: (current - previous) / previous * 100. The WHERE clause filters out rows where there is no previous month (first month for each seller) and avoids division by zero.

Save analysis.sql.
Before you run this, predict: will the growth_pct values be mostly positive, mostly negative, or a mix? Think about what "top 20 by growth" means.
Select the entire Query 2 (from WITH monthly_seller_revenue down to the final ;) and press F5.

You should see a result set with columns: seller_id, month, monthly_revenue, prev_month_revenue, and growth_pct. Since you sorted by growth_pct DESC, the top rows show sellers with the highest percentage growth in a given month.

Query returns no rows?

Make sure you selected both CTEs AND the final SELECT as one continuous block. The query starts at WITH and ends at the ; after LIMIT 20.
Check that the trailing comma after the first CTE's closing ) is present. Without it, PostgreSQL treats them as separate (invalid) statements.

Help me fix my window function query

Identify where orders get stuck in the pipeline

Managers want to know where the bottleneck is. Is it the time between purchase and approval? Approval to carrier pickup? Or carrier to doorstep? This query answers that by measuring average days at each stage.

In analysis.sql, below Query 2, add the pipeline bottleneck analysis:

-- ============================================
-- Query 3: Order Pipeline Bottleneck Analysis
-- Uses: Date arithmetic, aggregation, EXTRACT
-- ============================================

SELECT
    ROUND(AVG(
        EXTRACT(EPOCH FROM (order_approved_at - order_purchase_timestamp)) / 86400
    ), 2) AS avg_days_to_approval,
    ROUND(AVG(
        EXTRACT(EPOCH FROM (order_delivered_carrier_date - order_approved_at)) / 86400
    ), 2) AS avg_days_to_carrier,
    ROUND(AVG(
        EXTRACT(EPOCH FROM (order_delivered_customer_date - order_delivered_carrier_date)) / 86400
    ), 2) AS avg_days_carrier_to_customer,
    ROUND(AVG(
        EXTRACT(EPOCH FROM (order_delivered_customer_date - order_purchase_timestamp)) / 86400
    ), 2) AS avg_total_days
FROM orders
WHERE order_status = 'delivered'
    AND order_approved_at IS NOT NULL
    AND order_delivered_carrier_date IS NOT NULL
    AND order_delivered_customer_date IS NOT NULL;

What does EXTRACT(EPOCH FROM ...) do?

Subtracting two timestamps in PostgreSQL gives an interval. EXTRACT(EPOCH FROM ...) converts that interval into total seconds. Dividing by 86400 (seconds in a day) gives you the result in days.

The IS NOT NULL filters are important. Some orders never reached certain stages (e.g., canceled before carrier pickup). Including those would skew your averages with NULL arithmetic.

Save analysis.sql.
Before you run this, predict: which stage do you think takes the longest on average? Purchase to approval, approval to carrier, or carrier to customer?
Select Query 3 and press F5.

You should see a single row with four columns. The avg_days_carrier_to_customer value is likely the largest number, revealing that the carrier-to-doorstep leg is where orders spend the most time.

Seeing NULL in your results?

Double-check that all four IS NOT NULL conditions are in your WHERE clause. Missing one means NULL timestamps sneak in and produce NULL averages.
Make sure you included AND between each condition (not commas).

Help me debug my pipeline bottleneck query

✔️ Awesome, I've got everything!

Great. Double check you have saved analysis.sql with all three queries.

ⓧ I'd like to double check the full code

Your analysis.sql should contain, in order:

The file header comment (4 lines starting with -- E-Commerce Sales Analysis).
Query 1: the category_revenue CTE with a four-table JOIN, filtered to delivered orders, followed by the outer SELECT with ORDER BY total_revenue DESC LIMIT 10.
Query 2: two CTEs (monthly_seller_revenue and seller_growth) using LAG(), followed by the outer SELECT calculating growth_pct with ORDER BY growth_pct DESC LIMIT 20.
Query 3: a single SELECT using EXTRACT(EPOCH FROM ...) / 86400 to calculate four average-days columns, filtered to delivered orders with non-null timestamps.

Scroll up and confirm each query section is present in your file. Run each one individually (select it, then press F5) to verify it returns results.

You now have three polished analysis queries that demonstrate CTEs, window functions, and date arithmetic. These are exactly the techniques that show up in data analyst technical interviews. Next up, you will tackle a Secret Mission that goes even deeper into the data.

Secret mission

Delivery Performance by State

Your manager asks: which states have the worst late-delivery rates, and how many days late are they on average? Write a single query that combines geographic segmentation, CASE expressions, and date math to answer both questions at once.

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing costs.

Resources you used:

PostgreSQL 17 service running locally.
The olist_ecommerce database stored on your local disk.
Project SQL files (setup.sql, analysis.sql, delivery_analysis.sql).
Downloaded dataset folder (~/olist-data/).
PostgreSQL VS Code extension (ckolkman.vscode-postgres).

Keep everything running

No action needed. Choose this if you are still actively building or plan to use PostgreSQL for future data projects.

PostgreSQL uses minimal memory and CPU when idle.
Your olist_ecommerce database stays available for further analysis.
Your SQL files remain ready to run or extend.

Pause - I'll come back to this later

Stop the PostgreSQL service to free up memory. Your database and files stay on disk, ready to resume anytime.

Stop the PostgreSQL service by running:

brew services stop postgresql@17

When you are ready to resume, restart it by running:

brew services start postgresql@17

Your database, SQL files, and CSV data remain on disk untouched.

Delete - I don't want to use this again

Remove all project resources and start fresh. This deletes the database, your SQL files, the downloaded dataset, and PostgreSQL itself.

Drop the project database by running:

dropdb olist_ecommerce

Remove the downloaded dataset folder by running:

rm -rf ~/olist-data

Remove your SQL project files by running:

rm setup.sql analysis.sql delivery_analysis.sql

Stop and uninstall PostgreSQL entirely by running:

brew services stop postgresql@17
brew uninstall postgresql@17

Uninstall the PostgreSQL VS Code extension by opening the Extensions panel (Cmd+Shift+X), finding PostgreSQL by Chris Kolkman, and clicking Uninstall.

Nice Work!

Nice work! You just analyzed 100,000+ real e-commerce orders using intermediate SQL techniques that show up in the majority of data analyst job postings.

You've learned how to:

Load real-world CSV data into a local PostgreSQL database and verify it with exploratory queries.
Write CTEs and window functions (LAG) to answer real business questions like month-over-month seller growth and top revenue categories.
Identify delivery pipeline bottlenecks using date arithmetic and multi-table JOINs across 7 interconnected tables.
Secret Mission: Performed a geographic delivery performance analysis combining CASE expressions, date math, and multi-metric aggregation to pinpoint which states need logistics improvement.

Ready to quiz yourself?