Retail: Missing Barcodes
Walmart, Target, Amazon
Problem Statement
At Walmart, data integrity between the Point of Sale (POS) systems and the Master Product Catalog is vital for financial reporting. Occasionally, a product is sold before it is properly registered in the central system, leading to "orphan" sales.
Write a query to identify these orphan products. An orphan product is any product_id appearing in the sales_records table that does not exist in the master_catalog table.
Rules:
- You must return the unique product_id of every missing product.
- For each missing product, calculate the total number of times it was sold (total_orphan_sales) and the total revenue generated (total_lost_revenue).
- Revenue is calculated as: quantity * unit_price.
- Output columns: product_id, total_orphan_sales, total_lost_revenue.
- Sort by total_lost_revenue descending, then product_id ascending.
Tests your understanding of
Joins, Set Theory, Data Validation and Subqueries
Input Tables
master_catalog
| product_id(INTEGER) | product_name(VARCHAR) | category(VARCHAR) |
|---|---|---|
| 1 | Apple | Produce |
| 2 | Bread | Bakery |
| 3 | Milk | Dairy |
| 4 | Eggs | Dairy |
| 5 | Butter | Dairy |
sales_records
| sale_id(INTEGER) | product_id(INTEGER) | quantity(INTEGER) | unit_price(DECIMAL) |
|---|---|---|---|
| 101 | 1 | 2 | 0.5 |
| 102 | 99 | 1 | 15 |
| 103 | 2 | 1 | 2 |
| 104 | 88 | 5 | 10 |
| 105 | 99 | 2 | 15 |
| 106 | 77 | 1 | 5 |
| 107 | 88 | 1 | 10 |
Expected Output
| product_id(INTEGER) | total_orphan_sales(INTEGER) | total_lost_revenue(DECIMAL) |
|---|---|---|
| 88 | 2 | 60 |
| 99 | 2 | 45 |
| 77 | 1 | 5 |
Tags
MediumJoinsSet TheoryData ValidationSubqueries
20-30 min
42%