Analyze Cart Discounts with SQL

Use an API and SQLite to find carts and products with the largest discounts.

Introduction

30 Second Summary

Discounts can lift sales while quietly eating into revenue. A trading manager needs clear evidence about the carts and products causing the biggest loss.

In this project, you will investigate which e-commerce carts and products lose the most value to discounts. Your SQL analysis in SQLite will turn a live REST API response from Postman into a business recommendation.

What You'll Build

Your final demo traces a live shopping cart response into a ranked analysis that names the cart and product responsible for the most discount value.

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

A live JSON cart response you can inspect for cart totals plus every nested product.
A relational database that keeps each cart connected to its product lines.
A ranked discount analysis that identifies the highest-impact cart plus its leading product.
Secret Mission: Build a reusable discount-risk view that labels every cart as High, Review, or Normal.

Are there any prerequisites?

No prior technical experience is required. You need a Windows computer.

The guide includes installation for Postman plus DB Browser for SQLite.

Before We Start

This first checkpoint locks in the decision your analysis needs to support. You are investigating which carts and products lose the most gross value to discounts so an e-commerce trading manager can prioritize discount reviews.

Set Up the Analysis Workspace

A reliable discount investigation needs one place to inspect live API responses. It also needs one place to test the data model without setting up a database server.

In this step, you will prepare Postman for live requests. You will also prepare DB Browser for SQLite with a local SQLite database that can run a test query.

In this step, get ready to:

Install the signed-out Postman desktop app.
Install DB Browser for SQLite version 3.13.1.
Prove retail_cart_analysis.db can execute a setup query.

Install Postman on Windows

The Postman desktop app provides a request workspace on your computer. Its signed-out client lets you test APIs without creating an account.

Why use the desktop app?

The desktop app includes the Lightweight Postman API Client while you are signed out. This keeps the project focused on sending requests.

Open the official Postman installation guide.
Download the latest Postman desktop app for your Windows architecture.

You should see the downloaded .exe installer in your browser's downloads list.

Run the downloaded .exe installer.

When the installation finishes, Postman is available from Windows search.

Press the Windows key to open Windows search.
Type Postman into the search field.

You should see the Postman desktop app in the search results.

Press Enter to open Postman.
Continue in signed-out mode to enter the Lightweight Postman API Client.

You should see a workspace where you can create an HTTP request. Your API request tool is ready without an account.

Postman Not Opening?

Download the Windows build that matches your computer's architecture if the installer does not run.
Return to Postman's signed-out client option if the app displays account choices.

If Postman still does not open, help me diagnose the Postman installation.

Install DB Browser for SQLite

DB Browser provides a visual environment for creating local database files. This project uses version 3.13.1 so the workspace stays consistent.

Choose the tab that matches the DB Browser version currently installed on your computer.

✔️ I see version 3.13.1

Your installed version matches the project version.

Press the Windows key to open Windows search.
Type DB Browser for SQLite into the search field.

You should see DB Browser for SQLite in the search results.

Press Enter to open the app.

ⓧ I see an older version

The older installation needs the pinned 3.13.1 release before you create the database.

Close the older DB Browser window.
Open the official DB Browser download page.

The download page identifies version 3.13.1 as the latest Windows release.

Confirm whether your Windows computer uses a 32-bit, 64-bit, or ARM64 architecture.
Download DB.Browser.for.SQLite-v3.13.1-win32.msi for 32-bit Windows, DB.Browser.for.SQLite-v3.13.1-win64.msi for 64-bit Windows, or DB.Browser.for.SQLite-v3.13.1-arm64.msi for ARM64 Windows.

You should see the matching Standard installer in your browser's downloads list.

Run the downloaded .msi installer.
Keep the default installation options in the installer wizard.

Windows completes the version 3.13.1 installation.

Press the Windows key to open Windows search.
Type DB Browser for SQLite into the search field.

You should see DB Browser for SQLite in the search results.

Press Enter to open the updated app.

ⓧ DB Browser is not installed

Install the pinned release from the official Windows download page.

Open the official DB Browser download page.
Confirm whether your Windows computer uses a 32-bit, 64-bit, or ARM64 architecture.

The architecture determines which Standard installer your computer can run.

Download DB.Browser.for.SQLite-v3.13.1-win32.msi for 32-bit Windows, DB.Browser.for.SQLite-v3.13.1-win64.msi for 64-bit Windows, or DB.Browser.for.SQLite-v3.13.1-arm64.msi for ARM64 Windows.
Run the downloaded .msi installer.

The installer wizard opens with the standard installation options.

Keep the default installation options in the installer wizard.
Press the Windows key after the installation finishes.

Windows search is ready to locate the installed app.

Type DB Browser for SQLite into the search field.
Press Enter to open the app.

You should see the DB Browser for SQLite window open. Your visual SQL workspace is ready for its first database.

Installer Not Running?

Download the 64-bit installer if your computer uses standard 64-bit Windows.
Download the ARM64 installer if your Windows computer uses an ARM processor.

If the installer still fails, help me choose the correct DB Browser installer.

Create and test the local database

An empty database gives the analysis a persistent home before any project tables exist. A small test query confirms that DB Browser can execute SQL against that file.

Use DB Browser's new-database action to create a database.
Save the database to your Desktop as retail_cart_analysis.db.

You should now have retail_cart_analysis.db on your Desktop. DB Browser keeps the database open in its main window.

Close any table-definition prompt so the database remains empty.
Select the Execute SQL tab.

The SQL editor is now ready to accept a test statement.

Enter the setup query by copying the code below into the SQL editor:

SELECT sqlite_version() AS sqlite_version, 1 AS setup_ok;

What Does This Query Check?

The sqlite_version() function returns the version of the SQLite library running inside DB Browser.
The 1 AS setup_ok expression creates a result column named setup_ok with the value 1.

Before you execute the query, do you expect one result row or several rows?

Press Ctrl+Enter to run the query.

You should see one row containing a SQLite version plus setup_ok = 1. That's the workspace ready: your local database can execute SQL.

No Result Row?

Confirm that retail_cart_analysis.db is still open in DB Browser.
Return to the Execute SQL tab if another tab is selected.
Place the cursor inside the query before pressing Ctrl+Enter again.

If the result area stays empty, help me debug the SQLite setup query.

Your API client and local SQL environment are working. Next, you will inspect a live cart response and turn its structure into business requirements.

Inspect the Cart API

Your local analysis workspace is ready. Postman can now reach live data without an account.

A live REST API response reveals the shape of the cart data. You will inspect one cart before deciding what the database must store.

In this step, get ready to:

Send a live cart request in Postman.
Map the response into cart summary fields plus repeating cart items.
Frame a stakeholder question with measurable acceptance criteria.

Send the live request

An HTTP GET request reads a resource from a URL. DummyJSON returns the cart as structured JSON.

In the Postman lightweight API Client from earlier, create an HTTP request from the main workspace.
Keep GET as the request method.
Enter https://dummyjson.com/carts/1 in the request URL field.

Before you select Send, consider whether the response will contain one cart or several carts.

Select Send.
Select Body in the response panel.

You should see one cart displayed as automatically formatted JSON. The response contains cart details at the top level.

Confirm the cart identifiers by locating id plus userId.
Confirm the cart size fields by locating totalProducts plus totalQuantity.
Confirm the cart value fields by locating total plus discountedTotal.
Confirm the repeating product details by locating the products array.

Cart response not loading?

Confirm that the request method remains GET.
Check that the request URL exactly matches https://dummyjson.com/carts/1.
Check your internet connection if the request cannot reach DummyJSON.
Use help me debug this Postman request if the response still does not load.

Translate the response into an analyst's field map

The top-level JSON object represents one cart summary. The products array contains repeating child objects for the items in that cart.

Review the outermost JSON object in the response Body.
Expand the products array if its contents are collapsed.

How Does the Response Become Business Data?

The top-level id becomes the cart identifier.
The top-level userId becomes the customer reference.
The top-level totalProducts records the product-line count. totalQuantity records the unit count.
The top-level total becomes the gross cart total. discountedTotal becomes the discounted cart total.
Each object inside products becomes one repeating cart item.
The product id becomes the item identifier. title becomes the product name.
The product price becomes the unit price. quantity becomes the units purchased.
The product total becomes the gross line total. discountPercentage becomes the discount rate. discountedTotal becomes the discounted line total.

Classify id plus userId as cart identity fields.
Classify totalProducts plus totalQuantity as cart size measures.
Classify total plus discountedTotal as cart value measures.
Apply the product-item map to the first object inside products.

Frame the business requirement

A stakeholder question turns raw fields into a decision target. Acceptance criteria define the evidence needed to answer that question.

Use this stakeholder question: “Which carts and products lose the most gross value to discounts?”
Set the first success criterion as a cart-level discount ranking.
Set the second success criterion as a product-level discount ranking.
Set the final success criterion as one evidence-based recommended action.

Why Do You Need Two Rankings?

The cart ranking locates the basket with the largest discount value. The product ranking identifies the line driving that loss.

The recommendation turns both rankings into a decision for the e-commerce trading manager.

Return to the Postman response from earlier.

Before you send the request again, predict which fields will sit at cart level. Also predict which field will hold repeating records.

Select Send again.
Locate id at the top level.
Locate userId at the top level.
Locate totalProducts plus totalQuantity at the top level.
Locate total plus discountedTotal at the top level.
Locate the nested products array.

You should see all six cart summary fields at the top level. The products array should contain repeating product objects.

You've turned a live API response into a clear analysis target.

Next, you will test whether cart summaries alone can reveal the product driving the largest discount.

Prove the Flat Table Falls Short

Your live DummyJSON response revealed a cart summary plus repeating product details. The stakeholder question needs evidence from both levels.

This step tests how far a flat table can take the analysis. You will load only the cart-level fields into SQLite before checking whether the result can identify a product driver.

In this step, get ready to:

Create a reproducible analysis script with the business requirement.
Load four cart summaries into a flat table.
Rank the carts before testing the unmet product-level requirement.

Start the analysis script

A saved SQL script makes the analysis repeatable. You will build the script inside the DB Browser for SQLite window from earlier.

Switch back to DB Browser for SQLite from earlier.
Select the Execute SQL tab.
Replace the earlier setup query with this first block:

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS analysis_findings;
DROP TABLE IF EXISTS cart_items;
DROP TABLE IF EXISTS cart_snapshot;
DROP TABLE IF EXISTS business_requirements;

-- Business analysis requirement
CREATE TABLE business_requirements (
    requirement_id INTEGER PRIMARY KEY,
    stakeholder TEXT NOT NULL,
    business_question TEXT NOT NULL,
    success_measure TEXT NOT NULL
);

What does this code do?

The foreign-key setting prepares SQLite to enforce relationships when the product table is added later.
The guarded drop statements reset the project tables whenever the script is rerun.
The business_requirements table gives the stakeholder question a permanent place in the database.
The requirement_id column acts as the table's primary key.

Execute the block by pressing Ctrl+Enter.
Click Save SQL file in the Execute SQL toolbar.
Choose the folder that contains retail_cart_analysis.db.
Enter retail_cart_analysis.sql as the file name.
Click Save.

The execution should finish without a SQL error. Your analysis now has a reusable script plus an empty requirements table.

Did the first block fail?

Check that every table definition ends with a closing parenthesis followed by a semicolon. Confirm that the code is running against retail_cart_analysis.db.

If the editor still reports a problem, help me debug the first SQL block.

The table needs one row that captures the decision this analysis must support. That row becomes the reference point for every query that follows.

Append this requirement row below the existing table definition:

INSERT INTO business_requirements (
    requirement_id,
    stakeholder,
    business_question,
    success_measure
) VALUES (
    1,
    'E-commerce trading manager',
    'Which carts and products lose the most gross value to discounts?',
    'Return cart-level and product-level discount rankings and record one recommended action.'
);

What does this requirement capture?

The stakeholder field identifies the person who needs the decision.
The business question names the discount-loss problem.
The success measure requires a cart-level ranking.
It also requires a product-level ranking plus one recommended action.

Execute the cumulative script by pressing Ctrl+Enter.
Save the updated script with the Save SQL file control.

The result area should confirm that the statements completed without an error. Requirement row 1 is now loaded.

Did the requirement insert fail?

Check the commas between the four inserted values. Confirm that each text value begins and ends with a single quote.

For help locating the typo, debug my business requirement insert.

The next table mirrors the top level of the API response. Each row represents one cart without its nested products.

Append this cart summary table below the requirement row:

-- Flat cart summary that mirrors the top level of the API response
CREATE TABLE cart_snapshot (
    cart_id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total_products INTEGER NOT NULL,
    total_quantity INTEGER NOT NULL,
    gross_total REAL NOT NULL,
    discounted_total REAL NOT NULL
);

How is the cart summary modeled?

The cart_id column uniquely identifies each cart.
The user_id column preserves the cart owner's identifier.
The product-count columns store the size of each cart.
The total columns store gross value before discounts plus the final discounted value.

Execute the cumulative script by pressing Ctrl+Enter.
Save the updated retail_cart_analysis.sql file.

The execution should complete without an error. The database now has a cart-level structure ready for sample rows.

Did the cart table fail to appear?

Check the comma after every column except discounted_total. Confirm that the table definition ends with a closing parenthesis followed by a semicolon.

If the structure still fails, help me debug the cart_snapshot table.

Stable sample rows make the ranking reproducible. Their columns follow the cart-level field map you identified from the live response.

Append these four cart summaries below the cart_snapshot table definition:

INSERT INTO cart_snapshot (
    cart_id,
    user_id,
    total_products,
    total_quantity,
    gross_total,
    discounted_total
) VALUES
    (101, 501, 2, 3, 90.00, 83.00),
    (102, 502, 2, 3, 210.00, 174.00),
    (103, 503, 2, 2, 130.00, 123.60),
    (104, 504, 2, 3, 240.00, 214.00);

Why use stable sample rows?

The live API response reveals the real data shape. These synthetic rows keep every ranking consistent when the public sample data changes.

A reproducible dataset lets another analyst rerun the script and reach the same evidence.

Execute the cumulative script by pressing Ctrl+Enter.
Commit the inserted rows by clicking Write Changes.
Save the updated retail_cart_analysis.sql file.

The statements should complete without an error. The database now contains cart rows 101 through 104.

Did the cart rows fail to load?

Check that each row has six values. Confirm that a comma separates the first three rows.

If the insert still fails, help me debug the cart_snapshot rows.

Calculate cart-level discount impact

Discount value is the difference between gross total and discounted total. The ranking uses that calculation to show which cart gives away the most value.

Append this ranking query below the four cart rows:

-- The flat table can rank carts, but cannot identify a product driver
SELECT
    cart_id,
    user_id,
    gross_total,
    discounted_total,
    ROUND(gross_total - discounted_total, 2) AS discount_value,
    ROUND(
        100.0 * (gross_total - discounted_total) / gross_total,
        2
    ) AS discount_rate_pct
FROM cart_snapshot
ORDER BY discount_value DESC;

How does the ranking work?

The subtraction calculates the lost gross value for each cart.
The first ROUND expression keeps the discount value to two decimal places.
The second calculation converts the discount into a percentage of gross value.
The descending sort places the largest discount value first.

Before you run the ranking, do you think the cart-only rows can identify which product caused the largest discount?

Save retail_cart_analysis.sql.
Execute the cumulative script by pressing Ctrl+Enter.

You should see four result rows. Cart 102 is ranked first with a discount value of 36.0.

Is cart 102 missing from the top row?

Confirm that the final line sorts by discount_value in descending order. Check that cart 102 has a gross total of 210.00 plus a discounted total of 174.00.

If your result differs, help me debug the cart discount ranking.

Test the unmet requirement

The ranking answers the cart half of the stakeholder question. The final test checks whether those same columns can name the product responsible for cart 102.

Locate cart 102 in the result grid.
Scan the result column headers for product_name.
Try to identify the product responsible for the 36.0 discount value using only this result.

There is no product_name column. The flat cart summary discarded the repeating product details that could explain the loss.

Why does the flat table fall short?

Each cart row stores one summary. The API's nested products contain several child records for that cart.

Without those product rows, the database can rank carts but cannot trace a discount back to its product driver.

Use this comparison to confirm that your saved script matches the completed flat-table analysis.

✔️ Awesome, I've got everything!

Great. Double-check that retail_cart_analysis.sql is saved beside your database.

ⓧ I'd like to double check the full code

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS analysis_findings;
DROP TABLE IF EXISTS cart_items;
DROP TABLE IF EXISTS cart_snapshot;
DROP TABLE IF EXISTS business_requirements;

-- Business analysis requirement
CREATE TABLE business_requirements (
    requirement_id INTEGER PRIMARY KEY,
    stakeholder TEXT NOT NULL,
    business_question TEXT NOT NULL,
    success_measure TEXT NOT NULL
);

INSERT INTO business_requirements (
    requirement_id,
    stakeholder,
    business_question,
    success_measure
) VALUES (
    1,
    'E-commerce trading manager',
    'Which carts and products lose the most gross value to discounts?',
    'Return cart-level and product-level discount rankings and record one recommended action.'
);

-- Flat cart summary that mirrors the top level of the API response
CREATE TABLE cart_snapshot (
    cart_id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total_products INTEGER NOT NULL,
    total_quantity INTEGER NOT NULL,
    gross_total REAL NOT NULL,
    discounted_total REAL NOT NULL
);

INSERT INTO cart_snapshot (
    cart_id,
    user_id,
    total_products,
    total_quantity,
    gross_total,
    discounted_total
) VALUES
    (101, 501, 2, 3, 90.00, 83.00),
    (102, 502, 2, 3, 210.00, 174.00),
    (103, 503, 2, 2, 130.00, 123.60),
    (104, 504, 2, 3, 240.00, 214.00);

-- The flat table can rank carts, but cannot identify a product driver
SELECT
    cart_id,
    user_id,
    gross_total,
    discounted_total,
    ROUND(gross_total - discounted_total, 2) AS discount_value,
    ROUND(
        100.0 * (gross_total - discounted_total) / gross_total,
        2
    ) AS discount_rate_pct
FROM cart_snapshot
ORDER BY discount_value DESC;

Compare this reference with the full contents of your saved script. Every statement shown here belongs in retail_cart_analysis.sql.

Before the final check, which cart do you expect to see first after the full script runs?

Execute the full script by pressing Ctrl+Enter.
Commit the database changes by clicking Write Changes.
Inspect the final result grid.

You should see four carts with cart 102 ranked first at 36.0. You should also see that the result has no product_name column.

Does the final check look different?

Confirm that the entire script ran from the first line. Check that the final statement sorts the calculated discount value in descending order.

For a line-by-line comparison, help me verify my completed flat-table script.

You have proved the limitation with evidence from your own result. Next, you will restore the missing product detail with a related child table.

Model Nested Cart Data

Your flat SQLite result ranked cart 102 first. It could not name the product driving the discount loss.

The nested products array stores repeating product lines inside each JSON cart. A relational model keeps those lines in a child table that links back to each cart.

In this step, get ready to:

Define cart_items with parent-child keys.
Load eight representative product lines.
Verify eight joined rows across four carts.

Create the child table

The line_id column acts as the primary key for each product row. The cart_id column acts as the foreign key back to cart_snapshot.

Switch back to DB Browser for SQLite.
Return to the Execute SQL tab containing retail_cart_analysis.sql.
Scroll below the final ORDER BY discount_value DESC line.
Append the child-table definition by copying this block:

-- Normalized child table for the API's nested products array
CREATE TABLE cart_items (
    line_id INTEGER PRIMARY KEY,
    cart_id INTEGER NOT NULL,
    product_name TEXT NOT NULL,
    unit_price REAL NOT NULL,
    quantity INTEGER NOT NULL,
    gross_line_total REAL NOT NULL,
    discount_pct REAL NOT NULL,
    discounted_line_total REAL NOT NULL,
    FOREIGN KEY (cart_id) REFERENCES cart_snapshot(cart_id)
);

What Does This Table Do?

The line_id column gives each product line a unique identity.
The cart_id column links each product line to one row in cart_snapshot.
The remaining columns preserve each product's quantity plus its pricing details.

Save retail_cart_analysis.sql.
Run the full script in the Execute SQL tab by pressing Ctrl+Enter.

That is the structural gap closed. SQLite now accepts the child table without an error.

Table Creation Stopped?

Check that every column before FOREIGN KEY ends with a comma.
Confirm that PRAGMA foreign_keys = ON remains at the top of the script.
Help me compare my cart_items table with the working SQL.

Load the product lines

The eight rows recreate the repeating product pattern you saw in the live response. Their line totals match the four cart summaries.

Scroll to the end of the CREATE TABLE cart_items block.
Append the product rows by copying this block:

INSERT INTO cart_items (
    line_id,
    cart_id,
    product_name,
    unit_price,
    quantity,
    gross_line_total,
    discount_pct,
    discounted_line_total
) VALUES
    (1, 101, 'Wireless Mouse', 25.00, 2, 50.00, 10.00, 45.00),
    (2, 101, 'USB-C Hub', 40.00, 1, 40.00, 5.00, 38.00),
    (3, 102, 'Office Chair', 150.00, 1, 150.00, 20.00, 120.00),
    (4, 102, 'Desk Lamp', 30.00, 2, 60.00, 10.00, 54.00),
    (5, 103, 'Mechanical Keyboard', 80.00, 1, 80.00, 8.00, 73.60),
    (6, 103, 'Laptop Stand', 50.00, 1, 50.00, 0.00, 50.00),
    (7, 104, 'Webcam', 70.00, 2, 140.00, 15.00, 119.00),
    (8, 104, 'Microphone', 100.00, 1, 100.00, 5.00, 95.00);

How Are the Rows Organised?

The line_id values keep all eight product lines unique.
Each cart_id assigns two product lines to its parent cart.
The gross line totals reconcile with the matching cart's gross total.

Save retail_cart_analysis.sql.
Run the full script in the Execute SQL tab by pressing Ctrl+Enter.

The execution status completes without an error. This confirms all eight inserts satisfy the table constraints.

Product Rows Did Not Load?

Run the full script from its first line so the tables are recreated before the inserts.
Confirm that every cart_id value is one of the four IDs already stored in cart_snapshot.
Help me find why my cart_items inserts are failing.

Prove the parent-child relationship

A JOIN reconstructs each cart beside its product lines. A separate count check catches missing parent or child rows.

Scroll below the INSERT INTO cart_items statement.
Append the relationship checks by copying this block:

-- Validate the parent-child relationship
SELECT
    c.cart_id,
    c.user_id,
    i.product_name,
    i.quantity,
    i.gross_line_total,
    i.discounted_line_total
FROM cart_snapshot AS c
JOIN cart_items AS i
    ON c.cart_id = i.cart_id
ORDER BY c.cart_id, i.line_id;

SELECT
    (SELECT COUNT(*) FROM cart_snapshot) AS carts_loaded,
    (SELECT COUNT(*) FROM cart_items) AS items_loaded;

What Do These Checks Prove?

The first query matches each product line to the cart with the same cart_id.
The ordering keeps each cart's product lines together in the result.
The final query counts the parent rows plus the child rows.

Before you run these checks, consider how many joined product rows you expect to see. Consider what each count should report.

Save retail_cart_analysis.sql.
Run the full script in the Execute SQL tab by pressing Ctrl+Enter.

The join result contains eight rows. Each row includes cart_id, user_id, plus product_name.

The final result shows carts_loaded = 4 plus items_loaded = 8. You have closed the flat-table gap with product-level evidence.

Missing Joined Rows?

Compare the VALUES list with the eight product rows shown above.
Confirm that the cart_snapshot inserts run before the cart_items inserts.
Help me diagnose missing rows in my cart join.

✔️ Awesome, I've got everything!

Your script now contains the child table, eight product rows, plus both relationship checks. Double-check that you saved retail_cart_analysis.sql.

ⓧ I'd like to double check the full code

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS analysis_findings;
DROP TABLE IF EXISTS cart_items;
DROP TABLE IF EXISTS cart_snapshot;
DROP TABLE IF EXISTS business_requirements;

-- Business analysis requirement
CREATE TABLE business_requirements (
    requirement_id INTEGER PRIMARY KEY,
    stakeholder TEXT NOT NULL,
    business_question TEXT NOT NULL,
    success_measure TEXT NOT NULL
);

INSERT INTO business_requirements (
    requirement_id,
    stakeholder,
    business_question,
    success_measure
) VALUES (
    1,
    'E-commerce trading manager',
    'Which carts and products lose the most gross value to discounts?',
    'Return cart-level and product-level discount rankings and record one recommended action.'
);

-- Flat cart summary that mirrors the top level of the API response
CREATE TABLE cart_snapshot (
    cart_id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total_products INTEGER NOT NULL,
    total_quantity INTEGER NOT NULL,
    gross_total REAL NOT NULL,
    discounted_total REAL NOT NULL
);

INSERT INTO cart_snapshot (
    cart_id,
    user_id,
    total_products,
    total_quantity,
    gross_total,
    discounted_total
) VALUES
    (101, 501, 2, 3, 90.00, 83.00),
    (102, 502, 2, 3, 210.00, 174.00),
    (103, 503, 2, 2, 130.00, 123.60),
    (104, 504, 2, 3, 240.00, 214.00);

-- The flat table can rank carts, but cannot identify a product driver
SELECT
    cart_id,
    user_id,
    gross_total,
    discounted_total,
    ROUND(gross_total - discounted_total, 2) AS discount_value,
    ROUND(
        100.0 * (gross_total - discounted_total) / gross_total,
        2
    ) AS discount_rate_pct
FROM cart_snapshot
ORDER BY discount_value DESC;

-- Normalized child table for the API's nested products array
CREATE TABLE cart_items (
    line_id INTEGER PRIMARY KEY,
    cart_id INTEGER NOT NULL,
    product_name TEXT NOT NULL,
    unit_price REAL NOT NULL,
    quantity INTEGER NOT NULL,
    gross_line_total REAL NOT NULL,
    discount_pct REAL NOT NULL,
    discounted_line_total REAL NOT NULL,
    FOREIGN KEY (cart_id) REFERENCES cart_snapshot(cart_id)
);

INSERT INTO cart_items (
    line_id,
    cart_id,
    product_name,
    unit_price,
    quantity,
    gross_line_total,
    discount_pct,
    discounted_line_total
) VALUES
    (1, 101, 'Wireless Mouse', 25.00, 2, 50.00, 10.00, 45.00),
    (2, 101, 'USB-C Hub', 40.00, 1, 40.00, 5.00, 38.00),
    (3, 102, 'Office Chair', 150.00, 1, 150.00, 20.00, 120.00),
    (4, 102, 'Desk Lamp', 30.00, 2, 60.00, 10.00, 54.00),
    (5, 103, 'Mechanical Keyboard', 80.00, 1, 80.00, 8.00, 73.60),
    (6, 103, 'Laptop Stand', 50.00, 1, 50.00, 0.00, 50.00),
    (7, 104, 'Webcam', 70.00, 2, 140.00, 15.00, 119.00),
    (8, 104, 'Microphone', 100.00, 1, 100.00, 5.00, 95.00);

-- Validate the parent-child relationship
SELECT
    c.cart_id,
    c.user_id,
    i.product_name,
    i.quantity,
    i.gross_line_total,
    i.discounted_line_total
FROM cart_snapshot AS c
JOIN cart_items AS i
    ON c.cart_id = i.cart_id
ORDER BY c.cart_id, i.line_id;

SELECT
    (SELECT COUNT(*) FROM cart_snapshot) AS carts_loaded,
    (SELECT COUNT(*) FROM cart_items) AS items_loaded;

Your nested cart data now shows which products belong to each cart. Next, you will rank the discount impact and turn the result into a business recommendation.

Make a Business Recommendation

Your parent and child tables are connected. The database can now trace each cart discount back to its product lines.

A useful analysis must answer the trading manager's question. It must also point to a defensible action.

You'll use SQL aggregation to rank discount value at the cart level. A product ranking will expose the biggest driver.

The final result stores the evidence with a clear recommendation.

In this step, get ready to:

Rank carts by total discount impact.
Identify the product responsible for the largest discount value.
Store the evidence and recommendation.

Rank cart-level discount impact

The product lines hold the detail needed to rebuild each cart total. Grouping those lines produces a cart-level ranking while preserving the evidence behind every result.

Return to retail_cart_analysis.sql from earlier.
Extend retail_cart_analysis.sql after the row-count validation by adding this grouped query:

-- Rank carts by total discount value
SELECT
    c.cart_id,
    c.user_id,
    ROUND(SUM(i.gross_line_total), 2) AS gross_total,
    ROUND(SUM(i.discounted_line_total), 2) AS discounted_total,
    ROUND(SUM(i.gross_line_total - i.discounted_line_total), 2) AS discount_value,
    ROUND(
        100.0 * SUM(i.gross_line_total - i.discounted_line_total)
        / SUM(i.gross_line_total),
        2
    ) AS discount_rate_pct
FROM cart_snapshot AS c
JOIN cart_items AS i
    ON c.cart_id = i.cart_id
GROUP BY c.cart_id, c.user_id
ORDER BY discount_value DESC;

How Does This Ranking Work?

The JOIN connects each product line to its cart summary.
The SUM calculations combine product-line values within each cart.
The GROUP BY clause keeps one result row for each cart.
The ORDER BY clause places the largest discount value first.

Before you run this query, which cart do you expect to lead once its product lines are aggregated?

Highlight the new grouped query in the Execute SQL editor.
Press Ctrl+Enter to run the selected query.

You'll see cart 102 at the top with a discount value of 36.0. Its discount rate is 17.14 percent.

Save retail_cart_analysis.sql.

Can't Reproduce the Cart Ranking?

Confirm the join condition uses c.cart_id = i.cart_id.
Check that cart_items still contains all eight product lines.

If the totals still differ, help me debug the cart ranking query.

Identify the product driver

The cart ranking identifies where discount value is concentrated. The product ranking reveals which line creates most of that impact.

Append this product-ranking query after the cart-ranking query in retail_cart_analysis.sql:

-- Rank products by discount value
SELECT
    product_name,
    cart_id,
    ROUND(gross_line_total - discounted_line_total, 2) AS discount_value,
    discount_pct
FROM cart_items
ORDER BY discount_value DESC;

What Does This Query Reveal?

The subtraction calculates the gross value lost to the discount on each product line.
The ROUND function keeps the calculated value to two decimal places.
The descending order makes the highest-impact product the first result.

Before you run the product ranking, which item do you think contributes most of cart 102's discount value?

Highlight the new product-ranking query in the Execute SQL editor.
Press Ctrl+Enter to run the selected query.

You'll see Office Chair first with a discount value of 30.0. That product contributes most of cart 102's 36.0 discount value.

Save retail_cart_analysis.sql.

Is a Different Product Ranked First?

Confirm the query subtracts discounted_line_total from gross_line_total.
Check the Office Chair row for a gross line total of 150.00.
Check the same row for a discounted line total of 120.00.

If another product remains first, help me trace the product-ranking values.

Store the finding and action

A recommendation becomes reproducible when its evidence lives beside the analysis. The finding records what happened while the recommendation states what the trading manager should review first.

Append this findings table and evidence row after the product-ranking query in retail_cart_analysis.sql:

-- Store the analyst's conclusion and recommended action
CREATE TABLE analysis_findings (
    finding_id INTEGER PRIMARY KEY,
    finding TEXT NOT NULL,
    recommendation TEXT NOT NULL
);

INSERT INTO analysis_findings (
    finding_id,
    finding,
    recommendation
) VALUES (
    1,
    'Cart 102 has the largest discount value at 36.00, and Office Chair contributes 30.00 of that value.',
    'Review the Office Chair discount before changing lower-impact products.'
);

How Is the Recommendation Stored?

The analysis_findings table gives each conclusion a primary key.
The finding column preserves the ranked evidence.
The recommendation column preserves the action supported by that evidence.

Highlight the new table and insert statements in the Execute SQL editor.
Press Ctrl+Enter to run the selected statements.

You'll see both statements complete successfully. The database now contains analysis_findings row 1.

Save retail_cart_analysis.sql.

Couldn't Store the Finding?

Run the guarded DROP TABLE IF EXISTS analysis_findings statement from the top of the script if the table already exists.
Confirm the finding and recommendation each have matching single quotation marks.

If the insert still fails, help me debug the analysis_findings insert.

Append this final query after the insert statement in retail_cart_analysis.sql:

SELECT
    finding,
    recommendation
FROM analysis_findings;

What Does This Final Query Prove?

The query retrieves the stored evidence with its recommended action. This completes the path from product-level data to a decision the trading manager can review.

Before you run the final query, do you expect the stored recommendation to focus on the whole cart or its leading product?

Highlight the final SELECT query in the Execute SQL editor.
Press Ctrl+Enter to run the selected query.

You'll see a finding that names cart 102 with 36.00 in discount value. You'll also see a recommendation to review the Office Chair discount before lower-impact products.

Save the completed retail_cart_analysis.sql file.

Is the Final Result Empty?

Run the INSERT INTO analysis_findings statement again if row 1 was not inserted.
Confirm the final query reads from analysis_findings.

If the row is still missing, help me inspect the stored finding.

✔️ Awesome, I've got everything!

Your saved script now contains the complete reproducible analysis.

ⓧ I'd like to double check the full code

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS analysis_findings;
DROP TABLE IF EXISTS cart_items;
DROP TABLE IF EXISTS cart_snapshot;
DROP TABLE IF EXISTS business_requirements;

-- Business analysis requirement
CREATE TABLE business_requirements (
    requirement_id INTEGER PRIMARY KEY,
    stakeholder TEXT NOT NULL,
    business_question TEXT NOT NULL,
    success_measure TEXT NOT NULL
);

INSERT INTO business_requirements (
    requirement_id,
    stakeholder,
    business_question,
    success_measure
) VALUES (
    1,
    'E-commerce trading manager',
    'Which carts and products lose the most gross value to discounts?',
    'Return cart-level and product-level discount rankings and record one recommended action.'
);

-- Flat cart summary that mirrors the top level of the API response
CREATE TABLE cart_snapshot (
    cart_id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total_products INTEGER NOT NULL,
    total_quantity INTEGER NOT NULL,
    gross_total REAL NOT NULL,
    discounted_total REAL NOT NULL
);

INSERT INTO cart_snapshot (
    cart_id,
    user_id,
    total_products,
    total_quantity,
    gross_total,
    discounted_total
) VALUES
    (101, 501, 2, 3, 90.00, 83.00),
    (102, 502, 2, 3, 210.00, 174.00),
    (103, 503, 2, 2, 130.00, 123.60),
    (104, 504, 2, 3, 240.00, 214.00);

-- The flat table can rank carts, but cannot identify a product driver
SELECT
    cart_id,
    user_id,
    gross_total,
    discounted_total,
    ROUND(gross_total - discounted_total, 2) AS discount_value,
    ROUND(
        100.0 * (gross_total - discounted_total) / gross_total,
        2
    ) AS discount_rate_pct
FROM cart_snapshot
ORDER BY discount_value DESC;

-- Normalized child table for the API's nested products array
CREATE TABLE cart_items (
    line_id INTEGER PRIMARY KEY,
    cart_id INTEGER NOT NULL,
    product_name TEXT NOT NULL,
    unit_price REAL NOT NULL,
    quantity INTEGER NOT NULL,
    gross_line_total REAL NOT NULL,
    discount_pct REAL NOT NULL,
    discounted_line_total REAL NOT NULL,
    FOREIGN KEY (cart_id) REFERENCES cart_snapshot(cart_id)
);

INSERT INTO cart_items (
    line_id,
    cart_id,
    product_name,
    unit_price,
    quantity,
    gross_line_total,
    discount_pct,
    discounted_line_total
) VALUES
    (1, 101, 'Wireless Mouse', 25.00, 2, 50.00, 10.00, 45.00),
    (2, 101, 'USB-C Hub', 40.00, 1, 40.00, 5.00, 38.00),
    (3, 102, 'Office Chair', 150.00, 1, 150.00, 20.00, 120.00),
    (4, 102, 'Desk Lamp', 30.00, 2, 60.00, 10.00, 54.00),
    (5, 103, 'Mechanical Keyboard', 80.00, 1, 80.00, 8.00, 73.60),
    (6, 103, 'Laptop Stand', 50.00, 1, 50.00, 0.00, 50.00),
    (7, 104, 'Webcam', 70.00, 2, 140.00, 15.00, 119.00),
    (8, 104, 'Microphone', 100.00, 1, 100.00, 5.00, 95.00);

-- Validate the parent-child relationship
SELECT
    c.cart_id,
    c.user_id,
    i.product_name,
    i.quantity,
    i.gross_line_total,
    i.discounted_line_total
FROM cart_snapshot AS c
JOIN cart_items AS i
    ON c.cart_id = i.cart_id
ORDER BY c.cart_id, i.line_id;

SELECT
    (SELECT COUNT(*) FROM cart_snapshot) AS carts_loaded,
    (SELECT COUNT(*) FROM cart_items) AS items_loaded;

-- Rank carts by total discount value
SELECT
    c.cart_id,
    c.user_id,
    ROUND(SUM(i.gross_line_total), 2) AS gross_total,
    ROUND(SUM(i.discounted_line_total), 2) AS discounted_total,
    ROUND(SUM(i.gross_line_total - i.discounted_line_total), 2) AS discount_value,
    ROUND(
        100.0 * SUM(i.gross_line_total - i.discounted_line_total)
        / SUM(i.gross_line_total),
        2
    ) AS discount_rate_pct
FROM cart_snapshot AS c
JOIN cart_items AS i
    ON c.cart_id = i.cart_id
GROUP BY c.cart_id, c.user_id
ORDER BY discount_value DESC;

-- Rank products by discount value
SELECT
    product_name,
    cart_id,
    ROUND(gross_line_total - discounted_line_total, 2) AS discount_value,
    discount_pct
FROM cart_items
ORDER BY discount_value DESC;

-- Store the analyst's conclusion and recommended action
CREATE TABLE analysis_findings (
    finding_id INTEGER PRIMARY KEY,
    finding TEXT NOT NULL,
    recommendation TEXT NOT NULL
);

INSERT INTO analysis_findings (
    finding_id,
    finding,
    recommendation
) VALUES (
    1,
    'Cart 102 has the largest discount value at 36.00, and Office Chair contributes 30.00 of that value.',
    'Review the Office Chair discount before changing lower-impact products.'
);

SELECT
    finding,
    recommendation
FROM analysis_findings;

That closes the loop. Your database now connects ranked discount evidence to an action the trading manager can defend.

Secret mission

Build a Reusable Discount Risk View

Your ranking already shows where discount value is concentrated. Package the calculation into a reusable view that labels every cart as High, Review, or Normal whenever you run the script.

Clean Up Your Resources

This project creates no billable cloud resources. Decide whether to keep the local files, pause by closing the open applications, or delete the files entirely.

Resources you used:

Local SQLite database retail_cart_analysis.db containing the project tables, stored finding, and reusable cart_discount_risk view.
Local retail_cart_analysis.sql script that recreates the core database analysis.
Local secret_mission.sql script that recreates the discount risk view.

Keep everything running

No action needed. Choose this if you are still demonstrating the analysis or plan to extend it.

Keep retail_cart_analysis.db so the tables, finding, and cart_discount_risk view remain ready to query.
Keep retail_cart_analysis.sql so you can recreate the core analysis.
Keep secret_mission.sql so you can recreate the reusable risk view.

Pause - I'll come back to this later

Shut down the open applications to free up memory. Your database and scripts remain available without generating charges.

Close the Postman desktop app to end the signed-out API session.
Close DB Browser for SQLite to release the open database file.
Keep retail_cart_analysis.db in its current saved location.
Keep retail_cart_analysis.sql in its current saved location.
Keep secret_mission.sql in its current saved location.

Delete - I don't want to use this again

A clean slate removes the three local project files listed above. Your installed applications remain available for future work.

Close the Postman desktop app.
Close DB Browser for SQLite.
Delete retail_cart_analysis.db from its saved location.
Delete retail_cart_analysis.sql from its saved location.
Delete secret_mission.sql from its saved location.
Use Windows file search to confirm that retail_cart_analysis.db, retail_cart_analysis.sql, and secret_mission.sql return no matching local files.

Nice Work!

Outstanding work! Your end-to-end retail discount investigation turns a live cart response into an evidence-based business recommendation.

You've learned how to:

Frame a stakeholder question with measurable acceptance criteria. Inspect a live REST API request in Postman to identify the fields needed for the analysis.
Model nested JSON as related SQLite tables. Connect cart summaries to product lines with primary keys and foreign keys.
Use SQL to rank discount impact by cart and product. Store an evidence-based recommendation to review the Office Chair discount first.
Complete the optional Secret Mission by creating a reusable discount-risk view. Use CASE to classify carts as High, Review, or Normal.

Ready to quiz yourself?