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_idproduct_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_categoryinventory_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 *

Tableau & Google Sheets Checklist

This one-page checklist PDF will help you learn how to upload your data into Google Sheets and build an awesome Tableau dashboard without wasting time.

You have Successfully Subscribed!

4 Things For Every Tableau Dashboard Creator

This free 4-page eBook PDF will walk you through the 4 most important steps for creating a Tableau dashboard

You have Successfully Subscribed!

Massive List of Tableau Tools

Tableau is even more powerful with these 20 tools you might not know about. The 2-page PDF includes a description and helpful links for each tool to get you started.

You have Successfully Subscribed!

Career Boosting Business Analytics Trends

Use this list as a road map to take you from boring data job to the GO-TO PERSON in your company that knows the answers to all the questions.

All set! Check your email :)

Massive List of Free Data Viz Books

List of 10 Awesome Data Visualization Books

All set! Check your email to download.

Tableau Dashboards
Beginner’s Guide

Get the full 2-page PDF with the 5 steps outlined in this guide + 3 bonuses to help you along the way

BONUS #1: 5 Beginner mistakes to avoid

BONUS #2: Video walkthrough

BONUS #3: Links to the the Google Sheet and final Dashboard

All set! Check your email :)

How to Create 6 Different Tableau Bar Charts (Free Guide)

Get the step-by-step guide to create all 6 different types of Tableau Bar Charts (with copy-and-paste calculated fields included) for free.

 

All set! Check your email :)

Massive List of Tableau Web Data Connectors

Download the list of 21 different Web Data Connectors for Tableau in this Google Sheet (save a copy for yourself, too!) 

All set! Check your email :)

Slope Graph Examples PDF + Dashboard Walkthrough Video

4-page PDF showing 10 slope graph examples + a video walkthrough

All set! Check your email to download the bonus materials.

Tableau Tutorial Chart Redesign

Download the Tableau Tutorial PDF Checklist & Workbook

The step-by-step checklist for fixing busted charts with Tableau PLUS the full workbook with all of my formatting and calculations for free.

All set! Check your email to download.

Download the TableauHighlight Tables PDF Guide

The step-by-step guide for creating Tableau highlight tables
(plus direct links to the YouTube video walkthrough and link to final Tableau workbook)

You have Successfully Subscribed!

Tableau Scatter Plots: The Complete Guide

Get the step-by-step checklist guide to create scatter plots with Tableau

 

All set! Check your email :)

Rebuild a Busted Chart with Tableau

Enter your best email to get the PDF version of this tutorial + the companion Tableau workbook for free

All set! Check your email to download.

Download the Bonus Materials

Enter your best email to get the PDF checklist version of this guide and the companion Tableau workbook for free.

All set! Check your email :)