EY INTERVIEW QUESTION | Assign GroupID to each record using SQL

 --You are presented with a dataset of home listings, each with a unique Home ID and a Status. Your

--objective is to assign a grouping key to each record based on specific conditions. A new grouping key

--should be initiated every time a record has the status “New Listing” or “Relisted”. Each subsequent

--record, following either of these statuses, should inherit the same grouping key until the next

--occurrence of “New Listing” or “Relisted”.


DROP TABLE IF EXISTS HomeListings;

GO


CREATE TABLE HomeListings

(

ListingID  INTEGER PRIMARY KEY,

HomeID     VARCHAR(100),

[Status]     VARCHAR(100)

);

GO


INSERT INTO HomeListings (ListingID, HomeID, [Status]) VALUES 

(1, 'Home A', 'New Listing'),

(2, 'Home A', 'Pending'),

(3, 'Home A', 'Relisted'),

(4, 'Home B', 'New Listing'),

(5, 'Home B', 'Under Contract'),

(6, 'Home B', 'Relisted'),

(7, 'Home C', 'New Listing'),

(8, 'Home C', 'Under Contract'),

(9, 'Home C', 'Closed');

GO



SELECT * FROM HomeListings

Comments

Post a Comment

Popular posts from this blog

50 Essential SQL Questions to Land Your Dream Job

 CREATE TABLE worker (     first_name VARCHAR(50),     last_name VARCHAR(50),     department VARCHAR(50),     salary DECIMAL(10, 2),     joining_date DATE ); INSERT INTO worker (first_name, last_name, department, salary, joining_date) VALUES  ('John', 'Doe', 'HR', 55000, '2020-01-15'), ('Jane', 'Smith', 'Finance', 67000, '2019-03-22'), ('Robert', 'Johnson', 'IT', 72000, '2018-06-10'), ('Emily', 'Davis', 'Marketing', 59000, '2021-07-01'), ('Michael', 'Wilson', 'Sales', 63000, '2020-09-15'), ('Sarah', 'Brown', 'HR', 56000, '2022-02-19'), ('David', 'Miller', 'Finance', 68000, '2019-10-05'), ('Laura', 'Garcia', 'IT', 75000, '2020-11-22'), ('James', 'Martinez', 'Marketing', 60000, '2021-03-18'), ('Olivia...
Read more

How to find all the customers who placed orders on three consecutive days | SQL Scenario questions

  DML Script: DROP TABLE IF EXISTS Orders CREATE TABLE Orders ( customer_name VARCHAR(255), order_date DATETIME ); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-01'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-01'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-02'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-02'); INSERT INTO Orders (customer_name, order_date) VALUES ('Charlie', '2024-04-03'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-03'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-04'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-04'); INSERT INTO Orders (customer_name, order_date) VALUES ('Charlie', '2024-04-05'); INSER...
Read more

ACCENTURE SQL INTERVIEW QUESTION | Change the ProductIDs

 DROP TABLE IF EXISTS Products; CREATE TABLE Products (     ProductID INT PRIMARY KEY,     Product VARCHAR(255),     Category VARCHAR(100) ); INSERT INTO Products (ProductID, Product, Category) VALUES     (1, 'Laptop', 'Electronics'),     (2, 'Smartphone', 'Electronics'),     (3, 'Tablet', 'Electronics'),     (4, 'Headphones', 'Accessories'),     (5, 'Smartwatch', 'Accessories'),     (6, 'Keyboard', 'Accessories'),     (7, 'Mouse', 'Accessories'),     (8, 'Monitor', 'Accessories'),     (9, 'Printer', 'Electronics');
Read more