SQL Interview Question: Identify Products Sold in Last 6 Months but Not in Last 30 Days with Stock

In this SQL scenario-based interview question, we’ll walk through how to identify products sold in the last 6 months but not in the last 30 days, while ensuring that the product's stock level is greater than its reorder threshold. This video will cover the usage of LEFT JOIN, DATEADD, and WHERE conditions to filter the data accordingly. This is a common real-world business problem for retail data analysts and anyone working with sales and inventory management systems.

#sqlserver
#sqlquery
#whatissql
#sqldatabase
#oracle
#oraclesql
#SQLtutorialforbeginners
#learnSQLfromscratch
#SQLfromzerotohero
#SQLbasicstoadvanced
#SQLqueries
#SQLfordataanalysis
#SQLjoinsexplained
#SQLsubqueriestutorial
#SQLaggregationfunctions
#SQLinterviewquestions
#SQLrealworldexamples
#SQLmasterclass
#SQLtutorial2024
#learnSQLfast
#SQLfordatascience
#SQLbeginnertoexpert

drop table if exists Product
drop table if exists Sales
-- Create Product Table
CREATE TABLE Product (
ProductID INT PRIMARY KEY,
ProductName NVARCHAR(50),
StockLevel INT,
ReorderThreshold INT
);

-- Insert Data into Product Table
INSERT INTO Product VALUES
(1, 'Laptop', 30, 20),
(2, 'Mouse', 50, 40),
(3, 'Keyboard', 15, 10),
(4, 'Printer', 60, 50),
(5, 'Monitor', 20, 25);

-- Create Sales Table
CREATE TABLE Sales (
SaleID INT PRIMARY KEY,
ProductID INT,
SaleDate DATE
);

-- Insert Data into Sales Table
INSERT INTO Sales VALUES
(1, 1, DATEADD(MONTH, -3, GETDATE())), -- Sold in the last 6 months
(2, 2, DATEADD(MONTH, -2, GETDATE())), -- Sold in the last 6 months
(3, 3, DATEADD(MONTH, -6, GETDATE())), -- Boundary case: exactly 6 months
(4, 4, DATEADD(MONTH, -7, GETDATE())), -- Sold more than 6 months ago
(5, 5, DATEADD(MONTH, -8, GETDATE())); -- Sold more than 6 months ago

select * from Product
select * from sales