Q: Maximize Prime Item Inventory
"Using historical daily sales data for Prime items, predict the minimum inventory needed to avoid stockouts based on an average daily sales figure."

Step by Step Answer:

You are given a sales_history table with:

• item_id
• sale_date
• quantity_sold
• is_prime_item (Yes/No)

You need to:

1. Focus only on Prime items.
2. Find the average daily sales for each Prime item.
3. Suggest a minimum inventory level so that stockouts can be avoided for 1 day (or longer, if specified).

Sample Data (sales_history):

Step-by-Step Explanation:

Step 1: Focus Only on Prime Items

We will filter where is_prime_item = 'Yes'.

We only want to calculate for Prime items.

Step 2: Calculate Average Daily Sales Per Prime Item

For each Prime item_id, find the average of quantity_sold.

Use AVG(quantity_sold) grouped by item_id.

Example:

For item 101:

• Sales = [10, 8, 12]
• Average = (10 + 8 + 12) ÷ 3 = 10 units per day

For item 103:

• Sales = [15, 18]
• Average = (15 + 18) ÷ 2 = 16.5 units per day

Step 3: Recommend Minimum Inventory

If you want to avoid stockouts, minimum inventory should at least cover 1 day's average sales.

So, the minimum inventory:

• For item 101 = 10 units
• For item 103 = 17 units (you usually round up to be safe)

You can also suggest inventory for 2 or 3 days by multiplying the average daily sales.

Example:
For 3 days for item 101 = 10 × 3 = 30 units

SQL Query:

To suggest minimum 1-day inventory:

SELECT
item_id,
CEIL(AVG(quantity_sold)) AS minimum_inventory_1_day
FROM
sales_history
WHERE
is_prime_item = 'Yes'
GROUP BY
item_id;

Explanation of SQL:

• WHERE is_prime_item = 'Yes' filters only Prime items.
• AVG(quantity_sold) calculates the average daily sales.
• CEIL(...) rounds up the average to the next whole number, to be safe.
• GROUP BY item_id groups the data by each item.

Expected Output:

Important Points:

• Always use CEIL() because partial units (like 16.5) are not practical — you cannot stock half a unit.
• You can easily calculate inventory for multiple days by multiplying:
  • For 7 days: CEIL(AVG(quantity_sold) * 7)
• Make sure the date range covers a normal period (not a special sale event unless you are planning for that).