Amazon Product Performance

Top Products by Revenue Analysis

Business Context

At Amazon, understanding which products generate the most revenue is critical for inventory management, marketing strategy, and business planning. This analysis identifies our highest-performing products by revenue for the month of January 2023, helping our category managers make data-driven decisions about product promotions, restocking priorities, and supplier negotiations.

Technical Requirements

The primary objective is to develop a PostgreSQL query for the following:

Task: Find the top 3 products by total revenue in the month of January 2023.

Revenue Calculation: Revenue = Product Price × Quantity Sold

Important Considerations:

Only include orders with 'completed' status.
Only include orders placed in January 2023.
Round the total revenue to 2 decimal places.
The final output should list product ID, product name, and their total revenue.
Sort products in descending order by total revenue, then by product name alphabetically for ties.

Database Schema

The database consists of three tables: Products, Orders, and Order_Items.

-- Table: Products
CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

-- Table: Orders
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    status VARCHAR(50) -- e.g., 'completed', 'pending', 'cancelled'
);

-- Table: Order_Items
CREATE TABLE Order_Items (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    item_price DECIMAL(10, 2), -- Price at the time of order, can differ from current product price
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

Sample Data

Assume the following sample data for the tables:

Table: `Products`

product_id	product_name	price
101	Echo Dot (5th Gen)	49.99
102	Kindle Paperwhite	139.99
103	Fire TV Stick 4K	39.99
104	Anker Power Bank	25.99
105	Instant Pot Duo	89.00
106	Bose QuietComfort 45	279.00

Table: `Orders`

order_id	order_date	status
1	2023-01-05	completed
2	2023-01-10	completed
3	2023-01-12	pending
4	2023-01-15	completed
5	2023-01-20	cancelled
6	2023-01-25	completed
7	2022-12-28	completed
8	2023-02-02	completed
9	2023-01-08	completed
10	2023-01-18	completed

Table: `Order_Items`

order_item_id	order_id	product_id	quantity	item_price
1	1	101	2	49.99
2	1	104	1	25.99
3	2	102	1	139.99
4	2	106	1	279.00
5	3	103	1	39.99
6	4	106	2	279.00
7	4	101	3	49.99
8	5	105	1	89.00
9	6	102	2	139.99
10	6	103	5	39.99
11	7	101	1	49.99
12	8	105	2	89.00
13	9	105	3	89.00
14	10	106	1	279.00
15	10	104	2	25.99

Note on Pricing: For this problem, use the price from the Products table for revenue calculation. The item_price in Order_Items is provided for context but not used in this specific problem's revenue calculation.

Expected Output (based on sample data):

product_id	product_name	total_revenue
106	Bose QuietComfort 45	1116.00
102	Kindle Paperwhite	419.97
105	Instant Pot Duo	267.00

Top 3 Products by Revenue (Jan 2023)

MODERATE

Write a PostgreSQL query to find the top 3 products by total revenue generated from 'completed' orders placed in January 2023. Display the product ID, product name, and their total revenue, rounded to two decimal places, sorted by revenue in descending order (then by product name alphabetically for ties).

Solution (PostgreSQL)

Imagine you're a detective for Amazon sales: You want to find the 3 "superstar" products that made the most money in January 2023.

First, you only look at sales receipts (Orders) from January 2023 that were actually finalized ('completed' status).
Then, for each completed sale, you look at what items were sold (Order_Items) and how many of each (quantity).
You find the price of each item from the product catalog (Products table).
For each item sold, you calculate its mini-revenue: price × quantity.
Next, you group all these mini-revenues by product. So, if "SuperWidget Pro" was sold 10 times in different orders, you add up all its mini-revenues to get its total January revenue.
Finally, you list all products by their total revenue, from highest to lowest, and pick the top 3.

PostgreSQL Query

-- Find the top 3 products by total revenue in January 2023
SELECT
    p.product_id,
    p.product_name,
    ROUND(SUM(p.price * oi.quantity), 2) AS total_revenue
FROM
    Products p
JOIN
    Order_Items oi ON p.product_id = oi.product_id
JOIN
    Orders o ON oi.order_id = o.order_id
WHERE
    o.status = 'completed'
    AND o.order_date >= '2023-01-01'
    AND o.order_date <= '2023-01-31'
GROUP BY
    p.product_id, 
    p.product_name
ORDER BY
    total_revenue DESC,
    p.product_name ASC
LIMIT 3;

Explanation of the PostgreSQL Query:

1. Joining Tables:

FROM Products p: Starts with the Products table, aliased as p.
JOIN Order_Items oi ON p.product_id = oi.product_id: Connects each product to its occurrences in order line items.
JOIN Orders o ON oi.order_id = o.order_id: Connects the order line items to their parent orders to access order status and date.

2. Filtering Orders (WHERE clause):

o.status = 'completed': Ensures only sales that were successfully processed are considered.
AND o.order_date >= '2023-01-01' AND o.order_date <= '2023-01-31': Filters orders to include only those placed within January 2023.
- An alternative PostgreSQL-specific way to filter for the month is AND DATE_TRUNC('month', o.order_date) = '2023-01-01'::DATE.

3. Calculating Revenue and Grouping:

SELECT p.product_id, p.product_name, ...: Selects the product identifiers.
ROUND(SUM(p.price * oi.quantity), 2) AS total_revenue:
- p.price * oi.quantity: Calculates the revenue for each individual line item.
- SUM(...): Aggregates these line item revenues for each product.
- ROUND(..., 2): Rounds the total revenue for each product to two decimal places.
GROUP BY p.product_id, p.product_name: This is essential for the SUM() aggregate function. It groups all rows with the same product ID and name together, so SUM() calculates the total revenue per unique product.

4. Ordering and Limiting Results:

ORDER BY total_revenue DESC: Sorts the grouped products by their calculated total_revenue in descending order (highest revenue first).
, p.product_name ASC: If two products have the exact same total revenue (a tie), this secondary sort orders them alphabetically by product name.
LIMIT 3: Restricts the output to only the top 3 products after sorting.

Using Products.price vs. Order_Items.item_price: The problem uses Products.price as per the instruction. If historical pricing at the moment of sale was required, oi.item_price would be used. It's always good to clarify pricing sources in real-world scenarios.

Your Turn! What are your thoughts on this problem or alternative ways to solve it? Share your own SQL query attempts or insights in the comments below!

Back to SQL