Inventory Management Dataset

• inventory_levels.product_id →references products.product_idmany to one: each Inventory levels row points to one Products row
• inventory_levels.warehouse_id →references warehouses.warehouse_idmany to one: each Inventory levels row points to one Warehouses row
• transactions.product_id →references products.product_idmany to one: each Transactions row points to one Products row
• transactions.warehouse_id →references warehouses.warehouse_idmany to one: each Transactions row points to one Warehouses row

1. Which products have the lowest stock levels across all warehouses?

   tables: products, inventory_levels

2. What is the average daily movement (in/out) for high-demand products?

   tables: products, transactions

3. How does inventory turnover vary by product category?

   tables: products, inventory_levels, transactions

4. Identify products with stock below 50 units in any warehouse.

   tables: products, inventory_levels

Table names match the SQLite file and the SQL script.

SELECT p.product_name, SUM(il.stock_quantity) AS total_stock
FROM products p
JOIN inventory_levels il ON p.product_id = il.product_id
WHERE il.stock_status = 'low_stock'
GROUP BY p.product_name
ORDER BY total_stock ASC
LIMIT 20;

SELECT p.product_name, SUM(t.quantity) AS total_units_moved
FROM products p
JOIN transactions t ON p.product_id = t.product_id
GROUP BY p.product_name
ORDER BY total_units_moved DESC
LIMIT 20;

SELECT w.warehouse_name, AVG(il.stock_quantity) AS average_stock
FROM warehouses w
JOIN inventory_levels il ON w.warehouse_id = il.warehouse_id
GROUP BY w.warehouse_name
ORDER BY average_stock DESC
LIMIT 20;

SELECT p.product_name, il.stock_status, COUNT(il.inventory_id) AS count
FROM products p
JOIN inventory_levels il ON p.product_id = il.product_id
WHERE il.stock_status != 'optimal'
GROUP BY p.product_name, il.stock_status
ORDER BY count DESC
LIMIT 20;

Excel

Load each table into a separate sheet. Use Power Query to join tables or XLOOKUP for lookups. Create a PivotTable on the inventory_levels sheet, with product and warehouse details, to analyze stock status and quantities.

Power BI

Create a star schema with a central fact table (inventory_levels or transactions) and dimension tables (products, warehouses). Set up relationships based on product_id and warehouse_id. Create measures for total stock, average stock movement, and inventory turnover.

SQL

Load the SQLite or SQL relational download. Use the defined keys and join paths (e.g., inventory_levels.product_id to products.product_id) to perform analyses. Ensure foreign key constraints are considered for accurate joins.

Python

Load CSV files into pandas DataFrames. Use DataFrame operations for joins, aggregations, and analysis. Libraries like pandas and matplotlib can be used for data manipulation and visualization.

• CSV (zip)One CSV file per table, zipped
• Excel workbookOne worksheet per table
• SQLite databaseA ready-to-query database file with every table
• Parquet (zip)One Parquet file per table, zipped
• SQL scriptCREATE TABLE with primary and foreign keys, then INSERTs
• CSVA single CSV file
• JSONA single JSON file