sql interview question

Question

Write an SQL query to return the most recently updated marital status and their most recent income increment percentage:

Customer_infomation_table
customer_id Updated_date Martial_status Income
1 2021-5-5 Single 5000
1 2021-5-5 Married 6000
3 2021-5-5 Single 4000
3 2021-5-5 Single 4500
4 2021-5-5 married 12000
Sample output table
customer_id Updated_date Martial Status Income Increment Percentage
1 2021-5-5 Married 20
3 2021-5-5 Single 12.5
4 2021-5-5 Married null

Answer 1

First, as you have given the data, and the question it's a bad question. You have multiple entries for the same employee and the same date. So how do you determine which of those entries come first? The question either needs to specify that or only have one entry for a given employee and date.

That said if they have different dates, then the following will work

Create Table Customer_infomation_table(customer_id int, Updated_date date, Martial_status varchar(10), Income decimal(11,2));  
Insert Customer_infomation_table(customer_id, Updated_date, Martial_status, Income) Values  
(1, '2021-5-5', 'Single',  5000),  
(1, '2021-8-14', 'Married', 6000),  
(3, '2021-5-5', 'Single',  4000),  
(3, '2021-7-12', 'Single',  4500),  
(4, '2021-5-5', 'married', 12000);  
  
With cteRn As   
(Select customer_id, Updated_date, Martial_status, Income,  
  Row_number() Over(Partition By customer_id Order By Updated_date Desc) As rn  
From Customer_infomation_table)  
  
Select r1.customer_id, r1.Updated_date, r1.Martial_status, r1.Income,  
  Cast((r1.Income * 100) / r2.Income As decimal(5,2)) - 100 As Increment_Percentage  
From cteRn r1  
Left Join cteRn r2 On r1.customer_id = r2.customer_id  And Coalesce(r2.rn, 2) = 2  
Where r1.rn = 1  
Order By customer_id;  
  

Tom

Answer 2

Are you asking us to provide you the answer to an interview question so you can get a job or something? That isn't going to happen, at least from me, as that would be cheating don't you think?