Superstore Sales Profitability Analysis

Uncover hidden profit losses in retail data using SQL and Python charts.

Introduction

30 Second Summary

Every business has products that look like winners on the sales charts but are secretly bleeding money. Discounts can turn a top-seller into a money pit, and surface-level metrics hide the problem.

In this project, you will load 10,000 retail transactions into PostgreSQL, write SQL queries to uncover which product categories are losing money despite strong sales, and generate professional charts with Python that tell the profitability story.

What You'll Build

A finished analysis you can run from the command line that connects to your database, crunches the numbers, and saves three publication-ready charts proving exactly where discounts destroy profitability.

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

A PostgreSQL database with ~10,000 retail transactions loaded and queryable from both pgAdmin and Python.
SQL queries that reveal a hidden problem: high-revenue categories with terrible profit margins caused by excessive discounting.
A Python script that connects to your database and generates three publication-ready charts visualizing the discount-profit relationship using matplotlib.
Secret Mission: A regional breakdown identifying which Region + Category combination is bleeding the most money.

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, WHERE, GROUP BY) and have basic Python familiarity. You also need pgAdmin, VS Code, Python 3.9.6, and PostgreSQL installed on your machine.

Before We Start

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

You have raw sales data, a database, and Python on your machine. By the end of this project, you'll turn those ingredients into a complete profitability analysis that uncovers a hidden business problem and visualizes it with professional charts.

Set Up Your Analytics Environment

Your analysis pipeline has two moving parts: SQL queries that run inside PostgreSQL, and a Python script that pulls query results and turns them into charts.

Before any of that can work, you need the Python libraries that talk to your database and draw charts. You also need a dedicated database to keep your sales data separate from anything else on your server.

In this step, get ready to:

Install the psycopg2-binary and matplotlib Python packages.
Create a new PostgreSQL database called superstore.
Verify the database connection using the Query Tool.

Install the Python packages

Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
Type VS Code and press Enter to open it.
Open the built-in terminal by pressing Ctrl+` (both macOS and Windows).

Install the two packages your project needs by running this command:

pip install psycopg2-binary==2.9.12 matplotlib==3.9.3

What do these packages do?

psycopg2-binary is a PostgreSQL adapter for Python. It lets your Python scripts send SQL queries to your database and receive results back.
matplotlib is a charting library. It draws bar charts, line graphs, and other visualizations from your data.

The version pins (==2.9.12 and ==3.9.3) ensure you get versions compatible with Python 3.9.6.

Verify both packages installed correctly by running:

python -c "import psycopg2; import matplotlib; print('Ready!')"

You should see Ready! printed in your terminal.

Seeing an error instead of Ready?

If you see ModuleNotFoundError, the install did not complete successfully. Re-run the pip install command and check for error messages in the output.
If python is not recognized, try python3 instead. Some systems use python3 as the default command.
If you see a permissions error, try adding --user to the end of the pip install command.

Help me fix my Python package installation error.

Create the superstore database in pgAdmin

Your sales data needs its own dedicated database so it stays separate from any other PostgreSQL data on your machine.

Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
Type pgAdmin and press Enter to open it.
In the left sidebar, expand Servers to connect to your local PostgreSQL server.
Right-click on Databases, then select Create and click Database.
In the Database name field, type superstore.
Click Save.

You should see superstore appear in the left sidebar under Databases.

Why a separate database?

PostgreSQL can hold many databases on a single server. Creating a dedicated superstore database keeps your sales analysis tables isolated. If something goes wrong, you can drop the whole database without affecting anything else.

Don't see the Databases node?

Make sure you expanded the Servers group first. pgAdmin may ask for your PostgreSQL password when you first connect.
If you see a connection error, confirm your PostgreSQL service is running. On macOS, check System Preferences or your PostgreSQL app. On Windows, search for Services and confirm PostgreSQL is started.

Help me connect pgAdmin to my local PostgreSQL server.

Open the Query Tool and verify the connection

In the left sidebar, expand Databases if it is not already expanded.
Right-click on superstore, then select Query Tool.

A new tab opens with a blank query editor. This is where you will write and run all your SQL for this project.

Before you run the next query, what do you expect it to return?

Confirm the connection works by typing this query into the Query Tool and clicking the Execute button (the play icon):

SELECT version();

You should see a single row in the results panel containing your PostgreSQL version string.

Query returning an error?

Make sure you right-clicked on superstore specifically when opening the Query Tool. If you opened it from a different database, close the tab and reopen it from superstore.
Check that you included the semicolon at the end of the query.

Help me fix my pgAdmin Query Tool connection error.

Your analytics environment is ready. Next up, you will load 10,000 rows of sales data from the Superstore CSV into your new database.

Load the Superstore Data into PostgreSQL

Your environment is ready with all the tools you need. Now you need actual data to analyze.

The raw SampleSuperstore.csv on your hard drive is just a file sitting on disk. Loading it into PostgreSQL gives you the power to slice, filter, and aggregate 10,000 rows instantly with SQL.

In this step, get ready to:

Verify the CSV column structure and create a matching table in PostgreSQL.
Import approximately 10,000 rows of sales data using pgAdmin's Import/Export dialog.
Confirm the data loaded correctly with COUNT and sample queries.

Verify the CSV and create the sales_data table

Before you write a CREATE TABLE statement, you need to know exactly what columns the CSV contains and what order they appear in. The table columns must match the CSV header row precisely for the import to work.

Switch to VS Code.
Open the SampleSuperstore.csv file by clicking File in the top menu bar, then Open File, and browsing to wherever you downloaded the CSV.
Look at the first row (the header). You should see 13 column names: Ship Mode, Segment, Country, City, State, Postal Code, Region, Category, Sub-Category, Sales, Quantity, Discount, Profit.

Why check the CSV first?

The CREATE TABLE column order must match the CSV column order exactly. If they are misaligned, PostgreSQL imports data into the wrong columns. Checking the header row takes 10 seconds and prevents confusing errors later.

Now you will create a table that matches this structure. The column names use underscores instead of spaces to follow SQL naming conventions.

Switch back to pgAdmin and return to the Query Tool for your superstore database.
Create the sales_data table by pasting and running this SQL:

CREATE TABLE sales_data (
    ship_mode VARCHAR(50),
    segment VARCHAR(50),
    country VARCHAR(50),
    city VARCHAR(100),
    state VARCHAR(50),
    postal_code VARCHAR(20),
    region VARCHAR(50),
    category VARCHAR(50),
    sub_category VARCHAR(50),
    sales NUMERIC(10,2),
    quantity INTEGER,
    discount NUMERIC(4,2),
    profit NUMERIC(10,2)
);

What does this code do?

Each column maps to one CSV header. The data types control what PostgreSQL stores:

VARCHAR columns hold text like city names and categories.
NUMERIC(10,2) stores dollar amounts with two decimal places for sales and profit.
postal_code is VARCHAR, not INTEGER, because some US postal codes have leading zeros (like 01234) that would be lost in a number column.
discount is NUMERIC(4,2) because discounts are stored as decimals (0.2 means 20% off).

Click the Execute/Run button (the play icon) in the Query Tool toolbar, or press F5.
You should see a message in the Messages panel that says the query was successful.
In the pgAdmin tree on the left, expand superstore > Schemas > public > Tables. You should see sales_data listed. If you do not see it, right-click Tables and select Refresh.

Don't see the table?

Make sure you are connected to the superstore database, not the default postgres database. Check the Query Tool tab title.
If you see an error about the table already existing, you may have run the query twice. That is fine. The table is already created.

Help me troubleshoot why my CREATE TABLE didn't work in pgAdmin.

Now save this SQL to a file for your records.

Switch back to VS Code.
Create a new file by pressing Cmd+N (macOS) or Ctrl+N (Windows).
Paste the CREATE TABLE SQL you just ran into this file.
Save the file as create_table.sql in the same folder as your SampleSuperstore.csv by pressing Cmd+S (macOS) or Ctrl+S (Windows).

Import the CSV using pgAdmin's Import/Export dialog

The table exists but it is empty. Now you will use pgAdmin's built-in Import/Export tool to load all ~10,000 rows from the CSV into the table.

Switch back to pgAdmin.
In the pgAdmin tree on the left, expand superstore > Schemas > public > Tables.
Right-click on sales_data and select Import/Export Data.

A dialog window opens with several settings. Configure the General tab first:

Set the Import/Export switch to Import.
Click the file browser button next to Filename and browse to your SampleSuperstore.csv file.
Set Format to csv.

Now switch to the Options tab:

Set Header to Yes. This tells PostgreSQL to skip the first row (which contains column names, not data).
Click OK to start the import.
Wait for the success notification to appear. This may take a few seconds while pgAdmin uploads and processes the file.

Seeing an import error?

Double-check that Header is set to Yes in the Options tab. Without this, PostgreSQL tries to insert the header row as data, which causes a type error.
Make sure the file path points to the actual SampleSuperstore.csv file. If the file browser does not show it, try navigating to your Downloads folder.
If you see a permissions error, try moving the CSV file to your Desktop and selecting it from there.

Help me fix my pgAdmin CSV import error.

Verify the data loaded correctly

The import reported success, but you should always verify that the expected number of rows actually landed in the table.

Return to the Query Tool for your superstore database.
Before you run the next query, predict: roughly how many rows do you think the CSV contains?
Count the total rows by running this query:

SELECT COUNT(*) FROM sales_data;

You should see approximately 9,994 rows. This confirms all the data from the CSV made it into your table.

Now inspect a few sample rows to make sure the data looks correct by running:

SELECT * FROM sales_data LIMIT 5;

You should see 5 rows with data in all 13 columns, including sales, discount, and profit values.

What should I see?

The sample rows should show text values in columns like ship_mode, segment, and category. Numeric columns like sales and profit should show dollar amounts with decimals. The discount column shows values between 0 and 0.8, where 0.2 means a 20% discount.

Seeing 0 rows?

The import may have silently failed. Go back and repeat the Import/Export Data steps, making sure you selected Import (not Export) and pointed to the correct file.
Check that you are running the SELECT in the Query Tool connected to the superstore database, not postgres.

Help me figure out why my sales_data table has no rows after import.

✔️ Awesome, I've got everything!

Great. Make sure you have saved create_table.sql in VS Code.

ⓧ I'd like to double check the full code

Here is the complete create_table.sql file. Make sure yours matches exactly:

CREATE TABLE sales_data (
    ship_mode VARCHAR(50),
    segment VARCHAR(50),
    country VARCHAR(50),
    city VARCHAR(100),
    state VARCHAR(50),
    postal_code VARCHAR(20),
    region VARCHAR(50),
    category VARCHAR(50),
    sub_category VARCHAR(50),
    sales NUMERIC(10,2),
    quantity INTEGER,
    discount NUMERIC(4,2),
    profit NUMERIC(10,2)
);

Your database now holds nearly 10,000 rows of real retail data, ready to query. Next up, you will write SQL queries to explore how the business is performing across categories and sub-categories.

Explore Sales Performance with SQL

Your database is loaded with nearly 10,000 sales transactions. Now you can start asking the business questions that actually matter.

Before you can find problems, you need to understand what sells. In this step, you'll write SQL queries that give you the surface-level view of the business. This is the same picture every stakeholder sees when they look at sales reports.

In this step, get ready to:

Find total sales by product category.
Identify the top 10 sub-categories by revenue.
Calculate the overall profit margin for the business.

Find total sales by category

The first question any analyst asks about retail data is simple: which product categories bring in the most money?

In your pgAdmin Query Tool (already connected to your superstore database), clear any previous query and run this:

-- Total sales by category
SELECT category,
       ROUND(SUM(sales)::numeric, 2) AS total_sales
FROM sales_data
GROUP BY category
ORDER BY total_sales DESC;

What does this query do?

This query groups all 9,994 rows by their category value and sums up the sales column for each group.

GROUP BY category collapses rows into one row per category.
SUM(sales) adds up every sale amount within each group.
ROUND(..., 2) keeps the output to two decimal places.
ORDER BY total_sales DESC puts the highest-revenue category at the top.

In the Data Output panel below your query editor, you should see three rows. Technology leads in total sales, followed by Furniture and Office Supplies.

Not seeing any results?

Make sure you clicked the Execute button (play icon) or pressed F5 to run the query.
Check that the Query Tool tab says superstore in its title. If it says a different database, right-click superstore in the tree and select Query Tool again.
If you see an error about sales_data not existing, confirm your data loaded correctly in the previous step.

Help me debug my GROUP BY query in pgAdmin.

Find the top 10 sub-categories by sales

Categories give you the big picture. Sub-categories reveal which specific products drive the most revenue.

Clear the Query Tool editor and run this query:

-- Top 10 sub-categories by sales
SELECT sub_category,
       ROUND(SUM(sales)::numeric, 2) AS total_sales
FROM sales_data
GROUP BY sub_category
ORDER BY total_sales DESC
LIMIT 10;

What does this query do?

This query works the same way as the previous one, but groups by sub_category instead. The LIMIT 10 clause cuts the results to only the top 10 earners.

These look like the "winners" of the business. Products like Phones and Chairs appear near the top because they generate the most raw sales dollars.

Your results show 10 rows in the Data Output panel. Note down these top sellers. They look like the heroes of the business from a revenue perspective.

Seeing fewer than 10 rows?

Double-check that you typed LIMIT 10 at the end of your query.
If you see exactly 10 rows but unexpected sub-categories, your data loaded correctly. The dataset has 17 distinct sub-categories, so 10 is the right result count.

Help me troubleshoot my LIMIT query results.

Check the overall business health

You've seen which categories and sub-categories sell the most. Now for the big question: is this business actually making money overall?

Based on the strong sales numbers you've seen so far, what do you expect the overall profit picture to look like? Take a guess before you run this next query.

Clear the Query Tool editor and run this query:

-- Overall business health
SELECT ROUND(SUM(sales)::numeric, 2) AS total_sales,
       ROUND(SUM(profit)::numeric, 2) AS total_profit,
       ROUND((SUM(profit) / SUM(sales) * 100)::numeric, 2) AS profit_margin_pct
FROM sales_data;

What does this query do?

This query calculates three business-wide metrics in a single row:

total_sales sums every sale in the dataset.
total_profit sums the profit column across all transactions.
profit_margin_pct divides total profit by total sales and multiplies by 100 to get a percentage.

No GROUP BY here because you want one single row summarizing the entire business.

Your result shows a single row with a positive profit margin. The business is making money overall. If you look at just these aggregate numbers, everything appears healthy.

Getting a division error?

Make sure the parentheses in your query match exactly. The profit margin calculation wraps SUM(profit) / SUM(sales) * 100 together before casting to numeric.
If you see NULL values, your data may not have loaded completely. Re-run SELECT COUNT(*) FROM sales_data; to confirm you have approximately 9,994 rows.

Help me fix my profit margin calculation query.

Save your queries for reference

Switch to VS Code.
Create a new file called explore_sales.sql in the same project folder where create_table.sql lives.
Paste all three queries into this file (including the comments) and save it:

-- Total sales by category
SELECT category,
       ROUND(SUM(sales)::numeric, 2) AS total_sales
FROM sales_data
GROUP BY category
ORDER BY total_sales DESC;

-- Top 10 sub-categories by sales
SELECT sub_category,
       ROUND(SUM(sales)::numeric, 2) AS total_sales
FROM sales_data
GROUP BY sub_category
ORDER BY total_sales DESC
LIMIT 10;

-- Overall business health
SELECT ROUND(SUM(sales)::numeric, 2) AS total_sales,
       ROUND(SUM(profit)::numeric, 2) AS total_profit,
       ROUND((SUM(profit) / SUM(sales) * 100)::numeric, 2) AS profit_margin_pct
FROM sales_data;

Why save queries in a file?

Keeping your queries in .sql files means you can re-run them anytime, share them with colleagues, and include them in your portfolio. It also documents your analytical thought process.

✔️ Awesome, I've got everything!

Great. Double check you've saved explore_sales.sql and move on.

ⓧ I'd like to double check the full code

Here is the complete explore_sales.sql file. Make sure yours matches exactly:

-- Total sales by category
SELECT category,
       ROUND(SUM(sales)::numeric, 2) AS total_sales
FROM sales_data
GROUP BY category
ORDER BY total_sales DESC;

-- Top 10 sub-categories by sales
SELECT sub_category,
       ROUND(SUM(sales)::numeric, 2) AS total_sales
FROM sales_data
GROUP BY sub_category
ORDER BY total_sales DESC
LIMIT 10;

-- Overall business health
SELECT ROUND(SUM(sales)::numeric, 2) AS total_sales,
       ROUND(SUM(profit)::numeric, 2) AS total_profit,
       ROUND((SUM(profit) / SUM(sales) * 100)::numeric, 2) AS profit_margin_pct
FROM sales_data;

You now have three query results showing category sales rankings, top sub-categories, and an overall profit margin. From this surface view, the business looks healthy. Sales are strong across categories, the top sub-categories are generating serious revenue, and the overall margin is positive.

But here is the question a sharp analyst would ask: does a positive overall margin mean every part of the business is profitable? In the next step, you'll dig into profit at the category level and discover that these healthy-looking numbers are hiding a problem.

Uncover the Hidden Profitability Problem

In the last step, your queries painted a rosy picture. The business is profitable overall, Technology leads in sales, and familiar sub-categories like Phones and Chairs top the revenue chart.

But sales alone are half the story. In this step, you will add profit to every query and watch that healthy surface crack open. Some of those "top sellers" are actually losing money on every sale combined.

In this step, get ready to:

Compare sales against profit by category to find margin outliers.
Identify sub-categories that are losing money despite generating revenue.
Investigate whether discounts are destroying profitability.

Compare sales vs profit by category

Your Step 3 query ranked categories by total sales alone. Now you will run a nearly identical query, but with two new columns: total profit and profit margin percentage.

In pgAdmin, right-click the superstore database in the tree and select Query Tool to open a fresh tab.
Paste the following query into the new Query Tool tab:

-- Sales vs Profit by Category
SELECT category,
       ROUND(SUM(sales)::numeric, 2) AS total_sales,
       ROUND(SUM(profit)::numeric, 2) AS total_profit,
       ROUND((SUM(profit) / SUM(sales) * 100)::numeric, 2) AS profit_margin_pct
FROM sales_data
GROUP BY category
ORDER BY total_sales DESC;

What does this query do?

This is the same GROUP BY category query from Step 3, but with two critical additions.

total_profit sums the profit column, which accounts for costs and discounts that the sales column ignores.
profit_margin_pct divides profit by sales and multiplies by 100, giving you the percentage of each sales dollar that becomes actual profit.

Before you run this, think back to your Step 3 results. Which category do you expect to have the highest profit margin? Which the lowest?

Click the Execute/Refresh button (or press F5) to run the query.

Look at the results. Technology and Office Supplies have healthy margins, but Furniture's profit margin is dramatically lower despite decent sales revenue. That gap is your first clue that something is wrong.

Seeing an error about division by zero?

This would only happen if a category had zero total sales. Confirm your data loaded correctly by running SELECT COUNT(*) FROM sales_data; in a separate tab. You should see approximately 9,994 rows.

Help me debug my profitability query in pgAdmin

Find the money-losing sub-categories

You now know Furniture's margins are weak. The next question is: which specific products within Furniture (and other categories) are actually losing money overall?

Add a blank line below your first query and paste this second query:

-- Money-losing sub-categories
SELECT sub_category,
       ROUND(SUM(sales)::numeric, 2) AS total_sales,
       ROUND(SUM(profit)::numeric, 2) AS total_profit
FROM sales_data
GROUP BY sub_category
HAVING SUM(profit) < 0
ORDER BY total_profit ASC;

What does HAVING do here?

HAVING filters groups after aggregation, unlike WHERE which filters individual rows before grouping. Here it keeps only sub-categories whose total profit across all orders is negative.

Highlight only this second query (from SELECT sub_category through the semicolon) and press F5 to run just that selection.

The results show sub-categories like Tables and Bookcases with negative total profit. These products generate revenue but lose money overall. Some of them appeared in your "Top 10 by sales" list from Step 3.

Query returns no rows?

Make sure you highlighted only the second query before pressing F5. If you ran both queries together, the results panel shows the output of the last statement. Try selecting just the second query text and running again.

Help me run a specific query selection in pgAdmin

Investigate the discount-profit relationship

You know which products lose money. The next question is why. The discount column holds the answer. This query groups all orders into discount buckets and measures the average profit in each bucket.

Add a blank line below your second query and paste this third query:

-- Discount impact on profit
SELECT
    CASE
        WHEN discount = 0 THEN 'No Discount'
        WHEN discount > 0 AND discount <= 0.2 THEN 'Low (1-20%)'
        WHEN discount > 0.2 AND discount <= 0.4 THEN 'Medium (21-40%)'
        ELSE 'High (41%+)'
    END AS discount_level,
    COUNT(*) AS order_count,
    ROUND(AVG(profit)::numeric, 2) AS avg_profit,
    ROUND(SUM(profit)::numeric, 2) AS total_profit
FROM sales_data
GROUP BY discount_level
ORDER BY avg_profit DESC;

What does the CASE statement do?

CASE works like an if-else chain inside SQL. It evaluates each WHEN condition in order and assigns a label to matching rows.

The discount column stores values as decimals (0.2 means 20% off). This query groups those decimals into human-readable buckets so you can see the trend at a glance instead of comparing dozens of individual discount levels.

Highlight only the third query and press F5 to run it.

The pattern is clear. Orders with no discount or low discounts are profitable. Orders in the Medium and High buckets have negative average profit. Discounts above 20% consistently destroy margins.

Why does this matter for your portfolio?

You just demonstrated a core analyst skill: challenging surface-level metrics. Step 3 showed a healthy business. Step 4 proved that was misleading. This ability to dig deeper is exactly what hiring managers look for in entry-level data analyst candidates.

Seeing unexpected discount levels in the output?

Check that your CASE conditions use decimal values (0.2, 0.4) not whole numbers (20, 40). The discount column stores percentages as decimals where 0.2 means 20% off.

Help me understand the discount column format in my Superstore data

Save your profitability queries

In the Query Tool, click Save As (the disk icon with a pencil, or use Ctrl+Shift+S).
Save the file as profitability_analysis.sql in your project folder.

Before you move on, take stock of what your three queries proved. Do they support the conclusion that the business is healthy, or do they tell a different story?

Your results confirm: at least one category (Furniture) has a profit margin dramatically below the others. Specific sub-categories have negative total profit. High discount levels correlate with negative average profit. The "top sellers" story from Step 3 was incomplete.

✔️ Awesome, I've got everything!

Great. Make sure you have saved profitability_analysis.sql with all three queries.

ⓧ I'd like to double check the full code

Your profitability_analysis.sql should contain these three query blocks in order. Scroll up and confirm each is present:

Query 1: "Sales vs Profit by Category" with total_sales, total_profit, and profit_margin_pct grouped by category.
Query 2: "Money-losing sub-categories" with a HAVING SUM(profit) < 0 filter.
Query 3: "Discount impact on profit" with a CASE statement bucketing discounts into No Discount, Low, Medium, and High levels.

You have uncovered the real story. Numbers in a table are convincing for analysts, but stakeholders need visuals. Next up, you will connect Python to your database and turn these findings into publication-ready charts.

Visualize the Story with Python

Your SQL queries proved that high-revenue products can silently bleed money. But numbers in a table are hard to present to a stakeholder or include in a portfolio.

Charts make the discount-profit problem immediately obvious. In this step, you will connect Python to your PostgreSQL database and generate three publication-ready charts using matplotlib.

In this step, get ready to:

Create a Python script that connects to your superstore database.
Build a grouped bar chart comparing sales vs profit by category.
Generate charts showing discount impact and sub-category profitability.

Create a Python script and connect to PostgreSQL

Before building charts, you need to confirm that Python can talk to your database. A quick connection test catches credential issues early.

Switch back to your VS Code terminal and test the database connection by running this command:

python -c "import psycopg2; conn = psycopg2.connect(host='localhost', database='superstore', user='postgres', password='your-password-here'); print('Connected!'); conn.close()"

Replace your-password-here with the password you set for your local PostgreSQL postgres user.

You should see Connected! printed in the terminal.

Seeing a connection error?

If you see OperationalError, double-check your PostgreSQL password. Make sure your local PostgreSQL server is running.

Help me troubleshoot my psycopg2 connection error.

Create a new file called visualize_profits.py in your project folder by running:

touch visualize_profits.py

Open visualize_profits.py in VS Code and add the following connection code:

import psycopg2
import matplotlib.pyplot as plt

conn = psycopg2.connect(
    host="localhost",
    database="superstore",
    user="postgres",
    password="your-password-here"
)

cursor = conn.cursor()

What does this code do?

psycopg2 is the Python library that lets you talk to PostgreSQL databases.
matplotlib.pyplot provides the charting functions you will use to create bar charts.
psycopg2.connect() opens a connection to your local superstore database using the credentials you provide.
conn.cursor() creates a cursor object that executes SQL queries and fetches results.

Replace your-password-here with the same PostgreSQL password you used in the terminal test.
Save visualize_profits.py.

Build the sales and discount charts

Your first chart will recreate the category-level sales vs profit comparison as a visual. This makes the Furniture problem jump off the screen.

In visualize_profits.py, add the following code below the cursor = conn.cursor() line:

# Chart 1: Sales vs Profit by Category
cursor.execute("""
    SELECT category,
           ROUND(SUM(sales)::numeric, 2) AS total_sales,
           ROUND(SUM(profit)::numeric, 2) AS total_profit
    FROM sales_data
    GROUP BY category
    ORDER BY total_sales DESC;
""")
categories_data = cursor.fetchall()

categories = [row[0] for row in categories_data]
sales = [float(row[1]) for row in categories_data]
profits = [float(row[2]) for row in categories_data]

fig, ax = plt.subplots(figsize=(10, 6))
x = range(len(categories))
width = 0.35
ax.bar([i - width/2 for i in x], sales, width, label="Sales", color="steelblue")
ax.bar([i + width/2 for i in x], profits, width, label="Profit", color="orange")
ax.set_xlabel("Category")
ax.set_ylabel("Amount (USD)")
ax.set_title("Sales vs Profit by Category")
ax.set_xticks(x)
ax.set_xticklabels(categories)
ax.legend()
plt.tight_layout()
plt.savefig("sales_vs_profit_by_category.png")
plt.close()

What does this chart code do?

cursor.execute() runs the same category-level query you wrote in pgAdmin, now from Python.
cursor.fetchall() pulls all result rows into a Python list of tuples.
The list comprehensions extract each column into its own list (categories, sales, profits).
ax.bar() draws two sets of bars side by side (grouped bar chart) so you can visually compare sales to profit for each category.
plt.savefig() saves the chart as a PNG file instead of displaying it in a window.

Save visualize_profits.py and run it to test the first chart:

python visualize_profits.py

Confirm the file was created by running:

ls sales_vs_profit_by_category.png

You should see sales_vs_profit_by_category.png listed. If you open it, you will see Technology with high sales and profit, and Furniture with decent sales but a much smaller profit bar.

File not created?

If you see a connection error, confirm your password in the script matches the one you tested earlier. If you see a syntax error, check that the triple-quoted SQL string has matching opening and closing """.

Help me debug why my visualize_profits.py script is failing.

Now add the second chart, which visualizes how discounts destroy profitability.

In visualize_profits.py, add the following code below the plt.close() line from Chart 1:

# Chart 2: Discount Impact on Profit
cursor.execute("""
    SELECT
        CASE
            WHEN discount = 0 THEN 'No Discount'
            WHEN discount > 0 AND discount <= 0.2 THEN 'Low (1-20%)'
            WHEN discount > 0.2 AND discount <= 0.4 THEN 'Medium (21-40%)'
            ELSE 'High (41%+)'
        END AS discount_level,
        ROUND(AVG(profit)::numeric, 2) AS avg_profit
    FROM sales_data
    GROUP BY discount_level
    ORDER BY avg_profit DESC;
""")
discount_data = cursor.fetchall()

levels = [row[0] for row in discount_data]
avg_profits = [float(row[1]) for row in discount_data]

fig, ax = plt.subplots(figsize=(8, 5))
colors = ["green" if p > 0 else "red" for p in avg_profits]
ax.bar(levels, avg_profits, color=colors)
ax.set_xlabel("Discount Level")
ax.set_ylabel("Average Profit (USD)")
ax.set_title("How Discounts Impact Profitability")
ax.axhline(y=0, color="black", linestyle="-", linewidth=0.5)
plt.tight_layout()
plt.savefig("discount_impact_on_profit.png")
plt.close()

What does this chart do?

The CASE statement buckets each order into a discount level, just like your earlier SQL query.
The color list turns bars green for positive average profit and red for negative. This makes the "danger zone" immediately visible.
ax.axhline() draws a zero-line so the viewer can instantly see which discount levels fall below break-even.

Save visualize_profits.py and run it again:

python visualize_profits.py

Confirm the second chart was created:

ls discount_impact_on_profit.png

You should see discount_impact_on_profit.png listed. Opening it shows green bars for No Discount and Low discount levels, with red bars for Medium and High levels that dip below zero.

Build the sub-category chart and finalize the script

The third chart pinpoints exactly which sub-categories are losing money. This is the chart that makes your analysis actionable.

In visualize_profits.py, add the following code below the plt.close() line from Chart 2:

# Chart 3: Bottom Sub-Categories by Profit
cursor.execute("""
    SELECT sub_category,
           ROUND(SUM(profit)::numeric, 2) AS total_profit
    FROM sales_data
    GROUP BY sub_category
    ORDER BY total_profit ASC
    LIMIT 10;
""")
subcategory_data = cursor.fetchall()

sub_cats = [row[0] for row in subcategory_data]
sub_profits = [float(row[1]) for row in subcategory_data]

fig, ax = plt.subplots(figsize=(10, 6))
colors = ["red" if p < 0 else "green" for p in sub_profits]
ax.barh(sub_cats, sub_profits, color=colors)
ax.set_xlabel("Total Profit (USD)")
ax.set_title("Sub-Categories by Profitability (Bottom 10)")
ax.axvline(x=0, color="black", linestyle="-", linewidth=0.5)
plt.tight_layout()
plt.savefig("subcategory_profitability.png")
plt.close()

cursor.close()
conn.close()

print("Analysis complete! Charts saved:")
print("  - sales_vs_profit_by_category.png")
print("  - discount_impact_on_profit.png")
print("  - subcategory_profitability.png")

What does this final section do?

ax.barh() creates a horizontal bar chart so sub-category names are readable without rotating text.
The query orders by profit ascending with LIMIT 10, so the worst performers appear first.
cursor.close() and conn.close() cleanly shut down the database connection when finished.
The print statements give you a confirmation message listing all generated files.

Save visualize_profits.py.

Before you run the final script, what do you expect to see in the terminal output?

Run the complete script:

python visualize_profits.py

You should see this output:

Analysis complete! Charts saved:
  - sales_vs_profit_by_category.png
  - discount_impact_on_profit.png
  - subcategory_profitability.png

Confirm all three chart files exist in your project folder:

ls *.png

You should see all three PNG files listed. Open subcategory_profitability.png to see the bottom performers. Tables should appear as a red bar extending far to the left, confirming it is the biggest money-loser.

Script ran but no PNG files appeared?

Make sure you are running the script from the same folder where visualize_profits.py is saved. The PNGs are created in whatever directory you run the command from.

If you see a SyntaxError, check that every cursor.execute(""" has a matching """) closing it.

Help me fix my visualize_profits.py script.

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

Your visualize_profits.py should contain these sections in order. Scroll up and confirm each one is present:

Imports and connection setup (import psycopg2, import matplotlib.pyplot as plt, psycopg2.connect(...), conn.cursor()).
Chart 1: Sales vs Profit by Category query, data extraction, grouped bar chart, saved as sales_vs_profit_by_category.png.
Chart 2: Discount Impact query with CASE statement, green/red bar chart, saved as discount_impact_on_profit.png.
Chart 3: Bottom Sub-Categories query, horizontal bar chart, saved as subcategory_profitability.png.
Cleanup (cursor.close(), conn.close()) and print confirmation message.

You now have three professional charts that tell a clear story: Furniture underperforms on profit, discounts above 20% destroy margins, and sub-categories like Tables are actively losing money. Next up, the Secret Mission challenges you to drill deeper into regional breakdowns.

Secret mission

Regional Profitability Breakdown

You know discounts are destroying margins and Furniture is the worst offender. But which region is bleeding the most? Add a regional dimension to your analysis and produce a chart that pinpoints the exact region-category combination a manager should investigate first.

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 cloud costs.

Resources you used:

The superstore PostgreSQL database with the sales_data table.
Python packages: psycopg2-binary and matplotlib.
Project folder containing SQL files (create_table.sql, explore_sales.sql, profitability_analysis.sql, regional_analysis.sql), Python scripts (visualize_profits.py, visualize_regional.py), and generated PNG charts.

Keep everything running

No action needed. Choose this if you plan to continue exploring the data, refining your queries, or building additional visualizations.

Your superstore database stays available in pgAdmin whenever you need it.
Your Python scripts and charts remain in your project folder, ready to run or share.
PostgreSQL runs locally with no ongoing costs.

Pause - I'll come back to this later

Since everything runs locally with no costs, there is nothing to pause. Your database and files will be waiting for you when you return.

Your PostgreSQL database does not consume meaningful resources while idle.
Your project files and generated charts stay on disk until you decide to remove them.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow these steps to clean up everything you created.

Drop the database: in pgAdmin, right-click on the superstore database in the browser tree, select Delete/Drop, and confirm.
Uninstall the Python packages by running this in your terminal:

pip uninstall psycopg2-binary matplotlib -y

Delete your project folder (the folder containing your .py and .sql files) by running:

rm -rf ~/Desktop/superstore-analysis

On Windows, use this command instead:

Remove-Item -Recurse -Force ~\Desktop\superstore-analysis

Adjust the folder path

If you saved your project folder in a different location, replace the path above with the actual path to your folder containing the SQL, Python, and PNG files.

Nice Work!

You just built a complete sales profitability analysis from scratch. Starting with a raw CSV file, you loaded data into PostgreSQL, uncovered a hidden business problem with SQL, and produced professional charts that tell the story visually.

You've learned how to:

Load raw CSV data into a PostgreSQL database and write SQL queries using GROUP BY, CASE statements, and aggregation functions to uncover profitability patterns.
Discover that high-revenue products can lose money when discounts exceed 20%, turning a surface-level sales view into a deeper profitability insight.
Connect Python to PostgreSQL using psycopg2 and generate publication-ready bar charts with matplotlib that communicate your findings visually.
Secret Mission: Identified the worst-performing region-category combination and wrote a business recommendation for stakeholders.

Ready to quiz yourself?