Sales Dashboard with SQL and Streamlit

Build an interactive sales dashboard powered by raw SQL queries in Streamlit.

Introduction

30 Second Summary

Sales teams generate tons of data every day. Managers need quick answers to questions like who is closing the most deals, which products are selling best, and how revenue is trending over time.

In this project, you will build an interactive dashboard that pulls insights from a database using SQL queries and displays them as professional visualizations with Streamlit.

What You'll Build

An interactive sales performance dashboard connected to a local SQLite database, where every chart and metric on screen is powered by a SQL query you write yourself.

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

  • A local SQLite database with two related tables containing realistic sales agent and deal data.
  • A Streamlit dashboard with KPI metrics, bar charts, and line charts driven entirely by raw SQL queries.
  • Interactive filters that dynamically modify your SQL WHERE clauses to update all visualizations in real time.
  • Secret Mission: Add a ranked Top Performers Leaderboard powered by advanced SQL subqueries.

Are there any prerequisites?

You should be comfortable with basic Python concepts like variables, functions, and imports. No prior SQL or Streamlit experience is needed.

Before We Start

Before you can query sales data and build charts, you need a local development environment with the right tools installed. This means setting up a Python project with Streamlit (the framework that turns Python scripts into interactive web apps) and confirming that SQLite is ready for your database.

In this step, you'll create a project folder, install Streamlit, and set up the files you'll work in throughout the project.

In this step, get ready to:
  • Create your project folder with a Python virtual environment.
  • Install Streamlit and verify it works.
  • Set up the project file structure.
Create your project folder and virtual environment

A virtual environment gives your project its own isolated set of Python packages. This prevents conflicts between different projects on your machine.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Visual Studio Code and press Enter to open it.
  • Open the integrated terminal by pressing Ctrl+` (Control + backtick) on both macOS and Windows.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir sales-dashboard
cd sales-dashboard
  • Confirm the folder was created by running:
ls

You should see an empty listing since the folder is brand new.

  • Create a virtual environment by running this command:
python3 -m venv venv

What does this command do?

  • python3 -m venv tells Python to use the built-in venv module to create a virtual environment.
  • The second venv is the folder name where the environment files are stored.
  • Activate the virtual environment so that any packages you install go into this project only:

macOS

  • Run this command to activate:
source venv/bin/activate

Windows

  • Run this command to activate:
venv\Scripts\activate

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

Don't see (venv) in your prompt?

  • Make sure you ran the activation command from inside the sales-dashboard folder (where the venv folder lives).
  • On Windows PowerShell, you may need to allow scripts first. Run Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser then try activating again.
  • help me activate my Python virtual environment
  • Install Streamlit by running this command:
pip install streamlit==1.58.0

What does this command do?

pip install streamlit==1.58.0 downloads and installs Streamlit version 1.58.0 into your virtual environment. Pinning the version ensures your project behaves consistently.

This may take a minute to download all the dependencies. When it finishes, you'll see a success message in the terminal.

  • Verify Streamlit installed correctly by running:
streamlit hello

A new browser tab should open displaying a Streamlit sample app. This confirms everything is working.

  • Return to your terminal and press Ctrl+C to stop the Streamlit server.

Seeing 'command not found' for streamlit?

  • Check that your virtual environment is still active. You should see (venv) in your prompt.
  • If it's not active, re-run the activation command from earlier.
  • help me fix streamlit command not found
Create the project file structure

Your project needs two Python files and a configuration directory. One file will populate the database and the other will be your dashboard app.

  • Create the two Python files and the Streamlit config directory by running these commands:
touch seed_database.py app.py
mkdir .streamlit

What are these files for?

  • seed_database.py will generate and populate your SQLite database with sample sales data.
  • app.py is the Streamlit dashboard where you'll write SQL queries and display visualizations.
  • .streamlit/ is the configuration directory Streamlit looks for when customizing app settings.
  • Confirm the files were created by running:
ls -a

You should see app.py, seed_database.py, .streamlit, and venv in the output.

  • Now open the sales-dashboard folder as your VS Code workspace. Click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop and select the sales-dashboard folder, then click Open.

You should see the project files listed in the Explorer sidebar on the left.

Don't see your files in the Explorer?

  • Make sure you selected the sales-dashboard folder itself, not a parent folder or individual file.
  • If the Explorer panel is hidden, press Cmd+Shift+E (macOS) or Ctrl+Shift+E (Windows) to reveal it.
  • help me open my project folder in VS Code
Verify sqlite3 is available

SQLite ships with Python, so there's nothing to install. You just need to confirm it's accessible.

  • Open the integrated terminal again by pressing Ctrl+` (if it closed when you reopened the folder).
  • Make sure your virtual environment is active. You should see (venv) in the prompt. If not, re-run the activation command from earlier.
  • Confirm sqlite3 is available by running this command:
python3 -c "import sqlite3; print(sqlite3.sqlite_version)"

You should see a version number printed (something like 3.39.5 or similar). The exact number doesn't matter. Any output confirms SQLite is ready.

Seeing 'ModuleNotFoundError'?

  • This is rare since sqlite3 is part of Python's standard library. If you see this error, your Python installation may be incomplete.
  • Try running python3 --version to confirm Python is working. If that fails, reinstall Python from python.org.
  • help me fix sqlite3 not found in Python

Your environment is ready. Next up, you'll write a seed script that creates a database with sales agents and their deals.

Create and Seed the Database

Your environment is set up and ready to go. Now it's time to create the data your dashboard will visualize.

In this step, you'll write a Python script that builds a SQLite database with two related tables. One table stores sales agents and the other stores their deals. The relationship between these tables is what lets you practice JOIN queries later.

In this step, get ready to:
  • Write a seed script that creates two linked database tables.
  • Populate the tables with 8 agents and 120 deals of realistic sample data.
  • Run the script and confirm your database is populated correctly.
Write the seed script that creates two tables

A relational database stores data in tables that link to each other. Your dashboard needs two tables: one for agent profiles and one for the deals those agents closed. The agent_id column in the deals table references the agents table, creating the relationship you'll query with JOINs.

  • In VS Code, open seed_database.py and add the following code to set up the database schema:
import sqlite3
import random
from datetime import date, timedelta

def create_database():
    # Connect to SQLite (creates the file if it doesn't exist)
    conn = sqlite3.connect("sales.db")
    cursor = conn.cursor()

    # Drop existing tables so the script is re-runnable
    cursor.execute("DROP TABLE IF EXISTS deals")
    cursor.execute("DROP TABLE IF EXISTS agents")

    # Create agents table to store sales team profiles
    cursor.execute("""
        CREATE TABLE agents (
            agent_id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            region TEXT NOT NULL,
            hire_date TEXT NOT NULL
        )
    """)

    # Create deals table linked to agents via agent_id
    cursor.execute("""
        CREATE TABLE deals (
            deal_id INTEGER PRIMARY KEY,
            agent_id INTEGER NOT NULL,
            deal_amount REAL NOT NULL,
            deal_date TEXT NOT NULL,
            product_category TEXT NOT NULL,
            FOREIGN KEY (agent_id) REFERENCES agents (agent_id)
        )
    """)

What does this code do?

sqlite3.connect("sales.db") opens a connection to a SQLite database file. If the file doesn't exist yet, SQLite creates it automatically when you first write data.

The DROP TABLE IF EXISTS statements make the script re-runnable. If you run it twice, it wipes the old tables before creating fresh ones.

The FOREIGN KEY on agent_id in the deals table tells SQLite that every deal must reference a valid agent. This is the relationship you'll exploit with JOIN queries in later steps.

  • Save seed_database.py.
  • You won't see a visible change yet. The next chunk adds the sample data that brings these tables to life.
Populate with realistic sample data

Empty tables aren't useful for a dashboard. You need enough data to create meaningful charts and metrics. This chunk inserts 8 sales agents across 4 regions and generates 120 deals with varying sizes across 4 product categories.

  • In seed_database.py, add the following code directly below the last CREATE TABLE block (still inside the create_database() function):
    # 8 agents spread across 4 regions
    agents = [
        (1, "Alice Johnson", "North", "2022-03-15"),
        (2, "Bob Martinez", "South", "2021-07-01"),
        (3, "Carol Williams", "East", "2023-01-10"),
        (4, "David Kim", "West", "2022-11-20"),
        (5, "Eva Chen", "North", "2023-06-05"),
        (6, "Frank Brown", "South", "2021-09-12"),
        (7, "Grace Lee", "East", "2022-05-30"),
        (8, "Henry Wilson", "West", "2023-03-18"),
    ]

    cursor.executemany(
        "INSERT INTO agents (agent_id, name, region, hire_date) VALUES (?, ?, ?, ?)",
        agents
    )

    # Generate 120 deals with varying amounts by category
    categories = ["Software", "Hardware", "Services", "Support"]
    deals = []
    random.seed(42)  # Fixed seed so every learner gets the same data

    start_date = date(2024, 1, 1)
    end_date = date(2024, 6, 30)
    days_range = (end_date - start_date).days

    for deal_id in range(1, 121):
        agent_id = random.randint(1, 8)
        category = random.choice(categories)

        # Different categories have different typical deal sizes
        if category == "Software":
            amount = round(random.uniform(5000, 50000), 2)
        elif category == "Hardware":
            amount = round(random.uniform(2000, 30000), 2)
        elif category == "Services":
            amount = round(random.uniform(10000, 80000), 2)
        else:
            amount = round(random.uniform(1000, 15000), 2)

        deal_date = start_date + timedelta(days=random.randint(0, days_range))

        deals.append((deal_id, agent_id, amount, deal_date.isoformat(), category))

    cursor.executemany(
        "INSERT INTO deals (deal_id, agent_id, deal_amount, deal_date, product_category) VALUES (?, ?, ?, ?, ?)",
        deals
    )

    # Save changes and print summary
    conn.commit()

    print("Database created successfully!")
    print(f"Agents: {cursor.execute('SELECT COUNT(*) FROM agents').fetchone()[0]}")
    print(f"Deals: {cursor.execute('SELECT COUNT(*) FROM deals').fetchone()[0]}")

    conn.close()

# Run the function when this file is executed directly
if __name__ == "__main__":
    create_database()

What does this code do?

The agents list defines 8 team members, each with a region (North, South, East, West). The executemany() method inserts all 8 rows in one call using ? placeholders for safe parameterized insertion.

random.seed(42) locks the random number generator to a fixed starting point. This means every time anyone runs this script, they get the exact same 120 deals. That keeps your dashboard output consistent and debuggable.

The loop generates deals with different price ranges per category. Services deals range from $10,000 to $80,000 while Support deals range from $1,000 to $15,000. This creates realistic variation in your charts.

The if __name__ == "__main__" block at the bottom means create_database() only runs when you execute the file directly. It won't run if you import the file from somewhere else.

  • Save seed_database.py. You'll run this script in the next substep to create and populate the database.

Seeing a red underline or indentation error?

Make sure all the code you just added is indented inside the create_database() function (4 spaces in). Only the if __name__ block at the very bottom sits at the top level (no indentation).

help me fix indentation errors in my seed_database.py file

✔️ Awesome, I've got everything!

Great. Double check you've saved seed_database.py before moving on.

ⓧ I'd like to double check the full code

Here's the complete seed_database.py file. Make sure yours matches:

import sqlite3
import random
from datetime import date, timedelta

def create_database():
    conn = sqlite3.connect("sales.db")
    cursor = conn.cursor()

    cursor.execute("DROP TABLE IF EXISTS deals")
    cursor.execute("DROP TABLE IF EXISTS agents")

    cursor.execute("""
        CREATE TABLE agents (
            agent_id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            region TEXT NOT NULL,
            hire_date TEXT NOT NULL
        )
    """)

    cursor.execute("""
        CREATE TABLE deals (
            deal_id INTEGER PRIMARY KEY,
            agent_id INTEGER NOT NULL,
            deal_amount REAL NOT NULL,
            deal_date TEXT NOT NULL,
            product_category TEXT NOT NULL,
            FOREIGN KEY (agent_id) REFERENCES agents (agent_id)
        )
    """)

    agents = [
        (1, "Alice Johnson", "North", "2022-03-15"),
        (2, "Bob Martinez", "South", "2021-07-01"),
        (3, "Carol Williams", "East", "2023-01-10"),
        (4, "David Kim", "West", "2022-11-20"),
        (5, "Eva Chen", "North", "2023-06-05"),
        (6, "Frank Brown", "South", "2021-09-12"),
        (7, "Grace Lee", "East", "2022-05-30"),
        (8, "Henry Wilson", "West", "2023-03-18"),
    ]

    cursor.executemany(
        "INSERT INTO agents (agent_id, name, region, hire_date) VALUES (?, ?, ?, ?)",
        agents
    )

    categories = ["Software", "Hardware", "Services", "Support"]
    deals = []
    random.seed(42)

    start_date = date(2024, 1, 1)
    end_date = date(2024, 6, 30)
    days_range = (end_date - start_date).days

    for deal_id in range(1, 121):
        agent_id = random.randint(1, 8)
        category = random.choice(categories)

        if category == "Software":
            amount = round(random.uniform(5000, 50000), 2)
        elif category == "Hardware":
            amount = round(random.uniform(2000, 30000), 2)
        elif category == "Services":
            amount = round(random.uniform(10000, 80000), 2)
        else:
            amount = round(random.uniform(1000, 15000), 2)

        deal_date = start_date + timedelta(days=random.randint(0, days_range))

        deals.append((deal_id, agent_id, amount, deal_date.isoformat(), category))

    cursor.executemany(
        "INSERT INTO deals (deal_id, agent_id, deal_amount, deal_date, product_category) VALUES (?, ?, ?, ?, ?)",
        deals
    )

    conn.commit()

    print("Database created successfully!")
    print(f"Agents: {cursor.execute('SELECT COUNT(*) FROM agents').fetchone()[0]}")
    print(f"Deals: {cursor.execute('SELECT COUNT(*) FROM deals').fetchone()[0]}")

    conn.close()

if __name__ == "__main__":
    create_database()
Run the seed script and verify

The script is complete. Now run it to create the sales.db file and populate it with your sample data.

  • Run the seed script by executing this command in your terminal:
python3 seed_database.py
  • You should see this output:
Database created successfully!
Agents: 8
Deals: 120
  • Confirm the sales.db file now exists by running:
ls
  • You should see sales.db listed alongside seed_database.py and app.py in the output.

Seeing a ModuleNotFoundError or SyntaxError?

If you see a SyntaxError, check your indentation. Every line inside create_database() must be indented by 4 spaces. The if __name__ line must have zero indentation.

If the script runs but you don't see sales.db in your ls output, make sure your terminal is in the sales-dashboard folder. Run pwd to check your current location.

help me debug my seed_database.py script that won't run

Your database is ready with realistic sales data. Next up, you'll connect to this database from a Streamlit app and write SQL queries that power live dashboard metrics.

Build the Dashboard Layout with KPI Metrics

Your database is seeded and ready to query. Now it's time to build the actual dashboard that pulls insights out of that data using SQL.

In this step, you'll create a Streamlit app that connects to your SQLite database and displays three key performance metrics. Every number on screen will come from a SQL query you write yourself.

In this step, get ready to:
  • Set up the Streamlit app structure with a title and database connection.
  • Write a SQL query using SUM, COUNT, and AVG to calculate KPI metrics.
  • Run the app and see your first SQL-powered dashboard in the browser.
Set up the Streamlit app structure

Every Streamlit app is just a Python script that runs top-to-bottom. You need to import your libraries, set a page title, and open a connection to your database.

  • In VS Code, open app.py and add the following code:
import streamlit as st
import sqlite3
import pandas as pd

# Set the dashboard title and subtitle
st.title("Sales Agent Performance Dashboard")
st.markdown("Every metric and chart below is powered by a SQL query.")

# Connect to the SQLite database
conn = sqlite3.connect("sales.db")

What does this code do?

The three imports bring in everything you need: streamlit for building the UI, sqlite3 for connecting to your database, and pandas for running SQL queries and getting results as DataFrames.

sqlite3.connect("sales.db") opens a connection to the database file you created in the previous step. This connection object is what you'll pass to pandas whenever you want to run a query.

Why sqlite3 instead of Streamlit's st.connection?

Streamlit offers a built-in st.connection feature for database access, but it abstracts the SQL behind SQLAlchemy. Using sqlite3 directly with pandas.read_sql_query() keeps every raw SQL query front-and-center so you get maximum practice writing SQL yourself.

  • Save app.py.

You won't see a change yet. The next chunk adds the SQL query that powers your first metrics.

Write SQL queries for KPI metrics and display them

KPI (Key Performance Indicator) cards give a quick snapshot of performance. You'll write a single SQL query that calculates three aggregate values from your deals table.

  • In app.py, add the following code below the conn = sqlite3.connect("sales.db") line:
# --- KPI Metrics ---
st.header("Key Metrics")

# SQL query using aggregate functions to calculate KPIs
kpi_query = """
    SELECT
        SUM(d.deal_amount) as total_revenue,
        COUNT(*) as total_deals,
        AVG(d.deal_amount) as avg_deal_size
    FROM deals d
    JOIN agents a ON d.agent_id = a.agent_id
"""

# Run the query and get results as a DataFrame
kpi_data = pd.read_sql_query(kpi_query, conn)

# Display three metric cards side by side
col1, col2, col3 = st.columns(3)
col1.metric("Total Revenue", f"${kpi_data['total_revenue'][0]:,.0f}")
col2.metric("Total Deals", f"{kpi_data['total_deals'][0]}")
col3.metric("Avg Deal Size", f"${kpi_data['avg_deal_size'][0]:,.0f}")

conn.close()

What does this SQL query do?

This single query uses three aggregate functions to calculate KPIs from all 120 deals:

  • SUM(d.deal_amount) adds up every deal amount to get total revenue.
  • COUNT(*) counts the total number of deals.
  • AVG(d.deal_amount) calculates the average deal size.

The JOIN connects the deals table to the agents table using agent_id. You don't need the agent data yet, but adding the JOIN now makes it easy to add filters by region later.

pd.read_sql_query() sends the SQL to your database and returns the results as a pandas DataFrame. Then st.columns(3) creates three side-by-side columns, and st.metric() displays each value as a large, formatted card.

  • Save app.py.

Seeing a red error about the query?

Make sure the triple quotes """ open and close your SQL string correctly. A missing closing set of triple quotes is the most common cause.

Also check that sales.db is in the same folder as app.py. If not, re-run python3 seed_database.py from within the sales-dashboard folder.

Help me debug my SQL query in app.py

✔️ Awesome, I've got everything!

Great. Make sure you've saved your file before moving on to run the app.

ⓧ I'd like to double check the full code

Here's the complete app.py so far:

import streamlit as st
import sqlite3
import pandas as pd

st.title("Sales Agent Performance Dashboard")
st.markdown("Every metric and chart below is powered by a SQL query.")

conn = sqlite3.connect("sales.db")

# --- KPI Metrics ---
st.header("Key Metrics")

kpi_query = """
    SELECT
        SUM(d.deal_amount) as total_revenue,
        COUNT(*) as total_deals,
        AVG(d.deal_amount) as avg_deal_size
    FROM deals d
    JOIN agents a ON d.agent_id = a.agent_id
"""

kpi_data = pd.read_sql_query(kpi_query, conn)

col1, col2, col3 = st.columns(3)
col1.metric("Total Revenue", f"${kpi_data['total_revenue'][0]:,.0f}")
col2.metric("Total Deals", f"{kpi_data['total_deals'][0]}")
col3.metric("Avg Deal Size", f"${kpi_data['avg_deal_size'][0]:,.0f}")

conn.close()
Run the app and see your first SQL-powered metrics
  • In your terminal, launch the Streamlit app by running:
streamlit run app.py

Streamlit starts a local web server and opens your browser automatically to http://localhost:8501.

  • Verify you can see three metric cards at the top of the page: Total Revenue, Total Deals, and Avg Deal Size.

What should I see?

You should see the title "Sales Agent Performance Dashboard" at the top, followed by a "Key Metrics" header and three large metric cards side by side. The exact dollar amounts depend on the random seed data, but all three cards should display formatted numbers.

App not opening in your browser?

If the browser doesn't open automatically, copy the URL shown in your terminal (usually http://localhost:8501) and paste it into your browser manually.

Make sure your virtual environment is still active. If you see a "module not found" error, re-activate it with source venv/bin/activate and try again.

Help me fix my Streamlit app that won't start

Keep Streamlit running

Leave the Streamlit server running in your terminal. Every time you save app.py, Streamlit detects the change and offers to rerun the app. Click Always rerun in the browser to see updates automatically.

Your dashboard is live and pulling real numbers from your database. Next up, you'll add interactive filters that dynamically modify the SQL queries based on user selections.

Add Interactive Filters That Modify Your SQL

Your dashboard displays total revenue, deal count, and average deal size. Those metrics cover the entire dataset right now.

In a real sales environment, a manager wants to slice data by region or product category. In this step, you will add sidebar filters that dynamically modify your SQL queries so the KPI metrics update instantly when a filter changes.

In this step, get ready to:
  • Query the database for filter options using SELECT DISTINCT.
  • Build a dynamic WHERE clause from filter selections using parameterized queries.
  • Connect the filters to your KPI query so metrics update when filters change.
Query the database for filter options

Instead of hardcoding filter options, you will query the database to find out what regions and product categories actually exist. This way, if the data changes, your filters stay accurate.

  • In app.py, find the line that says # --- KPI Metrics ---.
  • Add the following code directly above that line (between conn = sqlite3.connect("sales.db") and the KPI section):
# --- Sidebar Filters ---
st.sidebar.header("Filters")

regions = pd.read_sql_query("SELECT DISTINCT region FROM agents ORDER BY region", conn)
selected_region = st.sidebar.selectbox(
    "Region",
    options=["All"] + regions["region"].tolist()
)

categories = pd.read_sql_query(
    "SELECT DISTINCT product_category FROM deals ORDER BY product_category", conn
)
selected_categories = st.sidebar.multiselect(
    "Product Categories",
    options=categories["product_category"].tolist(),
    default=categories["product_category"].tolist()
)

What does this code do?

SELECT DISTINCT region FROM agents returns one row per unique region in your database. This gives you the dropdown options without hardcoding them.

st.sidebar.selectbox() creates a single-choice dropdown. The "All" option is prepended so the learner can view all regions at once.

st.sidebar.multiselect() creates a multi-choice picker. The default parameter pre-selects all categories so the dashboard starts with all data visible.

  • Save app.py.
  • Switch to your browser. Streamlit auto-detects file changes and shows a "Rerun" button in the top-right corner. Click Rerun.
  • You should see a new sidebar on the left with a Region dropdown (showing "All") and a Product Categories multiselect (showing all four categories selected).

Don't see the sidebar?

  • Make sure you saved app.py and clicked Rerun in the browser (or enable "Always rerun" from the menu).
  • Check that your new code sits between the conn = sqlite3.connect("sales.db") line and the # --- KPI Metrics --- comment. Order matters in Streamlit.
  • If you see a "no such table: agents" error, make sure sales.db is in the same folder as app.py.

Help me debug why my Streamlit sidebar filters aren't appearing.

Build a dynamic WHERE clause from filter selections

The filters exist in the sidebar, but they are not connected to any query yet. You need logic that translates the selected values into a SQL WHERE clause.

  • In app.py, add the following code directly below the selected_categories multiselect block you just wrote, and above # --- KPI Metrics ---:
# --- Build WHERE clause ---
where_conditions = []
params = []

if selected_region != "All":
    where_conditions.append("a.region = ?")
    params.append(selected_region)

if selected_categories:
    placeholders = ", ".join(["?"] * len(selected_categories))
    where_conditions.append(f"d.product_category IN ({placeholders})")
    params.extend(selected_categories)

where_clause = ""
if where_conditions:
    where_clause = "WHERE " + " AND ".join(where_conditions)

What does this code do?

This block builds a SQL WHERE clause dynamically based on what the user selects in the sidebar.

  • where_conditions collects individual conditions (like a.region = ?) as strings.
  • params holds the actual values to substitute into the ? placeholders. This is called a parameterized query. It keeps your SQL safe from injection attacks.
  • If no filters are active, where_clause stays as an empty string. This means the query returns all rows.

Why use parameterized queries instead of f-strings for values?

You could build the WHERE clause by dropping values directly into the SQL string with an f-string. But that opens the door to SQL injection, where a crafted input could break or manipulate your query.

The ? placeholder tells SQLite to treat each value as data, never as SQL syntax. The database engine handles escaping for you.

You won't see a change yet. The next edit connects this WHERE clause to your KPI query.

Connect the filters to your KPI query

Now you need to inject where_clause and params into the existing KPI query. This makes the metrics respond to filter changes.

  • In app.py, scroll down to the KPI section and find this line:
kpi_query = """
    SELECT
        SUM(d.deal_amount) as total_revenue,
        COUNT(*) as total_deals,
        AVG(d.deal_amount) as avg_deal_size
    FROM deals d
    JOIN agents a ON d.agent_id = a.agent_id
"""
  • Replace that entire block with this updated version that uses an f-string and inserts the where_clause:
kpi_query = f"""
    SELECT
        SUM(d.deal_amount) as total_revenue,
        COUNT(*) as total_deals,
        AVG(d.deal_amount) as avg_deal_size
    FROM deals d
    JOIN agents a ON d.agent_id = a.agent_id
    {where_clause}
"""
  • Next, find the line directly below the query:
kpi_data = pd.read_sql_query(kpi_query, conn)
  • Replace it with this version that passes params to the query:
kpi_data = pd.read_sql_query(kpi_query, conn, params=params)

What changed?

  • The f prefix before the triple quotes turns the query string into an f-string. This lets Python insert the {where_clause} variable directly into the SQL text.
  • params=params tells pandas to substitute the ? placeholders in the WHERE clause with the actual filter values.
  • Save app.py and click Rerun in your browser.
  • Change the Region dropdown from "All" to "North". The Total Revenue, Total Deals, and Avg Deal Size numbers should drop because you are now viewing only deals from North-region agents.
  • Remove one or two categories from the Product Categories multiselect. Confirm the metrics change again.

Metrics not changing when you pick a filter?

  • Confirm you changed """ to f""" on the kpi_query line. Without the f prefix, Python treats {where_clause} as literal text.
  • Make sure you added params=params to the pd.read_sql_query() call. Without it, the ? placeholders have no values to substitute.
  • Check that the WHERE clause builder code sits above the KPI section. If it is below, the where_clause variable does not exist yet when the query runs.

Help me debug why my Streamlit filters don't update the KPI metrics.

✔️ Awesome, I've got everything!

Great. Double check you have saved app.py and that changing filters in the sidebar updates your KPI cards.

ⓧ I'd like to double check the full code

Here is the complete app.py at this point:

import streamlit as st
import sqlite3
import pandas as pd

st.title("Sales Agent Performance Dashboard")
st.markdown("Every metric and chart below is powered by a SQL query.")

conn = sqlite3.connect("sales.db")

# --- Sidebar Filters ---
st.sidebar.header("Filters")

regions = pd.read_sql_query("SELECT DISTINCT region FROM agents ORDER BY region", conn)
selected_region = st.sidebar.selectbox(
    "Region",
    options=["All"] + regions["region"].tolist()
)

categories = pd.read_sql_query(
    "SELECT DISTINCT product_category FROM deals ORDER BY product_category", conn
)
selected_categories = st.sidebar.multiselect(
    "Product Categories",
    options=categories["product_category"].tolist(),
    default=categories["product_category"].tolist()
)

# --- Build WHERE clause ---
where_conditions = []
params = []

if selected_region != "All":
    where_conditions.append("a.region = ?")
    params.append(selected_region)

if selected_categories:
    placeholders = ", ".join(["?"] * len(selected_categories))
    where_conditions.append(f"d.product_category IN ({placeholders})")
    params.extend(selected_categories)

where_clause = ""
if where_conditions:
    where_clause = "WHERE " + " AND ".join(where_conditions)

# --- KPI Metrics ---
st.header("Key Metrics")

kpi_query = f"""
    SELECT
        SUM(d.deal_amount) as total_revenue,
        COUNT(*) as total_deals,
        AVG(d.deal_amount) as avg_deal_size
    FROM deals d
    JOIN agents a ON d.agent_id = a.agent_id
    {where_clause}
"""

kpi_data = pd.read_sql_query(kpi_query, conn, params=params)

col1, col2, col3 = st.columns(3)
col1.metric("Total Revenue", f"${kpi_data['total_revenue'][0]:,.0f}")
col2.metric("Total Deals", f"{kpi_data['total_deals'][0]}")
col3.metric("Avg Deal Size", f"${kpi_data['avg_deal_size'][0]:,.0f}")

conn.close()

Your dashboard now responds to user input. The same SQL query produces different results depending on what the user selects. Next up, you will add bar charts and line charts powered by GROUP BY queries so the filtered data tells a visual story.

Add Visualizations Powered by SQL

Your filters are wired up and your KPI cards respond to every dropdown change. Now it's time to turn those SQL queries into visual stories.

In this step, you'll write GROUP BY queries that aggregate data by agent and by month, then render the results as charts. You'll also add a sortable table of recent deals so you can drill into the raw data.

In this step, get ready to:
  • Write a GROUP BY query and display revenue per agent as a bar chart.
  • Write a GROUP BY query with date formatting and display monthly trends as a line chart.
  • Query individual deals with a JOIN and display them in an interactive table.
Write a GROUP BY query for revenue by agent

The GROUP BY clause in SQL collapses many rows into one row per group. Paired with SUM(), it gives you a total for each agent.

  • In app.py, find the line that says conn.close() at the bottom of the file.
  • Add the following code directly above conn.close():
# --- Revenue by Agent (Bar Chart) ---
st.header("Revenue by Agent")

agent_query = f"""
    SELECT a.name, SUM(d.deal_amount) as total_revenue
    FROM deals d
    JOIN agents a ON d.agent_id = a.agent_id
    {where_clause}
    GROUP BY a.name
    ORDER BY total_revenue DESC
"""

agent_data = pd.read_sql_query(agent_query, conn, params=params)
agent_data_indexed = agent_data.set_index("name")
st.bar_chart(agent_data_indexed["total_revenue"])

What does this code do?

The SQL query JOINs the deals and agents tables so you can group deals by agent name. GROUP BY a.name collapses all of an agent's deals into one row, and SUM(d.deal_amount) totals their revenue.

The ORDER BY total_revenue DESC sorts agents from highest earner to lowest. Setting the agent name as the DataFrame index lets st.bar_chart() use it as the x-axis labels automatically.

  • Save app.py.
  • Switch to your browser. Streamlit should auto-reload. You should see a bar chart below your KPI cards showing each agent's total revenue.

Don't see the bar chart?

  • Make sure you pasted the new code above conn.close(). If the connection is closed before the query runs, you'll get an error.
  • Check that the where_clause variable inside the f-string is wrapped in curly braces: {where_clause}.

Why isn't my bar chart showing up in Streamlit?

Write a GROUP BY query for monthly revenue trends

Grouping by month reveals trends over time. SQLite's strftime() function formats dates into year-month strings so you can group deals by calendar month.

  • In app.py, add the following code directly below the bar chart section you just added (still above conn.close()):
# --- Monthly Revenue Trend (Line Chart) ---
st.header("Monthly Revenue Trend")

monthly_query = f"""
    SELECT strftime('%Y-%m', d.deal_date) as month, SUM(d.deal_amount) as revenue
    FROM deals d
    JOIN agents a ON d.agent_id = a.agent_id
    {where_clause}
    GROUP BY month
    ORDER BY month
"""

monthly_data = pd.read_sql_query(monthly_query, conn, params=params)
monthly_data_indexed = monthly_data.set_index("month")
st.line_chart(monthly_data_indexed["revenue"])

What does this code do?

The key here is strftime('%Y-%m', d.deal_date). This is a SQLite-specific function that converts a full date like 2024-03-15 into 2024-03. Grouping by this formatted string gives you one row per calendar month.

The ORDER BY month ensures the line chart draws left to right in chronological order. Without it, months could appear in any order and the trend line would zigzag randomly.

  • Save app.py.
  • Switch to your browser. Below the bar chart, you should now see a line chart showing revenue per month from January to June 2024.

Line chart showing no data?

  • Make sure the strftime format string uses single quotes inside the SQL: '%Y-%m'. Double quotes will cause a SQL syntax error.
  • Confirm that your sidebar category filter still has at least one category selected. If all categories are deselected, the query returns zero rows.

Help me fix my monthly revenue line chart query.

Add a raw data table showing individual deals

Charts summarize patterns, but sometimes you need to see the actual rows. A table of recent deals lets you verify the numbers behind the charts and spot individual outliers.

  • In app.py, add the following code below the line chart section (still above conn.close()):
# --- Recent Deals Table ---
st.header("Recent Deals")

deals_query = f"""
    SELECT a.name as Agent, d.deal_amount as Amount,
           d.deal_date as Date, d.product_category as Category
    FROM deals d
    JOIN agents a ON d.agent_id = a.agent_id
    {where_clause}
    ORDER BY d.deal_date DESC
    LIMIT 20
"""

deals_data = pd.read_sql_query(deals_query, conn, params=params)
st.dataframe(deals_data, use_container_width=True)

What does this code do?

This query uses column aliases (a.name as Agent) to give the table friendly column headers. The LIMIT 20 keeps the table manageable by showing only the 20 most recent deals.

The st.dataframe() widget renders an interactive table that learners can sort by clicking column headers. use_container_width=True stretches the table to fill the page width.

  • Save app.py.
  • Switch to your browser. Below the line chart, you should see a table with columns for Agent, Amount, Date, and Category.
  • Try clicking a column header in the table to sort it. Try changing the Region filter in the sidebar and confirm all three visualizations update together.

Table not showing or columns look wrong?

  • Check that your SQL aliases match: a.name as Agent, d.deal_amount as Amount, etc. A typo in the alias changes the column header.
  • Make sure conn.close() is still the very last line in the file. All queries must run before the connection closes.

Why is my Streamlit dataframe not displaying correctly?

✔️ Awesome, I've got everything!

Great. Double check you've saved app.py and that all three visualizations (bar chart, line chart, table) appear in your browser.

ⓧ I'd like to double check the full code

import streamlit as st
import sqlite3
import pandas as pd

st.title("Sales Agent Performance Dashboard")
st.markdown("Every metric and chart below is powered by a SQL query.")

conn = sqlite3.connect("sales.db")

# --- Sidebar Filters ---
st.sidebar.header("Filters")

regions = pd.read_sql_query("SELECT DISTINCT region FROM agents ORDER BY region", conn)
selected_region = st.sidebar.selectbox(
    "Region",
    options=["All"] + regions["region"].tolist()
)

categories = pd.read_sql_query(
    "SELECT DISTINCT product_category FROM deals ORDER BY product_category", conn
)
selected_categories = st.sidebar.multiselect(
    "Product Categories",
    options=categories["product_category"].tolist(),
    default=categories["product_category"].tolist()
)

# --- Build WHERE clause ---
where_conditions = []
params = []

if selected_region != "All":
    where_conditions.append("a.region = ?")
    params.append(selected_region)

if selected_categories:
    placeholders = ", ".join(["?"] * len(selected_categories))
    where_conditions.append(f"d.product_category IN ({placeholders})")
    params.extend(selected_categories)

where_clause = ""
if where_conditions:
    where_clause = "WHERE " + " AND ".join(where_conditions)

# --- KPI Metrics ---
st.header("Key Metrics")

kpi_query = f"""
    SELECT
        SUM(d.deal_amount) as total_revenue,
        COUNT(*) as total_deals,
        AVG(d.deal_amount) as avg_deal_size
    FROM deals d
    JOIN agents a ON d.agent_id = a.agent_id
    {where_clause}
"""

kpi_data = pd.read_sql_query(kpi_query, conn, params=params)

col1, col2, col3 = st.columns(3)
col1.metric("Total Revenue", f"${kpi_data['total_revenue'][0]:,.0f}")
col2.metric("Total Deals", f"{kpi_data['total_deals'][0]}")
col3.metric("Avg Deal Size", f"${kpi_data['avg_deal_size'][0]:,.0f}")

# --- Revenue by Agent (Bar Chart) ---
st.header("Revenue by Agent")

agent_query = f"""
    SELECT a.name, SUM(d.deal_amount) as total_revenue
    FROM deals d
    JOIN agents a ON d.agent_id = a.agent_id
    {where_clause}
    GROUP BY a.name
    ORDER BY total_revenue DESC
"""

agent_data = pd.read_sql_query(agent_query, conn, params=params)
agent_data_indexed = agent_data.set_index("name")
st.bar_chart(agent_data_indexed["total_revenue"])

# --- Monthly Revenue Trend (Line Chart) ---
st.header("Monthly Revenue Trend")

monthly_query = f"""
    SELECT strftime('%Y-%m', d.deal_date) as month, SUM(d.deal_amount) as revenue
    FROM deals d
    JOIN agents a ON d.agent_id = a.agent_id
    {where_clause}
    GROUP BY month
    ORDER BY month
"""

monthly_data = pd.read_sql_query(monthly_query, conn, params=params)
monthly_data_indexed = monthly_data.set_index("month")
st.line_chart(monthly_data_indexed["revenue"])

# --- Recent Deals Table ---
st.header("Recent Deals")

deals_query = f"""
    SELECT a.name as Agent, d.deal_amount as Amount,
           d.deal_date as Date, d.product_category as Category
    FROM deals d
    JOIN agents a ON d.agent_id = a.agent_id
    {where_clause}
    ORDER BY d.deal_date DESC
    LIMIT 20
"""

deals_data = pd.read_sql_query(deals_query, conn, params=params)
st.dataframe(deals_data, use_container_width=True)

conn.close()

Your dashboard is fully built. Every chart and metric on screen is driven by a SQL query you wrote yourself. Next up, the Secret Mission challenges you to write even more advanced SQL with subqueries and rankings.

Secret mission

Add a Top Performers Leaderboard

Your dashboard shows great high-level metrics, but the sales team wants to know who their star players are. Build a ranked leaderboard that calculates each agent's total revenue, deal count, and percentage contribution to company revenue using advanced SQL with subqueries.

Clean Up Your Resources

Clean Up Your Resources

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

Resources you used:

  • The sales-dashboard project folder on your Desktop (contains app.py, seed_database.py, sales.db, and the venv virtual environment).
  • The running Streamlit dev server process in your terminal.

Keep everything running

No action needed. Choose this if you're still actively building or want to keep experimenting with your SQL queries and dashboard.

  • Your Streamlit app continues running at the local URL shown in your terminal.
  • You can edit app.py and the dashboard will automatically reload in your browser.
  • Your sales.db database stays on disk and can be queried anytime.

Pause - I'll come back to this later

Shut down the running Streamlit process to free up your terminal. Your project files stay on disk so you can restart anytime.

  • In the terminal where Streamlit is running, press Ctrl+C to stop the server.
  • Deactivate your virtual environment by running:
deactivate

To resume later, open your terminal, navigate back to the sales-dashboard folder, reactivate the virtual environment, and run the app again.

Delete - I don't want to use this again

Remove all project files, the database, and the virtual environment. This frees up disk space completely.

  • In the terminal where Streamlit is running, press Ctrl+C to stop the server.
  • Deactivate your virtual environment by running:
deactivate
  • Navigate to your Desktop and delete the entire project folder by running:

macOS

cd ~/Desktop
rm -rf sales-dashboard

Windows

cd ~\Desktop
rmdir /s /q sales-dashboard
  • Confirm the folder is gone by running ls (macOS) or dir (Windows). You should no longer see sales-dashboard in the output.

Nice Work!

Nice Work!

You just built a fully interactive sales performance dashboard powered entirely by SQL queries you wrote yourself.

You've learned how to:

  • Created a SQLite database with two related tables and wrote SQL queries using SELECT, WHERE, JOIN, GROUP BY, ORDER BY, and aggregate functions (SUM, AVG, COUNT) to extract meaningful insights from relational data.
  • Connected a Streamlit app to a live database and used interactive widgets (selectbox, multiselect) to dynamically modify SQL WHERE clauses and filter results in real time.
  • Visualized SQL query results as bar charts, line charts, and metric cards to turn raw data into a professional-looking dashboard.
  • Secret Mission: Built a ranked Top Performers Leaderboard using advanced SQL with subqueries, correlated queries, and multiple aggregations to calculate revenue share percentages and each agent's best month.

Ready to quiz yourself?