Build a Retail Sales Dashboard

Build a returns-aware retail dashboard with PostgreSQL and Power BI.

Introduction

30 Second Summary

A busy sales month can look successful until refunds reveal that some revenue never stayed in the business. Without one clear view, a store manager has to guess which products deserve attention.

In this project, you will build a local retail analytics system with PostgreSQL and Microsoft Power BI Desktop. You will use returns-aware SQL to turn raw transactions into an interactive report for day-to-day retail decisions.

What You'll Build

Picture changing a store or category filter and watching the report reveal the sales movement that needs a manager's attention.

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

  • A 12-month sales view where you can compare gross revenue with net revenue after refunds.
  • A manager's decision view that surfaces product performance, customer value, category movement, purchase recency, average order value, and return rate.
  • An interactive Power BI report that responds to month, store, and category filters. You can save it locally as Retail_Sales_Analytics.pbix for demonstrations.
  • Secret Mission: A customer re-engagement view that segments customers into Active, Watchlist, and At Risk groups.

Are there any prerequisites?

You need a supported 64-bit Windows computer. The guide covers setup for PostgreSQL, pgAdmin, and Microsoft Power BI Desktop.

Before We Start

Before hands-on work begins, commit to building a returns-aware retail sales and customer insights dashboard backed by PostgreSQL. Power BI Desktop will turn its results into visuals that help a store manager choose the business action that deserves attention first.

Install and Verify the Analytics Stack

A trustworthy dashboard needs a dependable place to store its data. Your local relational database will provide that foundation for every retail question in this project.

This step sets up PostgreSQL for data storage. You will use pgAdmin to run queries before presenting the results in Power BI Desktop.

In this step, get ready to:
  • Install PostgreSQL 18.6 with pgAdmin 4 v9.18.
  • Install Microsoft Power BI Desktop 2.157.1354.0 for local report authoring.
  • Run a SQL check that confirms your local database server responds.
Install PostgreSQL and pgAdmin

PostgreSQL runs the local database server. pgAdmin gives you a visual workspace for connecting to that server and running queries.

Why PostgreSQL for this project?

PostgreSQL gives you experience with a client-server database. You will work with credentials and relational constraints in a workflow that resembles an analyst's workplace.

SQLite offers a lighter setup. PostgreSQL adds the database administration layer that this project is designed to teach.

  • Press the Windows key to open Windows search.
  • Type Settings into the search box.
  • Press Enter to open Settings.
  • Select Apps.
  • Select Installed apps.
  • Search for PostgreSQL.
  • Search for pgAdmin 4.

✔️ I see the required versions

Keep PostgreSQL 18.6 and pgAdmin 4 v9.18 installed. You can continue with the version check below.

ⓧ I see older versions

The project uses PostgreSQL 18.6 and pgAdmin 4 v9.18. The current Windows installer provides both tools.

Windows may request administrator permission when the installer starts. This setup can take several minutes while the database server is installed.

  • Download the PostgreSQL 18.6 Windows interactive installer from the official PostgreSQL download page.
  • Run the downloaded installer with administrator privileges.
  • Continue to Select Components.

The component selection controls which parts of the local database stack are available after setup.

  • Keep PostgreSQL Server selected.
  • Keep pgAdmin 4 selected.
  • Keep Command Line Tools selected.
  • Clear Stack Builder because this project does not require optional add-ons.

The database password protects your local server. Keep it private because you will use it when connecting from pgAdmin and Power BI Desktop.

  • Choose a database password in the Password field.
  • Store the database password in your password manager.
  • Keep 5432 in the Port field.

The installer now has the components and connection settings needed for this project.

  • Complete the installation.

ⓧ They are not installed

The PostgreSQL Windows interactive installer includes the database server and pgAdmin. One guided installation creates the local database stack.

Windows may request administrator permission when the installer starts. This setup can take several minutes while the database server is installed.

  • Download the PostgreSQL 18.6 Windows interactive installer from the official PostgreSQL download page.
  • Run the downloaded installer with administrator privileges.
  • Continue to Select Components.

The component selection controls which parts of the local database stack are available after setup.

  • Keep PostgreSQL Server selected.
  • Keep pgAdmin 4 selected.
  • Keep Command Line Tools selected.
  • Clear Stack Builder because this project does not require optional add-ons.

The database password protects your local server. Keep it private because you will use it when connecting from pgAdmin and Power BI Desktop.

  • Choose a database password in the Password field.
  • Store the database password in your password manager.
  • Keep 5432 in the Port field.

The installer now has the components and connection settings needed for this project.

  • Complete the installation.
  • Press the Windows key to open Windows search.
  • Type pgAdmin 4 into the search box.
  • Press Enter to open pgAdmin.
  • Select Help from the menu bar.
  • Select About pgAdmin 4.

You should see pgAdmin 4 version v9.18 in the About window. This confirms the database administration tool is ready.

Installation Not Completing?

  • Rerun the PostgreSQL installer with administrator privileges.
  • Confirm your internet connection can complete the installer download.
  • Restart Windows if the installer reports that files are still in use.

Ask for targeted help with the installer error: Help me diagnose why the PostgreSQL Windows installer or pgAdmin setup is failing.

Install Microsoft Power BI Desktop

Power BI Desktop turns query results into charts and interactive filters. This project keeps the report on your computer.

  • Press the Windows key to open Windows search.
  • Type Power BI Desktop into the search box.
  • Press Enter if the app appears.
  • Select Help if Power BI Desktop opens.
  • Select About if the Help ribbon is available.
  • Check the Version line if the About window opens.

✔️ I see the required version

Power BI Desktop 2.157.1354.0 is ready. Keep the application installed.

ⓧ I see an older version

Install the pinned desktop build so the report instructions match your interface. The installer updates the local desktop application.

The installation can take a few minutes. A quiet installer window during this period does not mean the setup has stopped.

ⓧ It is not installed

Install the 64-bit desktop application for local report authoring. This path does not require a cloud subscription.

The installation can take a few minutes. A quiet installer window during this period does not mean the setup has stopped.

  • Press the Windows key to open Windows search.
  • Type Power BI Desktop into the search box.
  • Press Enter to launch Power BI Desktop.
  • Dismiss any sign-in request.
  • Start a blank local report.
  • Select Help from the ribbon.
  • Select About.
  • Confirm the Version line shows 2.157.1354.0.

You should now see a blank report canvas. Leave this local report open for the final stack check.

Why Stay Local?

Power BI Desktop supports free local report authoring. This project does not publish to the Power BI Service.

Keeping the workflow local removes account and licensing requirements. Your database connection and report file remain on your Windows computer.

Power BI Desktop Not Opening?

  • Confirm that you selected the 64-bit installer named PBIDesktopSetup_x64.exe.
  • Restart Windows if setup completed but the application does not appear in search.
  • Rerun the installer if Windows reports an incomplete installation.

Ask for help based on the screen you see: Help me troubleshoot why Power BI Desktop is not installing or opening on Windows.

Connect and test pgAdmin

Installing the tools proves their files exist. A successful query proves pgAdmin can reach the PostgreSQL server and execute SQL.

  • Return to pgAdmin from earlier.
  • Expand Servers in the Object Explorer.

✔️ I see the local server

  • Select the local PostgreSQL server entry.
  • Enter your installation password if pgAdmin requests it.
  • Expand Databases.
  • Select the postgres maintenance database.

The postgres maintenance database should remain selected in Object Explorer.

ⓧ No local server is listed

  • Right-click Servers in Object Explorer.
  • Select Register.
  • Select Server.

The registration dialog stores the address and login details that pgAdmin uses for this local server.

  • Enter Local PostgreSQL as the registration name.
  • Select the Connection tab.
  • Enter localhost in Host name/address.
  • Enter 5432 in Port.

These values point pgAdmin to the PostgreSQL server running on your own computer.

  • Enter postgres in Maintenance database.
  • Enter postgres in Username.

The registration now targets the default maintenance database with the administrator account created during installation.

  • Enter your installation password in Password.
  • Save the server registration.

You should now see the local server beneath Servers in Object Explorer.

  • Expand the registered server beneath Servers.
  • Select the postgres database beneath Databases.
  • Select Tools from the menu bar.
  • Select Query Tool.

Before you run the check, predict the value PostgreSQL will return.

  • Test the local server by entering this query in the SQL Editor:
SELECT 2+2;

What Does This Check Prove?

PostgreSQL evaluates the arithmetic in the SQL Editor. A result in Data Output proves pgAdmin reached the server.

This tiny query also checks that the selected database accepts SQL. It gives you a clean baseline before the retail database is created.

  • Run the query by selecting Execute script.

You should see 4 in Data Output. That result confirms the local PostgreSQL connection works.

No Result in Data Output?

  • Confirm that the postgres maintenance database is selected.
  • Confirm that the server connection uses localhost.
  • Confirm that the connection uses port 5432.
  • Reconnect with the database password from the PostgreSQL installation.

Ask for help with the connection state: Help me troubleshoot why SELECT 2+2 is not returning 4 in pgAdmin Data Output.

  • Return to Power BI Desktop from earlier.
  • Confirm that the blank local report canvas remains open.

Your local analytics stack is working. PostgreSQL can answer queries while Power BI Desktop is ready to turn future results into a report.

Your database and reporting tools are ready. Next, you will create the retail database and expose the first misleading sales trend.

Build the Retail Database

Your local analytics tools are ready. A trustworthy dashboard now needs a relational database that keeps customers, orders, products, payments, and returns connected without duplicating business data.

This step builds a normalized PostgreSQL database with repeatable transactions. You will then test a simple monthly sales metric against the business story.

The first trend is intentionally simple. Its result gives you evidence for deciding whether the sales definition is trustworthy.

In this step, get ready to:
  • Create the local retail_analytics database.
  • Populate eight connected tables with deterministic retail data.
  • Run a 12-month gross-sales analysis and identify its limitation.
Create the project database

Your existing postgres maintenance database provides a safe place to create the separate retail_analytics database. Keeping the project in its own database gives every later table and view a clear home.

  • Switch back to pgAdmin from earlier.
  • Return to the Query Tool tab connected to postgres.
  • Click Open File in the Query Tool toolbar.
  • Double-click 00_create_database.sql in the project files.
  • Click Execute script in the Query Tool toolbar.

The script completes successfully. Your local server now contains the new project database.

Why Use a Separate Database?

A PostgreSQL server can host multiple databases. The retail_analytics database keeps this project's tables separate from the maintenance database and any future projects.

  • Right-click Databases in the Object Explorer.
  • Select Refresh from the context menu.

You should see retail_analytics listed beneath Databases. This confirms that the creation script ran against the correct server.

  • Right-click retail_analytics in the Object Explorer.
  • Select Query Tool from the context menu.

The new Query Tool tab is connected to retail_analytics. Every remaining script in this step runs in that database.

Database Missing After Refresh?

Confirm that 00_create_database.sql contains the database name exactly as shown below. Also confirm that the script ran from the Query Tool connected to postgres.

If the database still does not appear, help me diagnose why the PostgreSQL database creation script did not create retail_analytics.

✔️ Awesome, I've got everything!

Your retail_analytics database exists. Keep its Query Tool tab selected for the schema script.

ⓧ I'd like to double check the full code

CREATE DATABASE retail_analytics;

What Does This File Do?

The statement creates one database named retail_analytics on your local PostgreSQL server. The following scripts use that database as the container for the retail model.

Create and populate the relational model

A normalized model stores each business subject once. Primary keys identify individual records. Foreign keys preserve the links between customers, orders, products, payments, and returns.

How Is the Model Organized?

  • The customers, categories, products, and stores tables describe the people and items involved in a sale.
  • The orders and order_items tables separate each purchase from its individual product lines.
  • The payments and returns tables record money received and refunds issued.
  • The generated data stays identical on every run, so later business results remain reproducible.
  • Click Open File in the retail_analytics Query Tool toolbar.
  • Double-click 01_schema_and_data.sql in the project files.

Before you execute the script, which tables do you expect to contain more rows than the others?

  • Click Execute script in the Query Tool toolbar.

The final result appears in Data Output. You should see 20 customers, 8 products, 4 categories, 3 stores, 96 orders, 120 order items, 96 payments, and 8 returns.

That count pattern reflects the model's relationships. One order can contain several order items, while each order in this dataset has one payment.

Counts Do Not Match?

Confirm that the Query Tool is connected to retail_analytics. Running only part of the file can leave later inserts unfinished.

If the script reports an existing table or duplicate record, avoid running it again over a partial setup. Help me recover from a partially executed retail schema script without guessing which statements succeeded.

✔️ Awesome, I've got everything!

Your eight-table retail model is populated. The final validation row confirms that every deterministic dataset count matches the project design.

ⓧ I'd like to double check the full code

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    signup_date DATE NOT NULL
);

CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY,
    category_name VARCHAR(80) UNIQUE NOT NULL
);

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    category_id INTEGER NOT NULL REFERENCES categories(category_id),
    product_name VARCHAR(100) UNIQUE NOT NULL,
    unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price > 0)
);

CREATE TABLE stores (
    store_id INTEGER PRIMARY KEY,
    store_name VARCHAR(100) UNIQUE NOT NULL,
    city VARCHAR(80) NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    store_id INTEGER NOT NULL REFERENCES stores(store_id),
    order_date DATE NOT NULL,
    order_status VARCHAR(30) NOT NULL
);

CREATE TABLE order_items (
    order_item_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(order_id),
    product_id INTEGER NOT NULL REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price > 0)
);

CREATE TABLE payments (
    payment_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id INTEGER UNIQUE NOT NULL REFERENCES orders(order_id),
    payment_date DATE NOT NULL,
    amount NUMERIC(12, 2) NOT NULL CHECK (amount >= 0),
    payment_method VARCHAR(30) NOT NULL,
    payment_status VARCHAR(30) NOT NULL
);

CREATE TABLE returns (
    return_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_item_id BIGINT NOT NULL REFERENCES order_items(order_item_id),
    return_date DATE NOT NULL,
    return_quantity INTEGER NOT NULL CHECK (return_quantity > 0),
    refund_amount NUMERIC(12, 2) NOT NULL CHECK (refund_amount >= 0),
    return_reason VARCHAR(100) NOT NULL
);

INSERT INTO categories (category_id, category_name) VALUES
    (1, 'Electronics'),
    (2, 'Home'),
    (3, 'Apparel'),
    (4, 'Fitness');

INSERT INTO products (product_id, category_id, product_name, unit_price) VALUES
    (1, 1, 'Laptop Stand', 55.00),
    (2, 1, 'Wireless Keyboard', 80.00),
    (3, 2, 'Desk Lamp', 35.00),
    (4, 2, 'Storage Basket', 25.00),
    (5, 3, 'Everyday Shirt', 45.00),
    (6, 3, 'Denim Jeans', 65.00),
    (7, 4, 'Yoga Mat', 40.00),
    (8, 4, 'Resistance Bands', 30.00);

INSERT INTO stores (store_id, store_name, city) VALUES
    (1, 'Downtown Store', 'Seattle'),
    (2, 'Riverside Store', 'Portland'),
    (3, 'Central Store', 'Spokane');

INSERT INTO customers (customer_id, customer_name, email, signup_date) VALUES
    (1, 'Customer 01', 'customer01@retail.example', DATE '2025-01-05'),
    (2, 'Customer 02', 'customer02@retail.example', DATE '2025-01-10'),
    (3, 'Customer 03', 'customer03@retail.example', DATE '2025-01-15'),
    (4, 'Customer 04', 'customer04@retail.example', DATE '2025-01-20'),
    (5, 'Customer 05', 'customer05@retail.example', DATE '2025-01-25'),
    (6, 'Customer 06', 'customer06@retail.example', DATE '2025-01-30'),
    (7, 'Customer 07', 'customer07@retail.example', DATE '2025-02-04'),
    (8, 'Customer 08', 'customer08@retail.example', DATE '2025-02-09'),
    (9, 'Customer 09', 'customer09@retail.example', DATE '2025-02-14'),
    (10, 'Customer 10', 'customer10@retail.example', DATE '2025-02-19'),
    (11, 'Customer 11', 'customer11@retail.example', DATE '2025-02-24'),
    (12, 'Customer 12', 'customer12@retail.example', DATE '2025-03-01'),
    (13, 'Customer 13', 'customer13@retail.example', DATE '2025-03-06'),
    (14, 'Customer 14', 'customer14@retail.example', DATE '2025-03-11'),
    (15, 'Customer 15', 'customer15@retail.example', DATE '2025-03-16'),
    (16, 'Customer 16', 'customer16@retail.example', DATE '2025-03-21'),
    (17, 'Customer 17', 'customer17@retail.example', DATE '2025-03-26'),
    (18, 'Customer 18', 'customer18@retail.example', DATE '2025-03-31'),
    (19, 'Customer 19', 'customer19@retail.example', DATE '2025-04-05'),
    (20, 'Customer 20', 'customer20@retail.example', DATE '2025-04-10');

INSERT INTO orders (order_id, customer_id, store_id, order_date, order_status)
SELECT
    order_number,
    ((order_number - 1) % 20) + 1,
    ((order_number - 1) % 3) + 1,
    CAST(
        DATE '2025-09-05'
        + ((order_number - 1) / 8) * INTERVAL '1 month'
        + ((order_number - 1) % 8) * INTERVAL '2 days'
        AS DATE
    ),
    'Completed'
FROM generate_series(1, 96) AS generated(order_number);

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
    generated.order_id,
    products.product_id,
    CASE
        WHEN products.category_id = 3
            THEN GREATEST(1, 12 - ((generated.order_id - 1) / 8))
        ELSE 2 + ((generated.order_id - 1) / 32)
    END,
    products.unit_price
FROM generate_series(1, 96) AS generated(order_id)
JOIN products
    ON products.product_id = ((generated.order_id - 1) % 8) + 1;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
    generated.order_id,
    8,
    1,
    products.unit_price
FROM generate_series(4, 96, 4) AS generated(order_id)
JOIN products ON products.product_id = 8;

INSERT INTO payments (
    order_id,
    payment_date,
    amount,
    payment_method,
    payment_status
)
SELECT
    orders.order_id,
    orders.order_date,
    SUM(order_items.quantity * order_items.unit_price),
    CASE orders.order_id % 3
        WHEN 0 THEN 'Card'
        WHEN 1 THEN 'Cash'
        ELSE 'Digital Wallet'
    END,
    'Paid'
FROM orders
JOIN order_items ON order_items.order_id = orders.order_id
GROUP BY orders.order_id, orders.order_date;

WITH returned_lines AS (
    SELECT
        order_items.order_id,
        MIN(order_items.order_item_id) AS order_item_id
    FROM order_items
    WHERE order_items.order_id % 11 = 0
    GROUP BY order_items.order_id
)
INSERT INTO returns (
    order_item_id,
    return_date,
    return_quantity,
    refund_amount,
    return_reason
)
SELECT
    returned_lines.order_item_id,
    orders.order_date + 10,
    1,
    order_items.unit_price,
    CASE
        WHEN orders.order_id % 2 = 0 THEN 'Damaged'
        ELSE 'Changed mind'
    END
FROM returned_lines
JOIN order_items
    ON order_items.order_item_id = returned_lines.order_item_id
JOIN orders
    ON orders.order_id = returned_lines.order_id;

SELECT
    (SELECT COUNT(*) FROM customers) AS customers,
    (SELECT COUNT(*) FROM products) AS products,
    (SELECT COUNT(*) FROM categories) AS categories,
    (SELECT COUNT(*) FROM stores) AS stores,
    (SELECT COUNT(*) FROM orders) AS orders,
    (SELECT COUNT(*) FROM order_items) AS order_items,
    (SELECT COUNT(*) FROM payments) AS payments,
    (SELECT COUNT(*) FROM returns) AS returns;

What Does This File Do?

  • The table definitions enforce required values, unique records, positive quantities, and valid relationships.
  • The insert statements generate a fixed retail dataset with known customer, product, store, order, payment, and return totals.
  • The final query counts every table so you can catch an incomplete setup before starting the analysis.
Run the naive sales analysis

A monthly trend turns transaction rows into a result a store manager can scan. This first query defines sales as item quantity multiplied by sale unit price.

That definition sounds reasonable at first glance. Running it before evaluating its business meaning creates the baby version of your finished report.

  • Click Open File in the retail_analytics Query Tool toolbar.
  • Double-click 02_gross_sales.sql in the project files.

Before you execute the query, do you think multiplying sold quantity by unit price captures the full story of revenue?

  • Click Execute script in the Query Tool toolbar.

You should see 12 monthly rows in Data Output. The first month is 2025-09-01. The final month is 2026-08-01.

The Trend Has an Intended Gap

The query calculates gross sales from every sold item. It never subtracts the refund amounts recorded in returns.

Returned merchandise therefore remains inside the monthly totals. This visible shortfall proves why a metric definition matters as much as valid SQL.

✔️ Awesome, I've got everything!

Your monthly trend covers the complete 12-month reporting period. You have also identified why gross sales can overstate the revenue the business keeps.

ⓧ I'd like to double check the full code

SELECT
    CAST(date_trunc('month', orders.order_date) AS DATE) AS sales_month,
    ROUND(SUM(order_items.quantity * order_items.unit_price), 2) AS gross_sales
FROM orders
JOIN order_items ON order_items.order_id = orders.order_id
GROUP BY CAST(date_trunc('month', orders.order_date) AS DATE)
ORDER BY sales_month;

What Does This Query Do?

  • The join connects each order date to the product lines purchased in that order.
  • The calculation multiplies quantity by unit price before adding each month's line values.
  • The grouping produces one row per month. The ordering places those rows in reporting sequence.
  • The query does not reference refund amounts, which creates the intended gross-sales limitation.

Do You See Fewer Than 12 Rows?

Confirm that 01_schema_and_data.sql produced 96 orders before running the gross-sales query. Also confirm that this Query Tool is connected to retail_analytics.

If the date range or row count still differs, help me compare my retail dataset and gross-sales query with the expected 12-month result.

Your retail database is populated, connected, and producing a visible monthly trend. Next, you will centralize the refund logic so every later insight uses returns-aware revenue.

Create Returns-Aware Views

Your monthly trend now spans 12 months. It still counts every returned item as revenue.

That gap can distort every later metric. A reusable view layer in PostgreSQL centralizes the refund logic before any business question or dashboard uses the data.

In this step, get ready to:
  • Build an item-level sales view that exposes returns-aware revenue fields.
  • Create reusable views for monthly KPIs plus customer metrics.
  • Use category comparisons to validate the view layer.
Build the returns-aware detail layer

The safest grain for this analysis is one row per order item. This keeps products plus categories attached to each sale without duplicating its value.

A common table expression named return_totals groups each return by order_item_id before the sales joins. The result preserves one row per order item in vw_sales_detail.

Why aggregate returns first?

A single order item can have return data attached to it. Aggregating that data first prevents the later joins from multiplying the original sale.

The view preserves the original value in gross_revenue. It stores returned money in refund_amount.

The net_revenue field subtracts the refund. The returned_line field provides a numeric flag for later order-level metrics.

The pgAdmin Query Tool from earlier is still connected to retail_analytics. The complete script defines the detail view before the dependent views.

  • Confirm the Query Tool is still connected to retail_analytics.
  • Replace the current SQL Editor contents with the complete 03_analytics_views.sql script in the double-check tab below.

✔️ Awesome, I've got everything!

  • Confirm 03_analytics_views.sql matches the complete script in the other tab.
  • Save 03_analytics_views.sql.

ⓧ I'd like to double check the full code

  • Copy the full script below into the SQL Editor:
CREATE VIEW vw_sales_detail AS
WITH return_totals AS (
    SELECT
        order_item_id,
        SUM(return_quantity) AS returned_quantity,
        SUM(refund_amount) AS refund_amount
    FROM returns
    GROUP BY order_item_id
)
SELECT
    orders.order_id,
    orders.order_date,
    CAST(date_trunc('month', orders.order_date) AS DATE) AS sales_month,
    orders.order_status,
    customers.customer_id,
    customers.customer_name,
    stores.store_id,
    stores.store_name,
    stores.city,
    order_items.order_item_id,
    products.product_id,
    products.product_name,
    categories.category_id,
    categories.category_name,
    order_items.quantity,
    order_items.unit_price,
    payments.payment_method,
    payments.payment_status,
    order_items.quantity * order_items.unit_price AS gross_revenue,
    COALESCE(return_totals.refund_amount, 0.00) AS refund_amount,
    order_items.quantity * order_items.unit_price
        - COALESCE(return_totals.refund_amount, 0.00) AS net_revenue,
    CASE
        WHEN COALESCE(return_totals.refund_amount, 0.00) > 0 THEN 1
        ELSE 0
    END AS returned_line
FROM orders
JOIN customers ON customers.customer_id = orders.customer_id
JOIN stores ON stores.store_id = orders.store_id
JOIN order_items ON order_items.order_id = orders.order_id
JOIN products ON products.product_id = order_items.product_id
JOIN categories ON categories.category_id = products.category_id
JOIN payments ON payments.order_id = orders.order_id
LEFT JOIN return_totals
    ON return_totals.order_item_id = order_items.order_item_id;

CREATE VIEW vw_monthly_sales AS
WITH order_totals AS (
    SELECT
        sales_month,
        order_id,
        SUM(gross_revenue) AS gross_sales,
        SUM(refund_amount) AS refunds,
        SUM(net_revenue) AS net_sales,
        MAX(returned_line) AS returned_order
    FROM vw_sales_detail
    GROUP BY sales_month, order_id
)
SELECT
    sales_month,
    ROUND(SUM(gross_sales), 2) AS gross_sales,
    ROUND(SUM(refunds), 2) AS refunds,
    ROUND(SUM(net_sales), 2) AS net_sales,
    ROUND(SUM(net_sales) / NULLIF(COUNT(*), 0), 2) AS average_order_value,
    ROUND(100.0 * SUM(returned_order) / NULLIF(COUNT(*), 0), 2)
        AS return_rate_pct
FROM order_totals
GROUP BY sales_month;

CREATE VIEW vw_customer_metrics AS
SELECT
    customers.customer_id,
    customers.customer_name,
    COUNT(DISTINCT vw_sales_detail.order_id) AS order_count,
    ROUND(COALESCE(SUM(vw_sales_detail.net_revenue), 0.00), 2)
        AS net_revenue,
    ROUND(
        COALESCE(SUM(vw_sales_detail.net_revenue), 0.00)
        / NULLIF(COUNT(DISTINCT vw_sales_detail.order_id), 0),
        2
    ) AS average_order_value,
    MAX(vw_sales_detail.order_date) AS last_purchase_date,
    DATE '2026-09-01' - MAX(vw_sales_detail.order_date)
        AS days_since_last_purchase,
    CASE
        WHEN MAX(vw_sales_detail.order_date) IS NULL THEN 'No purchases'
        WHEN DATE '2026-09-01' - MAX(vw_sales_detail.order_date) > 60
            THEN 'Inactive'
        ELSE 'Active'
    END AS customer_status
FROM customers
LEFT JOIN vw_sales_detail
    ON vw_sales_detail.customer_id = customers.customer_id
GROUP BY customers.customer_id, customers.customer_name;

CREATE VIEW vw_top_customers AS
SELECT *
FROM vw_customer_metrics
ORDER BY net_revenue DESC, customer_id
LIMIT 10;

CREATE VIEW vw_inactive_customers AS
SELECT *
FROM vw_customer_metrics
WHERE customer_status = 'Inactive';

CREATE VIEW vw_category_trends AS
WITH category_months AS (
    SELECT
        sales_month,
        category_id,
        category_name,
        ROUND(SUM(net_revenue), 2) AS net_revenue
    FROM vw_sales_detail
    GROUP BY sales_month, category_id, category_name
), compared AS (
    SELECT
        sales_month,
        category_id,
        category_name,
        net_revenue,
        LAG(net_revenue) OVER (
            PARTITION BY category_id
            ORDER BY sales_month
        ) AS previous_month_revenue
    FROM category_months
)
SELECT
    sales_month,
    category_id,
    category_name,
    net_revenue,
    previous_month_revenue,
    ROUND(net_revenue - previous_month_revenue, 2) AS revenue_change,
    ROUND(
        100.0 * (net_revenue - previous_month_revenue)
        / NULLIF(previous_month_revenue, 0),
        2
    ) AS mom_change_pct
FROM compared;

CREATE VIEW vw_latest_category_change AS
SELECT *
FROM vw_category_trends
WHERE sales_month = (SELECT MAX(sales_month) FROM vw_category_trends);

SELECT COUNT(*) AS returned_lines FROM returns;
SELECT * FROM vw_monthly_sales ORDER BY sales_month;

How the script is organized

  • The vw_sales_detail view centralizes item-level sales plus refund calculations.
  • The vw_monthly_sales view calculates order-safe monthly KPIs.
  • The customer views turn sales history into value plus recency metrics.
  • The category views compare each month with its preceding month.
  • Save the SQL Editor contents as 03_analytics_views.sql.
Trace KPI and customer calculations

Monthly metrics need an order-level calculation before aggregation. The order_totals CTE collapses item rows into one row per order for each month.

Customer metrics face the same grain problem. COUNT(DISTINCT vw_sales_detail.order_id) ensures an order with multiple products counts once.

How the KPI views protect the grain

  • The order_totals CTE groups detail rows by sales_month plus order_id.
  • The final COUNT(*) counts those order-level rows for average order value.
  • The MAX(returned_line) calculation marks an order when any item has a return.
  • The fixed reporting date 2026-09-01 makes customer recency reproducible.
  • Locate CREATE VIEW vw_monthly_sales AS in 03_analytics_views.sql.
  • Trace the order_totals CTE through the final monthly grouping.
  • Locate CREATE VIEW vw_customer_metrics AS.
  • Confirm customer average order value uses COUNT(DISTINCT vw_sales_detail.order_id).
  • Confirm days_since_last_purchase uses the fixed date 2026-09-01.

These calculations keep multi-item orders from inflating order counts. They also give every customer the same reporting date for recency analysis.

Compare categories and validate the views

A window function can compare a row with another row while preserving both in the result. LAG retrieves the previous monthly revenue inside each category.

How the category comparison works

The category_months CTE calculates monthly net revenue for each category. The compared CTE adds the previous monthly value.

The PARTITION BY category_id clause gives each category its own timeline. The vw_latest_category_change view keeps the newest reporting month.

  • Locate CREATE VIEW vw_category_trends AS in 03_analytics_views.sql.
  • Confirm the comparison uses LAG(net_revenue) OVER (PARTITION BY category_id ORDER BY sales_month).
  • Confirm vw_latest_category_change filters to the maximum sales_month.

Before you execute the script, decide whether every monthly net total will still match its gross total.

  • Execute the entire 03_analytics_views.sql script by pressing F5.

The validation result reports returned_lines as 8. The monthly output contains at least one row where net_sales is lower than gross_sales.

Views not validating?

  • Confirm the Query Tool connection targets retail_analytics.
  • Compare 03_analytics_views.sql with the full-code tab if a view fails to create.
  • Run the final two validation queries from a fresh Query Tool tab if every view already exists.

Help me diagnose why my PostgreSQL analytics views or validation queries are failing.

That closes the accounting gap. Every later query can now use the same returns-aware definitions.

Your analytics layer now handles refunds consistently. Next up, you will use these views to answer seven sales plus customer questions.

Answer Business Questions

Your returns-aware views now give every analysis a consistent foundation. This step turns those views into seven reproducible business answers.

A dashboard earns trust when every visual maps to a precise business question. You will define each metric in SQL before Power BI presents it.

In this step, get ready to:
  • Answer the sales-performance questions with returns-aware views.
  • Answer the customer-value questions with reusable metrics.
  • Calculate an order-level return rate with a clear denominator.
Analyze sales, products, and categories

Sales performance needs a time view plus a ranking view. The latest category comparison adds an early warning about declining revenue.

The Query Tool in pgAdmin remains connected to retail_analytics. You will build one script that keeps every business answer reproducible.

  • Switch back to the Query Tool connected to retail_analytics.
  • Clear the SQL Editor.
  • Start 04_business_questions.sql by pasting this monthly trend query:
-- 1. What are the monthly sales trends?
SELECT *
FROM vw_monthly_sales
ORDER BY sales_month;

How Does This Measure Sales Movement?

  • The gross_sales column shows revenue before refunds.
  • The refunds column shows recorded refunds.
  • The net_sales column shows revenue after refunds.
  • Save the SQL editor contents as 04_business_questions.sql.
  • Execute the current script by pressing F5.

You will see 12 monthly rows in Data Output. Each row separates gross sales from refunds and net sales.

Monthly Results Missing?

  • Confirm the Query Tool is connected to retail_analytics.
  • Return to 03_analytics_views.sql if vw_monthly_sales is missing.

Help me diagnose a missing monthly sales result in pgAdmin.

  • Append this product-ranking query to 04_business_questions.sql.
-- 2. Which products generate the highest net revenue?
SELECT
    product_name,
    category_name,
    ROUND(SUM(net_revenue), 2) AS net_revenue
FROM vw_sales_detail
GROUP BY product_name, category_name
ORDER BY net_revenue DESC;

How Does This Rank Products?

  • The query groups item-level revenue by product and category.
  • The total uses net_revenue so recorded refunds reduce the ranking.
  • Descending order places the highest-earning product first.
  • Save 04_business_questions.sql.
  • Execute the growing script by pressing F5.

The second result set ranks products from highest to lowest net revenue.

Product Totals Look Inflated?

  • Confirm the total uses net_revenue from vw_sales_detail.
  • Check that both selected text fields appear in the GROUP BY clause.

Help me troubleshoot the product revenue ranking.

  • Append this latest-category comparison to 04_business_questions.sql.
-- 3. Which categories are declining in the latest month?
SELECT
    category_name,
    net_revenue,
    previous_month_revenue,
    revenue_change,
    mom_change_pct
FROM vw_latest_category_change
WHERE mom_change_pct < 0
ORDER BY mom_change_pct;

How Does This Detect Decline?

  • The view already compares the newest category revenue with the previous month.
  • The negative filter keeps categories whose month-over-month percentage has fallen.
  • Ascending order places the steepest decline first.
  • Save 04_business_questions.sql.
  • Execute the growing script by pressing F5.

The latest declining-category result includes Apparel. Its mom_change_pct value is below zero.

Apparel Missing From the Result?

  • Confirm the query reads from vw_latest_category_change.
  • Check that the filter uses mom_change_pct < 0.

Help me find why Apparel is missing from the declining-category result.

Analyze customer value and recency

Customer value shows who contributes the most net revenue. Recency shows whose purchasing activity has gone quiet.

  • Append this top-customer query to 04_business_questions.sql.
-- 4. Who are the top 10 customers?
SELECT *
FROM vw_top_customers
ORDER BY net_revenue DESC, customer_id;

How Does This Rank Customers?

  • The vw_top_customers view already limits the result to ten customers.
  • The ordering prioritizes net revenue.
  • The customer identifier provides a consistent tie-breaker.
  • Save 04_business_questions.sql.
  • Execute the growing script by pressing F5.

The fourth result set shows up to ten customers ranked by net revenue.

Customer Ranking Out of Order?

  • Confirm net_revenue uses descending order.
  • Check that the query reads from vw_top_customers.

Help me correct the customer revenue ranking.

  • Append this overall average-order-value query to 04_business_questions.sql.
-- 5. What is the overall average order value after refunds?
SELECT
    ROUND(
        SUM(net_revenue) / NULLIF(COUNT(DISTINCT order_id), 0),
        2
    ) AS average_order_value
FROM vw_sales_detail;

Why Count Distinct Orders?

  • The numerator sums revenue after refunds.
  • The denominator counts each order once.
  • The distinct count prevents multi-item orders from inflating the denominator.
  • Save 04_business_questions.sql.
  • Execute the growing script by pressing F5.

The fifth result set contains one average_order_value calculated from net revenue and distinct orders.

Average Order Value Missing?

  • Confirm the denominator contains COUNT(DISTINCT order_id).
  • Check that the numerator uses SUM(net_revenue).

Help me troubleshoot the net average order value query.

  • Append this inactive-customer query to 04_business_questions.sql.
-- 6. Which customers have not purchased recently?
SELECT *
FROM vw_inactive_customers
ORDER BY days_since_last_purchase DESC, customer_id;

How Does This Measure Recency?

  • The view compares each customer's last purchase with 2026-09-01.
  • The inactive rule keeps customers beyond the project-defined 60-day threshold.
  • The ordering places the longest absence first.
  • Save 04_business_questions.sql.
  • Execute the growing script by pressing F5.

The sixth result set returns inactive customers. Their days_since_last_purchase values are greater than 60.

No Inactive Customers Returned?

  • Confirm the query reads from vw_inactive_customers.
  • Return to 03_analytics_views.sql if the recency view was not created successfully.

Help me diagnose an empty inactive-customer result.

Calculate and interpret return rate

Return rate needs one row per order before division. A common table expression collapses the item-level return flags into that order-level grain.

  • Append this returned-order percentage query to 04_business_questions.sql.
-- 7. What percentage of orders include a return?
WITH order_returns AS (
    SELECT
        order_id,
        MAX(returned_line) AS returned_order
    FROM vw_sales_detail
    GROUP BY order_id
)
SELECT
    ROUND(
        100.0 * SUM(returned_order) / NULLIF(COUNT(*), 0),
        2
    ) AS returned_order_pct
FROM order_returns;

Why Collapse to One Row per Order?

  • The order_returns CTE groups item-level rows by order_id.
  • The MAX(returned_line) expression marks an order when any item has a return.
  • The final denominator counts orders.
  • Counting returned line items measures the share of item lines affected by returns.
  • Save 04_business_questions.sql.
  • Execute the growing script by pressing F5.

The final result set shows returned_order_pct as 8.33.

Return Rate Differs From 8.33?

  • Confirm order_returns groups by order_id.
  • Check that MAX(returned_line) creates one return flag per order.
  • Verify the final denominator uses COUNT(*) from the order-level CTE.

Help me debug an incorrect returned-order percentage.

✔️ Awesome, I've got everything!

Your seven queries are together in 04_business_questions.sql. Confirm the file is saved before the final run.

ⓧ I'd like to double check the full code

-- 1. What are the monthly sales trends?
SELECT *
FROM vw_monthly_sales
ORDER BY sales_month;

-- 2. Which products generate the highest net revenue?
SELECT
    product_name,
    category_name,
    ROUND(SUM(net_revenue), 2) AS net_revenue
FROM vw_sales_detail
GROUP BY product_name, category_name
ORDER BY net_revenue DESC;

-- 3. Which categories are declining in the latest month?
SELECT
    category_name,
    net_revenue,
    previous_month_revenue,
    revenue_change,
    mom_change_pct
FROM vw_latest_category_change
WHERE mom_change_pct < 0
ORDER BY mom_change_pct;

-- 4. Who are the top 10 customers?
SELECT *
FROM vw_top_customers
ORDER BY net_revenue DESC, customer_id;

-- 5. What is the overall average order value after refunds?
SELECT
    ROUND(
        SUM(net_revenue) / NULLIF(COUNT(DISTINCT order_id), 0),
        2
    ) AS average_order_value
FROM vw_sales_detail;

-- 6. Which customers have not purchased recently?
SELECT *
FROM vw_inactive_customers
ORDER BY days_since_last_purchase DESC, customer_id;

-- 7. What percentage of orders include a return?
WITH order_returns AS (
    SELECT
        order_id,
        MAX(returned_line) AS returned_order
    FROM vw_sales_detail
    GROUP BY order_id
)
SELECT
    ROUND(
        100.0 * SUM(returned_order) / NULLIF(COUNT(*), 0),
        2
    ) AS returned_order_pct
FROM order_returns;

Before the final run, predict the three result sets that should prove the analysis is complete.

  • Save 04_business_questions.sql.
  • Execute the complete script by pressing F5.

You will see seven reproducible result sets. The return-rate result shows 8.33.

The declining-category result includes Apparel. The inactive-customer result contains customer rows.

That is the analysis layer complete. Each future report visual now has a precise business question behind it.

Your seven business answers are ready for presentation. Next, you will return to the blank Power BI Desktop report and turn these metrics into interactive visuals.

Build the Interactive Power BI Report

Your PostgreSQL database now answers seven retail questions with returns-aware definitions. The calculations already distinguish gross revenue from net revenue.

Microsoft Power BI Desktop turns those trusted results into a report that a store manager can scan. You will connect the database before building measures plus interactive visuals.

In this step, get ready to:
  • Connect Power BI Desktop to the local retail database.
  • Create seven DAX measures for the report metrics.
  • Build report visuals that respond to store or category filters.
Connect Power BI Desktop to PostgreSQL

Power BI needs a defined source before it can build report visuals. Import mode loads the retail views into the local report model.

  • Switch back to the blank report in Power BI Desktop.
  • Open the data connection menu from the Power BI Desktop ribbon.
  • Select PostgreSQL database from the available connectors.
  • Enter localhost for the server.
  • Enter retail_analytics for the database.
  • Choose Import as the connection mode.
  • Continue to the authentication screen.

Why use Import mode?

Import mode brings a snapshot of the retail views into the local report. This approach fits the deterministic dataset used throughout the project.

The model can calculate measures from the imported rows as slicers change. Your report remains usable without publishing it to the Power BI service.

The credential screen is the only sensitive point in this step. Your screenshot checkpoint comes later after this screen is closed.

  • Enter postgres as the database username.
  • Enter the PostgreSQL installation password you created earlier.
  • Connect to the local database.

You will see the Navigator with the available database objects. The four report views are ready to load from here.

  • Select vw_sales_detail in Navigator.
  • Select vw_top_customers in Navigator.
  • Select vw_latest_category_change in Navigator.
  • Select vw_inactive_customers in Navigator.
  • Select Load to add the four views to the report model.

That is a major piece in place: the report canvas can now use your trusted retail views.

Connection not opening?

Confirm that the local PostgreSQL server is running. Check that the server is localhost.

Check that the database name is retail_analytics. Re-enter the installation password if authentication fails.

Help me diagnose my local Power BI PostgreSQL connection.

Add measures and core visuals

DAX measures give each report metric one reusable definition. They recalculate when a slicer changes the current filter context.

  • Select Modeling in the Power BI Desktop ribbon.
  • Select New measure.
  • Create Gross Revenue with the first formula below.
  • Repeat the New measure action for each remaining formula:
Gross Revenue = SUM(vw_sales_detail[gross_revenue])

Refund Amount = SUM(vw_sales_detail[refund_amount])

Net Revenue = SUM(vw_sales_detail[net_revenue])

Orders = DISTINCTCOUNT(vw_sales_detail[order_id])

Returned Orders =
CALCULATE(
    DISTINCTCOUNT(vw_sales_detail[order_id]),
    vw_sales_detail[returned_line] = 1
)

Average Order Value = DIVIDE([Net Revenue], [Orders], 0)

Return Rate = DIVIDE([Returned Orders], [Orders], 0)

What do these measures calculate?

  • Gross Revenue totals revenue before refunds.
  • Refund Amount totals recorded refunds.
  • Net Revenue totals revenue after recorded refunds.
  • Orders counts distinct order IDs.
  • Returned Orders counts distinct orders containing a returned line.
  • Average Order Value divides net revenue by distinct orders.
  • Return Rate divides returned orders by all orders.
  • Confirm that all seven measures appear in the Data list with calculator icons.

Missing a measure?

Create every formula as a separate measure. Pasting the entire source into one formula bar does not create seven measures.

Compare the measure names with the source before building visuals. A changed name can break formulas such as Average Order Value.

Help me find the problem in my Power BI measures.

✔️ Awesome, I've got everything!

Great. Confirm that the report now contains all seven measures before continuing.

ⓧ I'd like to double check the full code

Gross Revenue = SUM(vw_sales_detail[gross_revenue])

Refund Amount = SUM(vw_sales_detail[refund_amount])

Net Revenue = SUM(vw_sales_detail[net_revenue])

Orders = DISTINCTCOUNT(vw_sales_detail[order_id])

Returned Orders =
CALCULATE(
    DISTINCTCOUNT(vw_sales_detail[order_id]),
    vw_sales_detail[returned_line] = 1
)

Average Order Value = DIVIDE([Net Revenue], [Orders], 0)

Return Rate = DIVIDE([Returned Orders], [Orders], 0)

The core visuals turn the measures into a first report page. Cards show headline metrics while charts expose changes across time or products.

  • Select the Build visual tab in the Visualizations pane.
  • Select the Card visual.
  • Add Net Revenue to the Values field well.
  • Create a second Card for Average Order Value.
  • Create a third Card for Return Rate.

You should see three KPI cards on the canvas. Each card displays one returns-aware metric.

  • Select the Line chart visual.
  • Add sales_month to the X-axis field well.
  • Add Net Revenue to the Y-axis field well.
  • Add category_name to the Legend field well.

You should see monthly net revenue split into category series. The chart covers the imported sales period.

  • Select a clustered column chart from the Visualizations pane.
  • Add product_name to the X-axis field well.
  • Add Net Revenue to the Y-axis field well.

You should see products compared by net revenue. Your report now has both a time view plus a product view.

Add decision views and filters

Decision tables preserve the detailed results behind the summary visuals. Slicers let a manager narrow the main report to a specific month, store, or category.

  • Add a table visual from vw_top_customers using customer_name, order_count, net_revenue, plus average_order_value.
  • Add a table visual from vw_latest_category_change using category_name, net_revenue, previous_month_revenue, plus mom_change_pct.
  • Add a table visual from vw_inactive_customers using customer_name, last_purchase_date, days_since_last_purchase, plus net_revenue.

The report now shows high-value customers, current category movement, plus customers who may need re-engagement.

  • Select the Slicer visual in the Visualizations pane.
  • Choose sales_month from vw_sales_detail in the Data pane.
  • Create a second Slicer using store_name from vw_sales_detail.
  • Create a third Slicer using category_name from vw_sales_detail.

Before you test the report, consider whether every KPI plus chart should respond to the store filter.

  • Select a different store in the store_name slicer.
  • Confirm that the three KPI cards update.
  • Confirm that the monthly line chart updates.
  • Confirm that the product chart updates.
  • Select a value in the category_name slicer.
  • Confirm that the same KPI cards plus charts update again.
  • Save the local report as Retail_Sales_Analytics.pbix.

That is the full analyst workflow working: your returns-aware SQL now powers an interactive local report.

The report stays on your computer. No report has been published to the Power BI service.

Slicers not changing the charts?

Confirm that every slicer uses a field from vw_sales_detail. The KPI measures plus both charts also use that imported view.

Clear any existing slicer choice before selecting another value. Watch the KPI cards to confirm that the filter is reaching the measures.

Help me troubleshoot report slicers that do not update my visuals.

Secret mission

Segment Customers by Recency

Turn one inactivity flag into three customer recency tiers. You will add Active, Watchlist, and At Risk segments to your local report so a retention team can focus on the customers who have been away longest.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep your resources running, pause your work for later, or delete everything you created. Everything stays on your computer. There are no cloud resources or ongoing costs.

Resources you used:

  • One local PostgreSQL database named retail_analytics.
  • Eight local SQL and DAX source files: 00_create_database.sql, 01_schema_and_data.sql, 02_gross_sales.sql, 03_analytics_views.sql, 04_business_questions.sql, 05_power_bi_measures.dax, 06_secret_mission.sql, and 06_secret_mission_measure.dax.
  • One saved Power BI Desktop report named Retail_Sales_Analytics.pbix.

Keep everything running

No action needed. Choose this if you want to keep refining the report or rerunning the analysis.

  • Keep the retail_analytics database for future queries.
  • Retain the eight source files as a reproducible record of your analysis.
  • Retain Retail_Sales_Analytics.pbix for future report changes.

Pause - I'll come back to this later

Pausing keeps every artifact intact while releasing the memory used by the open desktop apps. PostgreSQL can remain installed for later projects.

  • Save any final changes to Retail_Sales_Analytics.pbix.
  • Close Power BI Desktop.
  • Close pgAdmin.
  • Leave PostgreSQL installed for later projects.

Your database, source files, and report remain available when you return.

Delete - I don't want to use this again

Remove the local database and every project file when you want a clean slate. This option erases the work you created in this project.

Deletion is permanent

Database deletion is permanent. Any database output without a backup disappears with it.

  • Back up any database output or report content you want to keep.
  • Close Power BI Desktop.
  • In pgAdmin, close every Query Tool tab connected to retail_analytics.
  • Select postgres in the Object Explorer.
  • Select Tools from the top menu.
  • Select Query Tool.
  • Remove the project database by executing this SQL command:
DROP DATABASE retail_analytics;

What does this command do?

PostgreSQL runs this command while the Query Tool is connected to postgres. It removes the local retail_analytics database without trying to delete the database that owns the active Query Tool session.

  • Refresh the database tree in pgAdmin.
  • Confirm that retail_analytics no longer appears.

Database still in use?

If PostgreSQL reports that another session is using the database, another connection remains open. Close Power BI Desktop. Close every pgAdmin Query Tool tab attached to retail_analytics before trying again.

Help me troubleshoot why PostgreSQL cannot drop my local retail_analytics database.

The local source files remain after database deletion. Windows search can locate them without assuming which folder you used.

  • Press the Windows key to open Windows search.
  • Search for 00_create_database.sql.
  • Open the matching project file's saved location.
  • Delete the matching file in File Explorer.
  • Repeat this removal process for the seven remaining source filenames in the resources checklist.
  • Confirm that every original source file is gone from its saved location.

That's the source layer cleared. The saved report is the final local artifact.

  • Search Windows for Retail_Sales_Analytics.pbix.
  • Open the matching project report's saved location.
  • Delete the matching report in File Explorer.
  • Confirm that Retail_Sales_Analytics.pbix is gone from its original saved location.

Nice Work!

Nice Work!

You did it! Your returns-aware retail analytics system now runs from a normalized PostgreSQL database to an interactive Power BI Desktop report.

You've learned how to:

  • Design a normalized relational model that connects customers, products, categories, stores, orders, order items, payments, and returns through primary and foreign keys.
  • Use returns-aware SQL views to calculate trustworthy net revenue across seven reproducible business questions.
  • Build an interactive report where KPI cards, sales trends, product rankings, and customer insights respond to month, store, and category slicers.
  • Secret Mission: Extend customer recency into three engagement segments that prioritize At Risk customers for a retention campaign.

Ready to quiz yourself?