Skip to content

Intermediate SQL Case Study: How to Analyze eCommerce Inventory (And Save The Day For Your Client)

Digital marketing is all about making data-driven decisions.

In order to do that, you have to understand and analyze the data you have. That’s where SQL and data analytics skills come in. By using these skills, you can make smart decisions that help grow your business.

Unfortunately, a lot of digital marketers never get started because they don’t see the full value of these critical skills:

  • Mistakes can be Costly: Without SQL and data analytics skills, you will miss important digital marketing trends in your data. Pre-canned reports from your vendors won’t always do the trick. This can lead to mistakes that cost your business money and make customers unhappy.
  • Get Ahead of the Competition: You can gain a competitive advantage by learning how to use SQL and data analytics for digital marketing efforts. You can make better decisions. And you can make decisions faster and more accurately than your competitors, which leads to success for your clients or your boss.
  • Data Overload: Without SQL skills, marketers risk being overwhelmed by data, missing key insights, and making flawed decisions that negatively impact their campaigns.
  • Frustration and Confusion: Without the ability to work with data, it’s easy to become frustrated and confused by the sheer amount of data you have to work with.

Learning SQL and data analytics can seem like an impossible task. As a digital marketer, you might feel like they don’t have the skills or knowledge to tackle it. You might not have the time or resources to devote to learning SQL and data analytics. You might feel like it’s not worth the investment.

Learning data analytics doesn’t have to be scary or overwhelming for digital marketers.

The truth is that anyone can learn SQL and data analytics. By following along with this case study, digital marketers will learn how to use SQL to track inventory levels for an online store. You’ll learn how to use data to make smart decisions that help grow your business and impress your clients or your boss.

Three parts to this case study:

  1. Problem statement
  2. Database Table Setup
  3. Questions & Answers

Let’s dive in and see what we can learn!

Problem Statement

The survival of a large online retailer is at stake due to inaccurate inventory tracking causing lost sales and unhappy customers.

As a digital marketer, your task is to use SQL to track and analyze the inventory levels of the retailer’s eCommerce store to save the day!

Database Table Setup

We will be working with two tables in this analysis:

​Follow along with the interactive SQL for this case study on DB Fiddle​

Table 1: products

This table contains information about the products sold by the eCommerce store. The fields in this table include:

  • product_id: unique identifier for each product
  • product_name: name of the product
  • product_category: category of the product
  • product_price: price of the product

Here is the SQL code to create the products table and insert some sample data:

CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(50),
product_category VARCHAR(20),
product_price NUMERIC(10,2)
);

INSERT INTO products (product_name, product_category, product_price)
VALUES ('Product A', 'Category 1', 19.99),
('Product B', 'Category 2', 29.99),
('Product C', 'Category 1', 39.99),
('Product D', 'Category 3', 49.99),
('Product E', 'Category 2', 59.99);

Table 2: inventory

This table contains information about the inventory levels of the products sold by the eCommerce store. The fields in this table include:

  • product_id: unique identifier for each product
  • inventory_date: date of the inventory count
  • inventory_level: number of units in inventory on the inventory date

Here is the SQL code to create the inventory table and insert some sample data:

CREATE TABLE inventory (
  product_id INT,
  inventory_date DATE,
  inventory_level INT
);

INSERT INTO inventory (product_id, inventory_date, inventory_level)
VALUES (1, '2022-01-01', 100),
       (2, '2022-01-01', 200),
       (3, '2022-01-01', 150),
       (4, '2022-01-01', 75),
       (5, '2022-01-01', 250),
       (1, '2022-01-02', 80),
       (2, '2022-01-02', 180),
       (3, '2022-01-02', 100),
       (4, '2022-01-02', 60),
       (5, '2022-01-02', 220),
       (1, '2022-01-03', 50),
       (2, '2022-01-03', 150),
       (3, '2022-01-03', 75),
       (4, '2022-01-03', 80),
       (5, '2022-01-03', 200);

That’s it for setup! Now we can work through some questions to analyze the results.

Case Study Questions & Answers

Question 1. What are the top 5 products with the highest inventory levels on the most recent inventory date?

SELECT p.product_name, i.inventory_level
FROM products p
JOIN inventory i ON p.product_id = i.product_id
WHERE i.inventory_date = (SELECT MAX(inventory_date) FROM inventory)
ORDER BY i.inventory_level DESC
LIMIT 5;

Answer

product_nameinventory_level
Product E200
Product B150
Product D80
Product C75
Product A50

Explanation

This SQL statement joins the products and inventory tables on the product_id field and selects the product_name and inventory_level fields.

It then filters the results to only include data from the most recent inventory date and sorts the results in descending order by inventory_level.

Finally, the LIMIT 5 clause is used to limit the output to the top 5 products with the highest inventory levels on the most recent inventory date.

Question 2. What is the total inventory level for each product category on the most recent inventory date?

SELECT p.product_category, SUM(i.inventory_level) AS total_inventory_level
FROM products p
JOIN inventory i ON p.product_id = i.product_id
WHERE i.inventory_date = (SELECT MAX(inventory_date) FROM inventory)
GROUP BY p.product_category;

Answer

product_categorytotal_inventory_level
Category 1125
Category 2350
Category 380

Explanation

This SQL statement joins the products and inventory tables on the product_id field and selects the product_category and the sum of the inventory_level fields for each category.

It then filters the results to only include data from the most recent inventory date and groups the results by product_category.

Question 3. What is the average inventory level for each product category for the month of January 2022?

SELECT p.product_category, AVG(i.inventory_level) AS avg_inventory_level
FROM products p
JOIN inventory i ON p.product_id = i.product_id
WHERE i.inventory_date >= '2022-01-01' AND i.inventory_date < '2022-02-01'
GROUP BY p.product_category;

Answer

product_categoryavg_inventory_level
Category 192.50
Category 2200.0
Category 371.67

Explanation

This SQL statement joins the products and inventory tables on the product_id field and selects the product_category and the average of the inventory_level fields for each category.

It then filters the results to only include data from the month of January 2022 and groups the results by product_category.

Question 4. Which products had a decrease in inventory level from the previous inventory date to the current inventory date?

SELECT i1.product_id, p.product_name, i1.inventory_level - i2.inventory_level AS inventory_diff
FROM inventory i1
JOIN inventory i2 ON i1.product_id = i2.product_id 
         AND i1.inventory_date = i2.inventory_date + INTERVAL '1 day'
JOIN products p ON i1.product_id = p.product_id
WHERE i1.inventory_level < i2.inventory_level;

Answer

product_idproduct_nameinventory_diff
1Product A-20
1Product A-30
2Product B-20
2Product B-30
3Product C-50
3Product C-25
4Product D-15
5Product E-30
5Product E-20

Explanation

This SQL statement self-joins the inventory table twice on the product_id field and the previous inventory date. It selects the product_id, product_name, and the difference between the inventory_level on the current inventory date and the previous inventory date.

It then joins the products table on the product_id field and filters the results to only include products where the inventory level has decreased from the previous inventory date to the current inventory date.

Question 5. What is the overall trend in inventory levels for each product category over the month of January 2022?

SELECT p.product_category, i.inventory_date, AVG(i.inventory_level) AS avg_inventory_level
FROM products p
JOIN inventory i ON p.product_id = i.product_id
WHERE i.inventory_date >= '2022-01-01' AND i.inventory_date < '2022-02-01'
GROUP BY p.product_category, i.inventory_date
ORDER BY p.product_category, i.inventory_date;

Answer

product_categoryinventory_dateavg_inventory_level
Category 12022-01-01125.0
Category 12022-01-0290.0
Category 12022-01-0362.5
Category 22022-01-01225.0
Category 22022-01-02200.0
Category 22022-01-03175.0
Category 32022-01-0175.0
Category 32022-01-0260.0
Category 32022-01-0380.0

Explanation

This SQL statement joins the products and inventory tables on the product_id field and selects the product_category, inventory_date, and the average of the inventory_level fields for each category and date. It then filters the results to only include data from the month of January 2022 and groups the results by product_category and inventory_date. Finally, it sorts the results by product_category and inventory_date. This query will show the overall trend in inventory levels for each product category over the month of January 2022.

That’s it!

In this case study, we’ve explored how SQL can help a big online store track and manage its inventory.

We learned how to create and use SQL tables, filter data using the WHERE clause, and group data using the GROUP BY clause. These skills can be super helpful for digital marketers, but not learning SQL could put you at a disadvantage.

Without knowing SQL, you might miss out on opportunities to grow your career and help your business succeed.

Leave a Reply

Your email address will not be published. Required fields are marked *