SQL | Asked in Deloitte | Last Known Value Problem

📊 Customer Data Enrichment Using Last Known Value | Deloitte SQL Interview Problem In this SQL challenge inspired by a Deloitte interview question, we work with an Orders table that tracks customer purchases. Some records have missing region values, and the goal is to enrich the dataset by filling those missing regions using the customer's last known region value based on previous orders. This problem tests your understanding of: Window functions Handling NULL values Using Last Known Value logic for data enrichment Real-world data cleaning techniques used in analytics You will learn how to propagate the most recent non-null region for each customer across their subsequent orders. 🗂 Table Structure CREATE TABLE Orders ( OrderID VARCHAR(10) PRIMARY KEY, CustomerID VARCHAR(10) NOT NULL, OrderDate DATETIME NOT NULL, Region VARCHAR(20) NULL, OrderValue DECIMAL(10, 2) NOT NULL ); 📥 Sample Data INSERT INTO Orders (OrderID, CustomerID, OrderDate, Region, OrderValue) VALUES ('O1001', 'C001', '2023-01-10 00:00:00.000', 'North', 100.00), ('O1002', 'C001', '2023-02-15 00:00:00.000', NULL, 120.00), ('O1003', 'C001', '2023-03-20 00:00:00.000', NULL, 150.00), ('O1005', 'C002', '2023-01-25 00:00:00.000', 'South', 250.00), ('O1006', 'C002', '2023-02-10 00:00:00.000', NULL, 180.00), ('O1007', 'C003', '2023-03-01 00:00:00.000', 'East', 220.00), ('O1008', 'C003', '2023-03-15 00:00:00.000', NULL, 210.00), ('O1009', 'C003', '2023-04-01 00:00:00.000', NULL, 190.00); 🎯 Objective Write an SQL query to fill the missing Region values by using the last known non-null Region for each customer based on the order date. This type of problem commonly appears in data analyst and data engineer interviews at companies like Deloitte and tests practical data transformation skills.