Analyze E-Commerce Sales with SQL

Design a PostgreSQL database and analyze sales data with Python and pandas.

Introduction

30 Second Summary

Every online store generates thousands of transactions. Business teams need to answer questions like "What's our best-selling category?" and "Which customers spend the most?" without scrolling through endless spreadsheets.

In this project, you will build a local data analysis pipeline for a fictional online store called ByteShop. You'll design a database, fill it with sample sales data, then use Python to query that data and create charts that answer real business questions.

What You'll Build

You'll create a PostgreSQL database, connect it to Python using SQLAlchemy, and generate matplotlib visualizations from analytical SQL queries.

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

  • A running PostgreSQL database with a multi-table e-commerce schema storing customers, products, and orders.
  • A Python script that connects to your database, runs analytical SQL queries, and loads results into Pandas DataFrames.
  • Three matplotlib charts visualizing sales trends, top products, and customer insights derived from your data.
  • Secret Mission: Add a customer segmentation analysis that groups customers by spending tier and visualizes the distribution.

Are there any prerequisites?

Basic Python knowledge (variables, functions, loops) is helpful. No prior SQL or database experience is required.

Install PostgreSQL and Python Tools

Before you can query sales data, you need a database to store it and tools to pull it into Python.

In this step, you'll install PostgreSQL as your local database server, pgAdmin 4 as a visual interface for managing it, and set up Python with the packages needed for data analysis.

In this step, get ready to:
  • Install PostgreSQL 17 with pgAdmin 4.
  • Verify the database server is running.
  • Set up a Python virtual environment with analysis packages.
Download and install PostgreSQL 17

PostgreSQL is the database engine that stores and manages your data. The EDB interactive installer bundles the server, pgAdmin 4, and command-line tools in a single download.

  • Check whether PostgreSQL is already on your machine by opening a terminal and running this command:
psql --version

You should see output in the format psql (PostgreSQL) 17.x if PostgreSQL 17 is already installed.

✔️ I see version 17

PostgreSQL 17 is already installed. Skip ahead to the next substep to verify it's running in pgAdmin.

ⓧ I see an older version

You have an older version of PostgreSQL installed. You can install PostgreSQL 17 alongside it using the EDB installer. The installer will use a separate data directory for the new version.

  • Open your browser and go to the PostgreSQL Windows downloads page.
  • Click the link to download the EDB installer. This takes you to the EDB downloads page.
  • In the table, find the row for version 17 and click the Windows x86-64 download link.
  • Once downloaded, double-click the installer file. If Windows prompts you for administrator access, approve it.
  • On the Select Components screen, make sure all components are checked: PostgreSQL Server, pgAdmin 4, Stack Builder, and Command Line Tools.
  • On the Password screen, enter a password for the postgres superuser. Remember this password. You'll need it throughout the project.
  • On the Port screen, keep the default port 5432.
  • Click through the remaining screens accepting the defaults, then click Next on the Pre Installation Summary.
  • When the installer finishes, uncheck the Stack Builder checkbox and click Finish.

Why uncheck Stack Builder?

Stack Builder downloads additional drivers and tools. You don't need any of those for this project.

After installation, add PostgreSQL to your system PATH so the psql command works from any terminal.

  • Press the Windows key, type environment variables, and select Edit the system environment variables.
  • Click Environment Variables.
  • Under System variables, select Path and click Edit.
  • Click New and add C:\Program Files\PostgreSQL\17\bin.
  • Click OK on all dialogs to save.
  • Close and reopen any terminal windows, then run psql --version again to confirm it shows version 17.

ⓧ Command not found or not recognized

PostgreSQL is not installed yet. Follow the steps below to install it from scratch.

  • Open your browser and go to the PostgreSQL Windows downloads page.
  • Click the link to download the EDB installer. This takes you to the EDB downloads page.
  • In the table, find the row for version 17 and click the Windows x86-64 download link.
  • Once downloaded, double-click the installer file. If Windows prompts you for administrator access, approve it.
  • Click Next on the Welcome screen.
  • Accept the default installation directory and click Next.
  • On the Select Components screen, make sure all components are checked: PostgreSQL Server, pgAdmin 4, Stack Builder, and Command Line Tools.
  • Accept the default data directory and click Next.
  • On the Password screen, enter a password for the postgres superuser. Remember this password. You'll need it throughout the project.
  • On the Port screen, keep the default port 5432.
  • Accept the default locale and click Next.
  • Review the Pre Installation Summary, then click Next to begin the installation.
  • When the installer finishes, uncheck the Stack Builder checkbox and click Finish.

Why uncheck Stack Builder?

Stack Builder downloads additional drivers and tools. You don't need any of those for this project.

The EDB installer does not add PostgreSQL to your system PATH by default. Add it now so the psql command works from any terminal.

  • Press the Windows key, type environment variables, and select Edit the system environment variables.
  • Click Environment Variables.
  • Under System variables, select Path and click Edit.
  • Click New and add C:\Program Files\PostgreSQL\17\bin.
  • Click OK on all dialogs to save.
  • Close and reopen your terminal, then verify by running:
psql --version

You should see psql (PostgreSQL) 17.x in the output.

Still not seeing a version number?

Make sure you closed and reopened your terminal after editing the PATH. Existing terminal windows won't pick up the change.

Double-check the path you added matches your installation directory. The default is C:\Program Files\PostgreSQL\17\bin.

help me troubleshoot why psql is not recognized after installing PostgreSQL 17 on Windows

Verify PostgreSQL is running in pgAdmin

pgAdmin 4 is a graphical tool for managing your PostgreSQL databases. It was included in the EDB installer, so it's already on your machine.

  • Press the Windows key to open your search bar.
  • Type pgAdmin and press Enter to open it.

What happens when pgAdmin opens?

The first time you open pgAdmin, it may ask you to set a master password. This is separate from your PostgreSQL postgres user password. It protects pgAdmin's saved server credentials.

Choose any password you'll remember and click OK.

  • In the left panel, expand Servers.
  • Click on PostgreSQL 17. If prompted, enter the postgres superuser password you set during installation.
  • Expand Databases. You should see a database called postgres listed.

Seeing the postgres database confirms your server is running and pgAdmin is connected.

✔️ I see the postgres database

Your PostgreSQL server is running and pgAdmin is connected. You're ready to move on.

ⓧ I can't connect or don't see it

If pgAdmin shows a connection error or the server doesn't appear, try these steps:

  • Make sure the PostgreSQL service is running. Press the Windows key, type Services, and open the Services app.
  • Look for a service named postgresql-x64-17. If it says Stopped, right-click it and select Start.
  • Go back to pgAdmin and try expanding Servers again.

help me troubleshoot pgAdmin not connecting to my local PostgreSQL 17 server

Create a Python virtual environment and install packages

Your Python script will use four packages to connect to PostgreSQL and analyze data. A virtual environment keeps these packages isolated from your system Python.

  • Check your Python version by opening a terminal and running this command:
python --version

This project requires Python 3.11 or higher. You should see output like Python 3.11.x or Python 3.12.x.

✔️ I see version 3.11 or higher

Your Python version is ready. Continue below to create a virtual environment.

ⓧ I see an older version

You need Python 3.11 or higher for this project. Some packages require features only available in newer Python versions.

  • Go to the Python downloads page and download the latest Python 3 installer for Windows.
  • Run the installer. On the first screen, check the box that says Add python.exe to PATH, then click Install Now.
  • Close and reopen your terminal, then run python --version again to confirm the new version.

What about my old version?

The new installer typically updates your default python command on Windows. If python --version still shows the old version after reopening your terminal, try running python3 --version or use the full path to the new Python installation.

ⓧ Command not found or not recognized

Python is not installed on your machine. Follow these steps to install it.

  • Go to the Python downloads page and download the latest Python 3 installer for Windows.
  • Run the installer. On the first screen, check the box that says Add python.exe to PATH, then click Install Now.
  • Once installation finishes, close and reopen your terminal.
  • Verify by running:
python --version

You should see Python 3.11.x or higher.

Now create a project folder and set up a virtual environment.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir byteshop-analysis
cd byteshop-analysis
  • Create a virtual environment by running:
python -m venv venv
  • Activate the virtual environment by running:

Windows

venv\Scripts\activate

macOS

source venv/bin/activate

You should see (venv) appear at the beginning of your terminal prompt. This confirms the virtual environment is active.

  • Install the four required packages by running:
pip install pandas sqlalchemy psycopg2-binary matplotlib

What are these packages?

  • pandas loads query results into DataFrames for analysis and manipulation.
  • sqlalchemy creates the connection between Python and PostgreSQL.
  • psycopg2-binary is the PostgreSQL database driver that SQLAlchemy uses under the hood.
  • matplotlib generates charts and visualizations from your data.

The installation may take a minute. When it finishes, you should see a success message for each package.

  • Verify the packages installed correctly by running:
pip list

You should see pandas, SQLAlchemy, psycopg2-binary, and matplotlib listed in the output.

Seeing an error during pip install?

Make sure your virtual environment is active. You should see (venv) in your prompt. If not, run the activate command again.

If you see a permissions error, do not use sudo. Instead, confirm you're inside the virtual environment. The venv handles permissions automatically.

help me fix pip install errors in my Python virtual environment

Your database and Python environment are ready. Next up, you'll design the database schema and create your tables.

Design and Create the Database Schema

Your tools are installed and PostgreSQL is running. Now it's time to design the database that will power your ByteShop analytics.

A relational database organizes data into tables that reference each other through keys. In this step, you'll create four interconnected tables and fill them with sample sales data that you'll query later with Python.

In this step, get ready to:
  • Create the byteshop database and define four related tables.
  • Execute your schema and verify the tables appear in pgAdmin's browser.
  • Populate all tables with realistic e-commerce sample data.
Create the byteshop database and define your tables

Before writing any SQL, you need a dedicated database to hold your store's data. Then you'll define the structure of four tables using DDL (Data Definition Language) statements.

  • In pgAdmin 4, expand your server in the left panel.
  • Right-click on Databases and select Create then Database.
  • Type byteshop in the Database name field, then click Save.
  • Click on your new byteshop database in the left panel to select it.
  • Open the Query Tool by clicking Tools in the top menu bar, then selecting Query Tool.

What is a Query Tool?

The Query Tool is pgAdmin's built-in SQL editor. You type SQL statements in the top panel, click the execute button (or press F5), and see results in the bottom panel.

Your schema needs four tables that relate to each other. Here's the design:

  • customers stores who is buying (name, email, city, join date).
  • products stores what is for sale (name, category, price).
  • orders tracks each purchase event, linked to a customer via a foreign key.
  • order_items tracks individual products within an order, linked to both orders and products.

What are primary keys and foreign keys?

A primary key uniquely identifies each row in a table. A foreign key is a column that points to the primary key of another table, creating a relationship between them.

For example, orders.customer_id references customers.id. This means every order belongs to exactly one customer.

Now write the SQL to create all four tables. Try writing it yourself based on the design above, or check the reference below.

✔️ I'll try it first

Write your CREATE TABLE statements in the Query Tool. Each table needs an id SERIAL PRIMARY KEY column plus the data columns listed above. Use REFERENCES for foreign keys.

Create the tables in this order: customers and products first (they have no dependencies), then orders (depends on customers), then order_items (depends on both orders and products).

ⓧ Let me check my work

Here is the complete schema. Paste this into your Query Tool:

-- Customers table: stores buyer information
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150),
    city VARCHAR(100),
    join_date DATE
);

-- Products table: stores items for sale
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(150),
    category VARCHAR(50),
    price NUMERIC(10, 2)
);

-- Orders table: each purchase event, linked to a customer
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    order_date DATE,
    status VARCHAR(20)
);

-- Order items table: individual products in an order
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER
);
Execute the DDL and verify your tables
  • Click the Execute/Run button (the play icon) in the Query Tool toolbar, or press F5.
  • You should see a Messages tab at the bottom showing that the queries returned successfully with no errors.

Now verify the tables are visible in the schema browser.

  • In the left panel, expand byteshop then Schemas then public then Tables.
  • If you don't see the tables, right-click on Tables and select Refresh.
  • You should see all four tables listed: customers, order_items, orders, and products.

Seeing an error when you execute?

If you see a "relation already exists" error, the tables were already created from a previous attempt. Drop them first by running DROP TABLE IF EXISTS order_items, orders, products, customers CASCADE; then re-run your CREATE statements.

If you see a "permission denied" error, make sure you opened the Query Tool on the byteshop database (not the default postgres database).

Help me fix my CREATE TABLE errors in pgAdmin

Populate tables with sample data

An empty schema is only structure. You need realistic data to analyze later. You'll insert at least 10 customers, 15 products across multiple categories, 30 orders, and 50 order items.

  • Clear the Query Tool editor (select all text and delete it).
  • Write INSERT INTO statements for each table. Insert data in this order: customers first, then products, then orders, then order_items (foreign keys must reference existing rows).

Why does insertion order matter?

Foreign keys enforce relationships. If you try to insert an order for customer_id = 3 before customer 3 exists, PostgreSQL rejects the insert. Always populate parent tables (customers, products) before child tables (orders, order_items).

Try creating your own sample data, or use the reference dataset below. The reference data is designed for interesting analytics results in later steps.

✔️ I'll try it first

Create your own fictional customers, products, and orders. Make sure you have:

  • At least 10 customers in different cities.
  • At least 15 products spread across 3-4 categories.
  • At least 30 orders spread across multiple months in 2023.
  • At least 50 order items linking orders to products.
  • A mix of order statuses: completed, shipped, processing, cancelled.

Spread your orders across the year so the monthly revenue trend chart has interesting variation.

ⓧ Let me check my work

Here is a complete reference dataset. Paste this into your Query Tool:

-- Insert 10 customers across different cities
INSERT INTO customers (name, email, city, join_date) VALUES
('Alice Chen', 'alice.chen@email.com', 'Portland', '2023-01-15'),
('Bob Martinez', 'bob.martinez@email.com', 'Austin', '2023-02-20'),
('Carol White', 'carol.white@email.com', 'Seattle', '2023-03-10'),
('David Kim', 'david.kim@email.com', 'Denver', '2023-04-05'),
('Emma Johnson', 'emma.johnson@email.com', 'Chicago', '2023-05-22'),
('Frank Liu', 'frank.liu@email.com', 'New York', '2023-06-18'),
('Grace Patel', 'grace.patel@email.com', 'San Francisco', '2023-07-30'),
('Henry Adams', 'henry.adams@email.com', 'Boston', '2023-08-12'),
('Iris Nakamura', 'iris.nakamura@email.com', 'Los Angeles', '2023-09-25'),
('Jake Thompson', 'jake.thompson@email.com', 'Miami', '2023-10-08');

-- Insert 15 products across 4 categories
INSERT INTO products (name, category, price) VALUES
('Wireless Mouse', 'Electronics', 29.99),
('Mechanical Keyboard', 'Electronics', 89.99),
('USB-C Hub', 'Electronics', 45.99),
('Webcam HD', 'Electronics', 59.99),
('Laptop Stand', 'Accessories', 34.99),
('Mouse Pad XL', 'Accessories', 19.99),
('Cable Organizer', 'Accessories', 12.99),
('Phone Mount', 'Accessories', 22.99),
('Antivirus Pro', 'Software', 39.99),
('Cloud Storage Plan', 'Software', 9.99),
('VPN Annual', 'Software', 49.99),
('Photo Editor', 'Software', 29.99),
('Gaming Headset', 'Gaming', 79.99),
('Controller', 'Gaming', 54.99),
('RGB LED Strip', 'Gaming', 24.99);

-- Insert 32 orders spread across 2023
INSERT INTO orders (customer_id, order_date, status) VALUES
(1, '2023-01-20', 'completed'),
(1, '2023-03-15', 'completed'),
(1, '2023-05-10', 'completed'),
(1, '2023-07-22', 'completed'),
(1, '2023-10-05', 'completed'),
(2, '2023-02-28', 'completed'),
(2, '2023-04-18', 'completed'),
(2, '2023-07-10', 'shipped'),
(2, '2023-11-05', 'completed'),
(3, '2023-03-22', 'completed'),
(3, '2023-08-15', 'completed'),
(4, '2023-04-10', 'completed'),
(4, '2023-05-28', 'completed'),
(4, '2023-07-30', 'completed'),
(4, '2023-09-12', 'completed'),
(4, '2023-11-20', 'shipped'),
(5, '2023-05-30', 'completed'),
(5, '2023-07-05', 'completed'),
(5, '2023-09-18', 'processing'),
(5, '2023-11-28', 'completed'),
(6, '2023-06-25', 'completed'),
(6, '2023-10-10', 'cancelled'),
(7, '2023-08-01', 'completed'),
(7, '2023-09-05', 'completed'),
(7, '2023-10-22', 'completed'),
(7, '2023-12-01', 'shipped'),
(8, '2023-08-20', 'completed'),
(8, '2023-10-15', 'completed'),
(8, '2023-12-10', 'processing'),
(9, '2023-09-30', 'completed'),
(10, '2023-10-12', 'completed'),
(10, '2023-11-18', 'completed');

-- Insert 65 order items linking orders to products
INSERT INTO order_items (order_id, product_id, quantity) VALUES
(1, 2, 1),
(1, 6, 1),
(2, 4, 1),
(2, 3, 1),
(3, 13, 1),
(3, 14, 1),
(4, 11, 1),
(4, 12, 1),
(4, 5, 1),
(5, 2, 1),
(5, 15, 1),
(5, 1, 1),
(6, 1, 1),
(6, 6, 1),
(7, 3, 1),
(7, 7, 1),
(8, 13, 1),
(9, 9, 1),
(9, 10, 1),
(10, 7, 1),
(10, 10, 1),
(11, 8, 1),
(12, 2, 1),
(12, 4, 1),
(13, 13, 1),
(13, 14, 1),
(13, 15, 1),
(14, 5, 1),
(14, 3, 1),
(15, 11, 1),
(15, 2, 1),
(15, 1, 1),
(16, 13, 1),
(16, 6, 1),
(16, 8, 1),
(17, 5, 1),
(17, 1, 1),
(18, 9, 1),
(18, 11, 1),
(19, 14, 1),
(19, 15, 1),
(20, 2, 1),
(21, 6, 1),
(21, 7, 1),
(21, 10, 1),
(22, 1, 1),
(23, 2, 1),
(23, 4, 1),
(23, 3, 1),
(24, 13, 1),
(24, 11, 1),
(25, 14, 1),
(25, 5, 1),
(25, 12, 1),
(26, 1, 1),
(26, 9, 1),
(26, 6, 1),
(27, 3, 1),
(27, 8, 1),
(28, 9, 1),
(28, 12, 1),
(29, 4, 1),
(30, 15, 1),
(30, 10, 1),
(31, 5, 1),
(31, 7, 1),
(32, 13, 1),
(32, 6, 1);
  • Click Execute/Run (or press F5) to insert all the data.
  • Verify the data loaded correctly by running a quick count query in the Query Tool:
-- Check row counts for each table
SELECT 'customers' AS table_name, COUNT(*) FROM customers
UNION ALL
SELECT 'products', COUNT(*) FROM products
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'order_items', COUNT(*) FROM order_items;

What should I see?

If you used the reference dataset, you should see: customers = 10, products = 15, orders = 32, order_items = 68. If you wrote your own data, confirm you meet the minimums (10, 15, 30, 50).

Insert failed with a foreign key violation?

This means you referenced a customer_id, order_id, or product_id that doesn't exist yet. Make sure you insert customers and products before orders, and orders before order_items.

If you need to start over, run TRUNCATE customers, products, orders, order_items CASCADE; to clear all data and try again.

Help me fix my INSERT statement errors

Your database is loaded and ready for analysis. Next up, you'll connect Python to this PostgreSQL database and start pulling data into Pandas DataFrames.

Connect Python to PostgreSQL

Your byteshop database is populated with customers, products, orders, and order items. The data is sitting in PostgreSQL ready to be queried.

Now you need a bridge between Python and your database. SQLAlchemy is the Python library that creates this bridge. It handles connection pooling, dialect translation, and gives Pandas a direct line to your PostgreSQL tables.

In this step, get ready to:
  • Create a SQLAlchemy engine that connects to your database.
  • Test the connection with a simple query.
  • Load a full table into a Pandas DataFrame.
Create a SQLAlchemy engine

The engine is SQLAlchemy's core object. It holds your connection details (username, password, host, database name) in a URL string and manages a pool of connections to your database.

  • In your code editor, create a new file called analysis.py in your project folder (the same folder where your virtual environment lives).
  • Add the following imports and engine creation code to analysis.py:
from sqlalchemy import create_engine, text
import pandas as pd

# Create a connection engine to the byteshop database
engine = create_engine("postgresql+psycopg2://postgres:your-password-here@localhost:5432/byteshop")

What does this code do?

The create_engine() function takes a connection URL that tells SQLAlchemy everything it needs to reach your database.

  • postgresql+psycopg2 tells SQLAlchemy to use the PostgreSQL dialect with the psycopg2 driver.
  • postgres:your-password-here is the username and password. Replace your-password-here with the password you set during PostgreSQL installation.
  • localhost:5432/byteshop is the host, port, and database name.

The engine does not connect immediately. It waits until you actually run a query.

  • Replace your-password-here in the connection string with the password you chose for the postgres user during installation.
  • Save analysis.py.

Not sure what password you set?

This is the superuser password you entered during the PostgreSQL 17 installation wizard. If you cannot remember it, you may need to reset it through pgAdmin or reinstall PostgreSQL.

help me figure out my PostgreSQL password

Test the connection with SELECT 1

Before running complex queries, you should confirm that Python can actually reach your database. A SELECT 1 query is the simplest possible test. If it returns a result, your connection works.

  • Add the following connection test below your engine creation code in analysis.py:
# Test the connection with a simple query
with engine.connect() as conn:
    result = conn.execute(text("SELECT 1"))
    print(result.fetchone())

What does this code do?

engine.connect() opens a live connection to PostgreSQL. The with block ensures the connection is closed automatically when the code finishes.

text("SELECT 1") wraps a raw SQL string so SQLAlchemy can execute it safely. fetchone() retrieves the first row of results.

  • Save analysis.py.
  • Run your script by executing this command in your terminal (make sure your virtual environment is active):
python analysis.py
  • You should see (1,) printed in your terminal. This confirms Python successfully connected to PostgreSQL and received a response.

Seeing a connection error?

  • Check that PostgreSQL is still running. Open pgAdmin 4 and confirm you can see your server.
  • Double-check the password in your connection string matches what you set during installation.
  • Confirm the port is 5432 and the database name is byteshop (case-sensitive).

help me debug my PostgreSQL connection error in Python

Load the products table into a DataFrame

Now that the connection works, you can pull real data from your database into Python. The pd.read_sql() function sends a SQL query through your engine and returns the results as a DataFrame.

  • Add the following code at the bottom of analysis.py:
# Load the products table into a Pandas DataFrame
products_df = pd.read_sql("SELECT * FROM products", engine)
print(products_df.head())

What does this code do?

pd.read_sql() accepts a SQL query string and a SQLAlchemy engine. It runs the query against your database and packages the result rows into a DataFrame with proper column names.

.head() displays the first 5 rows of the DataFrame so you can quickly verify the data loaded correctly without printing everything.

  • Save analysis.py and run it again:
python analysis.py
  • You should see (1,) from the connection test, followed by a formatted table showing your first 5 products with columns for id, name, category, and price.

Seeing an empty DataFrame or an error?

  • If you see an empty table, confirm you inserted data into the products table in the previous step. Switch to pgAdmin and run SELECT * FROM products; to check.
  • If you see a "relation does not exist" error, make sure the table name products is lowercase and matches what you created in pgAdmin.

help me fix my pd.read_sql query

✔️ Awesome, I've got everything!

Great. Double check you have saved analysis.py and your script prints both the connection test and the products table.

ⓧ I'd like to double check the full code

Here is what your complete analysis.py should look like at this point:

from sqlalchemy import create_engine, text
import pandas as pd

# Create a connection engine to the byteshop database
engine = create_engine("postgresql+psycopg2://postgres:your-password-here@localhost:5432/byteshop")

# Test the connection with a simple query
with engine.connect() as conn:
    result = conn.execute(text("SELECT 1"))
    print(result.fetchone())

# Load the products table into a Pandas DataFrame
products_df = pd.read_sql("SELECT * FROM products", engine)
print(products_df.head())

Python is now talking to PostgreSQL. Next up, you will write analytical SQL queries that answer real business questions about ByteShop's sales data.

Write Analytical SQL Queries

Your database is loaded with sales data, but raw rows don't answer business questions. A store manager doesn't want to scroll through 50 order items.

They want to know which product category brings in the most money, how revenue is trending month over month, and who their best customers are. SQL's analytical power comes from combining tables with JOINs and condensing rows with GROUP BY and aggregate functions like SUM(). In this step, you'll write three queries that transform scattered rows into focused business insights, then pull each result into a Pandas DataFrame for further use in Python.

In this step, get ready to:
  • Calculate total revenue per product category.
  • Calculate monthly revenue trends over time.
  • Identify the top 5 customers by total spending.
Calculate revenue by product category

The first business question is straightforward: which product categories generate the most revenue? To answer this, you need to combine data from two tables.

The order_items table knows how many of each product sold, and the products table knows the price and category. A JOIN connects these tables, GROUP BY condenses rows by category, and SUM() adds up the revenue.

  • Add the following code to your analysis.py file, below the existing products_df section:
# Query 1: Total revenue per product category
category_query = """
    SELECT p.category, SUM(oi.quantity * p.price) AS total_revenue
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    GROUP BY p.category
    ORDER BY total_revenue DESC;
"""

# Load query results into a DataFrame
revenue_by_category_df = pd.read_sql(category_query, engine)
print(revenue_by_category_df)

What does this code do?

The JOIN connects each row in order_items to its matching product using oi.product_id = p.id. This gives you access to both the quantity sold and the product's price and category in a single combined row.

The expression SUM(oi.quantity * p.price) multiplies quantity by price for each line item, then adds them all up per category. GROUP BY p.category tells PostgreSQL to collapse all rows sharing the same category into a single summary row.

Finally, pd.read_sql() sends the query to PostgreSQL through your SQLAlchemy engine and returns the result as a DataFrame.

  • Save analysis.py.
  • Run the script in your terminal. You should see a table with two columns: category and total_revenue, with one row per product category sorted from highest to lowest revenue.

Seeing an error about the connection?

  • Make sure PostgreSQL is still running. Open pgAdmin and check that you can connect to your local server.
  • Confirm your password in the connection string matches the one you set during PostgreSQL installation.
  • Check that your virtual environment is activated before running the script.

Why isn't my SQL query returning results from PostgreSQL?

Calculate monthly revenue over time

The next question asks: how is revenue trending month over month? This query needs data from three tables because order dates live in orders, quantities live in order_items, and prices live in products.

PostgreSQL's DATE_TRUNC() function truncates a date to a specific precision. Passing 'month' as the first argument sets every date to the first day of its month, which groups all orders from the same month together.

  • Add this code below your category query in analysis.py:
# Query 2: Monthly revenue over time
monthly_query = """
    SELECT DATE_TRUNC('month', o.order_date) AS month,
           SUM(oi.quantity * p.price) AS total_revenue
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
    GROUP BY month
    ORDER BY month;
"""

# Load monthly revenue into a DataFrame
monthly_revenue_df = pd.read_sql(monthly_query, engine)
print(monthly_revenue_df)

What does this code do?

This query chains two JOINs. The first connects orders to order_items (linking an order to its line items). The second connects order_items to products (linking each line item to its product price).

The DATE_TRUNC('month', o.order_date) call converts each order date to the first of its month. For example, 2025-03-15 becomes 2025-03-01. This lets GROUP BY month combine all orders from the same calendar month into one revenue total.

  • Save analysis.py and run the script again.
  • You should see a table with month and total_revenue columns, with one row per month ordered chronologically.

Seeing a column not found error?

  • Make sure your orders table has a column called order_date. Open pgAdmin and check the column name matches exactly.
  • Verify the alias AS month in the SELECT matches what you reference in GROUP BY month. PostgreSQL allows referencing SELECT aliases in GROUP BY.

Why is my DATE_TRUNC query failing in PostgreSQL?

Find the top 5 customers by spending

The final question is: who are the store's best customers? This query touches all four tables because you need the customer's name from customers, the order link from orders, item quantities from order_items, and prices from products.

  • Add this code below your monthly query in analysis.py:
# Query 3: Top 5 customers by total spending
top_customers_query = """
    SELECT c.name, SUM(oi.quantity * p.price) AS total_spend
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
    GROUP BY c.name
    ORDER BY total_spend DESC
    LIMIT 5;
"""

# Load top customers into a DataFrame
top_customers_df = pd.read_sql(top_customers_query, engine)
print(top_customers_df)

What does this code do?

This query chains three JOINs to trace the path from a customer all the way to the product prices they purchased. Each JOIN adds one link in the chain: customer to order, order to order item, order item to product.

The GROUP BY c.name collapses all of a customer's purchases into one row. ORDER BY total_spend DESC sorts highest spenders first, and LIMIT 5 returns only the top five.

  • Save analysis.py and run the script.
  • You should see a table with name and total_spend columns showing exactly five rows, ranked from highest spender to lowest.

Getting fewer than 5 rows?

  • If your customers table has fewer than 5 customers with orders, you'll see fewer rows. Check that you inserted at least 10 customers and linked them to orders in Step 2.
  • If you see zero rows, confirm your orders table has a customer_id column that references real IDs in the customers table.

Why is my multi-table JOIN returning empty results?

✔️ Awesome, I've got everything!

Great. Double check you've saved analysis.py and that all three DataFrames print correctly when you run the script.

ⓧ I'd like to double check the full code

Here is the full analysis.py up to this point. Compare your file against this:

import pandas as pd
from sqlalchemy import create_engine, text

# Create SQLAlchemy engine to connect to byteshop database
engine = create_engine('postgresql+psycopg2://postgres:your-password-here@localhost:5432/byteshop')

# Test the connection
with engine.connect() as conn:
    result = conn.execute(text("SELECT 1"))
    print(result.fetchone())

# Load the products table into a DataFrame
products_df = pd.read_sql("SELECT * FROM products", engine)
print(products_df.head())

# Query 1: Total revenue per product category
category_query = """
    SELECT p.category, SUM(oi.quantity * p.price) AS total_revenue
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    GROUP BY p.category
    ORDER BY total_revenue DESC;
"""

# Load query results into a DataFrame
revenue_by_category_df = pd.read_sql(category_query, engine)
print(revenue_by_category_df)

# Query 2: Monthly revenue over time
monthly_query = """
    SELECT DATE_TRUNC('month', o.order_date) AS month,
           SUM(oi.quantity * p.price) AS total_revenue
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
    GROUP BY month
    ORDER BY month;
"""

# Load monthly revenue into a DataFrame
monthly_revenue_df = pd.read_sql(monthly_query, engine)
print(monthly_revenue_df)

# Query 3: Top 5 customers by total spending
top_customers_query = """
    SELECT c.name, SUM(oi.quantity * p.price) AS total_spend
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
    GROUP BY c.name
    ORDER BY total_spend DESC
    LIMIT 5;
"""

# Load top customers into a DataFrame
top_customers_df = pd.read_sql(top_customers_query, engine)
print(top_customers_df)

You now have three DataFrames full of business insights sitting in Python memory. Next up, you'll turn these numbers into publication-quality charts with matplotlib.

Visualize Your Findings with Matplotlib

You have three DataFrames loaded with real business insights from your byteshop database. Numbers in rows and columns are useful, but charts tell the story instantly.

In this step, you will turn your analytical query results into three matplotlib visualizations: a horizontal bar chart, a line chart, and a vertical bar chart. Each chart answers a specific business question and gets saved as a PNG file you can share.

In this step, get ready to:
  • Create a horizontal bar chart showing revenue by product category.
  • Create a line chart showing monthly revenue trends with formatted dates and grid lines.
  • Create a bar chart of top 5 customers by total spend and save all charts as PNG files.
Create a horizontal bar chart of revenue by category

Your revenue_by_category_df DataFrame holds the total revenue for each product category. A horizontal bar chart is ideal here because category names are text labels that read more easily on the y-axis.

  • In analysis.py, import matplotlib and create your first chart by adding this code at the bottom of your file:
import matplotlib.pyplot as plt

# --- Chart 1: Revenue by Product Category (horizontal bar chart) ---
fig1, ax1 = plt.subplots(figsize=(10, 6))

# Plot revenue by category as horizontal bars
revenue_by_category_df.plot(kind='barh', x=[[X_COL="your column name that holds the category labels"]], y=[[Y_COL="your column name that holds the revenue values"]], ax=ax1, legend=False)

# Add axis labels and a title
ax1.set_xlabel([[XLABEL_1="a label describing the x-axis, e.g. Revenue ($)"]])
ax1.set_ylabel([[YLABEL_1="a label describing the y-axis, e.g. Product Category"]])
ax1.set_title([[TITLE_1="a descriptive chart title, e.g. Total Revenue by Product Category"]])

# Save the figure as a PNG file
plt.tight_layout()
plt.savefig('revenue_by_category.png')
plt.show()

What does this code do?

The plt.subplots(figsize=(10, 6)) call creates a figure and axes object with a 10x6 inch canvas. You pass the axes to .plot() so the chart draws on that canvas.

The .plot(kind='barh') method is a Pandas shortcut that wraps matplotlib. The x parameter sets the category axis labels and y sets the bar lengths. Using plt.tight_layout() prevents labels from being clipped at the edges.

Finally, plt.savefig('revenue_by_category.png') writes the chart to a PNG file in your project folder. The plt.show() call displays it in a pop-up window so you can preview it immediately.

  • Save analysis.py.
  • Run your script. You should see a pop-up window with a horizontal bar chart showing each product category's total revenue as a bar.

Chart window not appearing?

If the script finishes but no chart window opens, your matplotlib backend may not support interactive display. Try adding plt.switch_backend('TkAgg') before your first plot call. Alternatively, skip plt.show() and check for the revenue_by_category.png file in your project folder instead.

If you get a KeyError, double-check that the column names you passed to x and y match exactly what your SQL query returns. Print revenue_by_category_df.columns to see the actual column names.

Help me debug my matplotlib chart

Create a line chart of monthly revenue trends

Your monthly_revenue_df DataFrame has revenue totals grouped by month. A line chart shows trends over time, making it easy to spot growth patterns or seasonal dips.

  • Add the following code below your first chart in analysis.py:
# --- Chart 2: Monthly Revenue Trends (line chart) ---
fig2, ax2 = plt.subplots(figsize=(12, 6))

# Plot monthly revenue as a line with data point markers
ax2.plot(monthly_revenue_df[[[DATE_COL="your column name for the month/date values"]]], monthly_revenue_df[[[REV_COL="your column name for the revenue values"]]], marker='o', linewidth=2)

# Format x-axis dates for readability
plt.xticks(rotation=45)

# Add grid lines, labels, and title
ax2.grid(True, alpha=0.3)
ax2.set_xlabel([[XLABEL_2="a label for the time axis, e.g. Month"]])
ax2.set_ylabel([[YLABEL_2="a label for the revenue axis, e.g. Revenue ($)"]])
ax2.set_title([[TITLE_2="a descriptive title, e.g. Monthly Revenue Trends"]])

# Save the figure as a PNG file
plt.tight_layout()
plt.savefig('monthly_revenue.png')
plt.show()

What does this code do?

Instead of using Pandas' built-in .plot() wrapper, this chart uses ax2.plot() directly from matplotlib. This gives you more control over formatting. The marker='o' argument adds dots at each data point so individual months are visible.

The plt.xticks(rotation=45) rotates date labels at 45 degrees so they don't overlap. The ax2.grid(True, alpha=0.3) adds subtle grid lines that help readers trace values without cluttering the chart.

  • Save analysis.py and run your script.
  • You should see a line chart with data points connected by a line, showing how ByteShop's revenue changes month to month. Check that monthly_revenue.png appears in your project folder.

Dates looking garbled on the x-axis?

If your date labels overlap or show raw timestamps, make sure you applied plt.xticks(rotation=45) before plt.tight_layout(). If dates still look odd, your month column might be a string instead of a datetime. Try printing monthly_revenue_df.dtypes to check.

Help me fix my date formatting on the x-axis

Create a bar chart of top customers and save all charts

Your top_customers_df DataFrame holds the five highest-spending customers. A vertical bar chart makes it easy to compare spending across people at a glance.

  • Add this final chart code below the monthly revenue chart in analysis.py:
# --- Chart 3: Top 5 Customers by Total Spend (bar chart) ---
fig3, ax3 = plt.subplots(figsize=(10, 6))

# Plot top customers as a vertical bar chart
top_customers_df.plot(kind='bar', x=[[CUST_COL="your column name for the customer names"]], y=[[SPEND_COL="your column name for total spending"]], ax=ax3, legend=False)

# Add axis labels and title
ax3.set_xlabel([[XLABEL_3="a label for the customer axis, e.g. Customer"]])
ax3.set_ylabel([[YLABEL_3="a label for the spending axis, e.g. Total Spend ($)"]])
ax3.set_title([[TITLE_3="a descriptive title, e.g. Top 5 Customers by Total Spend"]])

# Rotate x-axis labels so names don't overlap
plt.xticks(rotation=45, ha='right')

# Save the figure as a PNG file
plt.tight_layout()
plt.savefig('top_customers.png')
plt.show()

print('All charts saved successfully!')

What does this code do?

This chart uses .plot(kind='bar') for vertical bars (as opposed to 'barh' for horizontal). The ha='right' argument in plt.xticks() aligns rotated labels to the right so they sit neatly under each bar.

Each plt.savefig() call saves the current figure to a PNG file. By the end of your script, you will have three files: revenue_by_category.png, monthly_revenue.png, and top_customers.png.

  • Save analysis.py and run your script.
  • You should see all three chart windows appear. Confirm that all three PNG files exist in your project folder by running:
ls *.png

You should see revenue_by_category.png, monthly_revenue.png, and top_customers.png listed in the output.

Only seeing one or two PNG files?

If a chart is missing, your script likely hit an error partway through. Run the script again and look at the traceback. The most common issue is a KeyError from a mismatched column name in one of the later charts. Check that top_customers_df.columns matches the x and y values you used.

Help me figure out why my charts aren't all saving

✔️ Awesome, I've got everything!

Great. Double check that you have saved analysis.py and all three PNG files are present in your project folder.

ⓧ I'd like to double check the full code

Here is the complete visualization section that should appear at the bottom of your analysis.py file, after your DataFrame loading code:

import matplotlib.pyplot as plt

# --- Chart 1: Revenue by Product Category (horizontal bar chart) ---
fig1, ax1 = plt.subplots(figsize=(10, 6))
revenue_by_category_df.plot(kind='barh', x='category', y='total_revenue', ax=ax1, legend=False)
ax1.set_xlabel('Revenue ($)')
ax1.set_ylabel('Product Category')
ax1.set_title('Total Revenue by Product Category')
plt.tight_layout()
plt.savefig('revenue_by_category.png')
plt.show()

# --- Chart 2: Monthly Revenue Trends (line chart) ---
fig2, ax2 = plt.subplots(figsize=(12, 6))
ax2.plot(monthly_revenue_df['month'], monthly_revenue_df['total_revenue'], marker='o', linewidth=2)
plt.xticks(rotation=45)
ax2.grid(True, alpha=0.3)
ax2.set_xlabel('Month')
ax2.set_ylabel('Revenue ($)')
ax2.set_title('Monthly Revenue Trends')
plt.tight_layout()
plt.savefig('monthly_revenue.png')
plt.show()

# --- Chart 3: Top 5 Customers by Total Spend (bar chart) ---
fig3, ax3 = plt.subplots(figsize=(10, 6))
top_customers_df.plot(kind='bar', x='name', y='total_spend', ax=ax3, legend=False)
ax3.set_xlabel('Customer')
ax3.set_ylabel('Total Spend ($)')
ax3.set_title('Top 5 Customers by Total Spend')
plt.xticks(rotation=45, ha='right')
plt.tight_layout()
plt.savefig('top_customers.png')
plt.show()

print('All charts saved successfully!')

Your column names may differ

The column names above (category, total_revenue, month, name, total_spend) are based on common SQL alias choices. If you used different aliases in your SQL queries from Step 4, replace these with your actual column names. Print any DataFrame's .columns attribute to check.

Secret mission

Customer Segmentation Analysis

Your analysis dashboards are impressive, but real businesses need to know WHO their customers are, not just what they buy. Can you build a customer segmentation system that groups buyers into Bronze, Silver, and Gold tiers based on their lifetime spending?

Clean Up Your Resources

Clean Up Your Resources

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

Resources you used:

  • PostgreSQL 17 server (running on localhost:5432).
  • The byteshop database with four tables (customers, products, orders, order_items).
  • pgAdmin 4 (bundled with PostgreSQL).
  • Python virtual environment with installed packages (pandas, sqlalchemy, psycopg2-binary, matplotlib).
  • Local project files: analysis.py and four PNG chart files.

Keep everything running

No action needed. Choose this if you're still actively building on this project or plan to add more queries and visualizations.

  • Your PostgreSQL server continues running in the background as a Windows service.
  • Your byteshop database and all tables remain accessible through pgAdmin.
  • Your Python virtual environment stays on disk, ready to reactivate whenever you need it.

Pause - I'll come back to this later

Stop the PostgreSQL service to free up memory. Your data and files stay intact for when you return.

  • Open Services on Windows by pressing the Windows key, typing Services, and pressing Enter.
  • Find postgresql-x64-17 in the list.
  • Right-click it and select Stop.
  • Deactivate your Python virtual environment by running:
deactivate

When you want to resume, restart the PostgreSQL service and reactivate your virtual environment.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each step below.

Drop the byteshop database
  • Open pgAdmin 4.
  • In the left-hand tree, expand Servers > PostgreSQL 17 > Databases.
  • Right-click the byteshop database and select Drop/Delete.
  • Confirm the deletion when prompted.
Delete your project files

Delete the folder that contains analysis.py and your PNG chart files.

  • Open File Explorer (Windows) or Finder (macOS) and navigate to where you stored your project.
  • Delete the entire project folder (this removes analysis.py, your virtual environment folder, and all PNG files).
Uninstall PostgreSQL 17 (optional)

Only do this if you no longer need PostgreSQL on your machine.

  • On Windows, open Control Panel and go to Uninstall or Change a Program.
  • Right-click PostgreSQL 17 and select Uninstall/Change.
  • Select Entire application and click Next.
  • Click OK when the uninstaller completes.

What about pgAdmin 4?

pgAdmin 4 was bundled with your PostgreSQL installer. Uninstalling PostgreSQL using the steps above removes pgAdmin 4 as well.

Nice Work!

Nice Work!

You just built a complete data analysis pipeline from database to visualization! Starting from an empty PostgreSQL instance, you designed a relational schema, populated it with sample data, connected Python, and turned raw rows into charts that answer real business questions.

Here's what you accomplished:

  • Designed a relational database schema with primary keys and foreign keys in PostgreSQL, then populated it with realistic e-commerce data using SQL INSERT statements.
  • Connected Python to PostgreSQL using SQLAlchemy and wrote analytical queries with JOIN, GROUP BY, and aggregate functions to answer business questions about revenue, trends, and top customers.
  • Loaded SQL results into Pandas DataFrames with pd.read_sql() and created publication-quality bar charts and line plots with matplotlib, saved as PNG files.
  • Secret Mission: Segmented customers into Bronze, Silver, and Gold spending tiers and visualized the distribution with count and percentage annotations.

Ready to quiz yourself?