// case studies · real world impact

Real World SQL

Explore how companies solve business‑critical problems with SQL – full schema, solution, and insight.

// SELECT A CASE STUDY

Product Sales Analysis: Revenue and Units Sold

🏢 AmazonBeginner

List all products with their total revenue, units sold, and profit margin to identify best-performing products.

Business Problem

An Amazon category manager wants a straightforward leaderboard of product performance heading into the weekly merchandising meeting: for every product, total units sold, total revenue, total profit, and profit margin percentage, so the team can quickly spot which SKUs deserve more homepage placement and which ones are quietly losing money despite decent sales volume.

Dataset & Schema

Product data with product_id, product_name, category, cost_price, selling_price; sales with sale_id, product_id, units_sold, sale_date.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT,
  category TEXT,
  cost_price DECIMAL(10,2),
  selling_price DECIMAL(10,2)
);
CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  product_id INTEGER,
  units_sold INTEGER,
  sale_date DATE,
  FOREIGN KEY(product_id) REFERENCES products(product_id)
);

SQL Solution

SELECT 
  p.product_id,
  p.product_name,
  p.category,
  p.cost_price,
  p.selling_price,
  SUM(s.units_sold) as total_units_sold,
  ROUND(SUM(s.units_sold * p.selling_price), 2) as total_revenue,
  ROUND(SUM(s.units_sold * (p.selling_price - p.cost_price)), 2) as total_profit,
  ROUND((p.selling_price - p.cost_price) / p.cost_price * 100, 2) as profit_margin_pct
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_id, p.product_name, p.category, p.cost_price, p.selling_price
ORDER BY total_revenue DESC;

Explanation

Step-by-step Solution:

1

SELECT product details: product_id, product_name, category, cost_price, selling_price.

2

Calculate total_units_sold using SUM(s.units_sold) to add up all units sold for each product.

3

Calculate total_revenue using SUM(s.units_sold * p.selling_price). This multiplies units by selling price per unit.

4

Calculate total_profit using SUM(s.units_sold * (p.selling_price - p.cost_price)). This is the actual profit per unit times quantity.

5

Calculate profit_margin_pct as ((selling_price - cost_price) / cost_price * 100) to show percentage profit.

6

Use LEFT JOIN to connect products with sales. GROUP BY all product columns to aggregate per product.

7

ORDER BY total_revenue DESC to show top revenue-generating products first. This helps identify your bestsellers and profit drivers.