Famous SQL Interview Question | First Name , Middle Name and Last Name of a Customer
Вставка
- Опубліковано 17 бер 2024
- In this video we will discuss a famous SQL interview problem where we need to segregate first name , middle name and last name from the customer name. I am going to explain the solution in a step by step manner.
script:
create table customers (customer_name varchar(30))
insert into customers values ('Ankit Bansal')
,('Vishal Pratap Singh')
,('Michael');
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer
![ОРБАН приехал к ПУТИНУ и "отчитался" о поездке в Киев 😁 [Пародия]](http://i.ytimg.com/vi/_0ukDovOWq8/mqdefault.jpg)
Join 100 days of SQL challenges where I have created hand picked SQL interview questions to sky rocket your SQL skills.
100daysofsql.com/
This also seems to work fine for me :
SELECT split_part(customer_name,' ',1) as first_name
,case when split_part(customer_name,' ',3) ='' then '' else split_part(customer_name,' ',2) end as second_name
,case when split_part(customer_name,' ',3) ='' then split_part(customer_name,' ',2) else split_part(customer_name,' ',3) end as third_name
from
customers
Not surprised endlessly impressive mastery that can only be envied. thanks
Liked the Logic and Explanation, superb Enjoyed. Thanks Ankit
Glad you liked it
Thank you Bro again explanation is next level
Great explanation Ankit
Great explanation sir 🙏🙏
Thanks for sharing good problems❤ worth watching
Glad you enjoyed it
Could you please post more Data Engineering SQL Questions . I think these questions are more alligned to Data Analyst which is cool also. Looking some super hard questions on Join and CTE,Subqueries.
My interview in 30 mins and was watching your Sql playlist ❤️
good luck!!
All the best 🙂
If u don't mind can you share your interview experience.
Thanks Ankit, your questions and the way you solve it, is amazing. It's very good way to build the logical understanding by wathcing your videos. Coming to this particular video. If the name has more than 3 words i.e. Vijay Pratap Singh Rathore. The query will become more complex. Is there any other method to solve it.
Once again, I appreciate your efforts in making these wonderful tutorials.
Yes it will become complex. Also you need to decide what you want to keep in the middle name and last name. In postgres and redshift we have a split part function which can make the solution easy . I hope other databases introduce that function.
Superb!
From this question, i learned, Char index, substring, left and right functions, you are SQL hero Ankit.
and how to use the substring with len and charindex to extract the first , middle and last name.
Keep learning 😊
Thank you so much
You're most welcome
In bigquery possible with split function with safe_offset(0),safe_offset(1) and safe_offset(2)
Hi Ankit ..your videos are good . Can you help how this can be achieved in oracle sql
Hi Ankit, i guess i have made it more simple, just check it.
select *,
case when len(empname)-len(replace(empname,' ',''))=1
OR len(empname)-len(replace(empname,' ',''))=2
then substring(empname,1,charindex(' ',empname)) ELSE EmpName end as F_Name,
case when len(empname)-len(replace(empname,' ',''))=2 then substring(empname,charindex(' ',empname)+1,CHARINDEX(' ',empname,CHARINDEX(' ',empname)+1)-charindex(' ',empname)) end as M_Name,
case when len(empname)-len(replace(empname,' ',''))=1
OR len(empname)-len(replace(empname,' ',''))=2 then substring(empname,charindex(' ',empname,5)+1,len(empname)) end as L_Name
from Employee
Love you 💓
Hi Ankit. To extract middle_name, can we write SUBSTRING(customer_name, first_space_position+1, second_space_position-1)
Your mic quality is not good. There is no clarity on what you are explaining. The information is very good and informative. Please do more videos like this.
This Works fine:
with cte as(
select *, ROW_NUMBER() over(order by customer_name) as rn from customers
),
cte1 as(
select value,
rn,
ROW_NUMBER() over(partition by rn order by rn) as rk from cte
cross apply string_split(customer_name, ' ')
),
cte2 as(
select *, case when rk = 1 then value end as firstname,
case when rk 1 and rk (select Max(rk) from cte1 where rn = a.rn) then value end as middle_name,
case when rk = (select Max(rk) from cte1 where rn = a.rn) and rk 1 then value end as lastname
from cte1 a
)
select STRING_AGG(firstname,'') as firstname,
STRING_AGG(middle_name,'') as middle_name,
STRING_AGG(lastname,'') as lastname
from cte2
group by rn
So if there are N number of words in the name, we will have to derive N - 1 number of space positions right?
In postgresql it's very easy, we can use split_part function.
Right but thats not available in most of other databases.
So true, your SQL interview question series so very helpful, thank you so much for that 🙏
Oracle function much better
brother electoral bond par ek baar join laga ke bataona problem aarahi hai meko i am beginer also ....because data duplicates.....
Hi Ankit,
Tried below solution in PostgreSQL, its working. Let me know your thoughts.
with cte as (
select customer_name,
length(customer_name)- length(replace(customer_name,' ','')) as no_of_spaces
from customers
)
select customer_name,
case when no_of_spaces >=0 then split_part(customer_name, ' ', 1) end as first_name,
case when no_of_spaces =1 then split_part(customer_name, ' ', no_of_spaces+1) end as last_name
from cte;
It's good but the split part function is not available in most other databases
another way to solve the same question without using string fuctions :
with cte as(
select customer_name, value
,row_number() over (partition by customer_name order by customer_name) as rnk
,count(value) over(partition by customer_name order by customer_name) as cnt
from customers
cross apply string_split(customer_name , ' ')
)
,cte2 as (
select customer_name
,case when rnk = 1 then value end as first_name
,case when rnk = 2 and cnt = 3 then value end as middle_name
,case when (rnk = 2 and cnt = 2) or (rnk = 3 or cnt = 3) then value end as last_name
from cte
)
select customer_name
, max(first_name) as first_name
, max(middle_name) as middle_name
, max(last_name) as last_name
from cte2 group by customer_name
in Postgres? with strpos or position? how is second space found?
What is meant by SQL and t SQL is it necessary for data analytics job
how much SQL and python is same in Data Engineering vs Data Analytics?
Using right function last name:
Case when no_of_spaces= 0 then null when no_of_spaces= 1 then right(customer_name,len(customer_name) - firstspaceposition) else right(customer_name,len(customer_name)- second space position)end as lastname from cte;
Perfect 💪
PySpark Version of this problem :
ua-cam.com/video/Zr6UXftnqOU/v-deo.html
Please do create AWS videos
Same
can middle name be extracted with LEFT or RIGHT?
with CTE as
(
select * from customers
cross apply string_split(customer_name,' ')
),
CTE2 as
(select *,count(*) over(partition by customer_name) as words_count,row_number() over(partition by customer_name order by (select null)) as rn from CTE)
select customer_name,max(case when words_count in (1,2,3) and rn=1 then value end) as first_name
,
max(case when words_count in (3) and rn=2 then value end) as middle_name
, max(case when words_count in (2) and rn=2 or words_count in (3) and rn=3 then value end) as last_name
from CTE2
group by customer_name
with cte as (
select customer_name,
length(customer_name)-length(replace(customer_name,' ','')) as no_of_spaces
from customers)
select *,
case
when no_of_spaces = 0 then customer_name
when no_of_spaces = 1 then substring_index(customer_name,' ',1)
when no_of_spaces = 2 then substring_index(customer_name,' ',1)
else null
end as first_name,
case
when no_of_spaces = 2 then substring_index(substring_index(customer_name,' ',-2),' ',1)
else null
end as middle_name,
case
when no_of_spaces = 1 then substring_index(customer_name,' ',-1)
when no_of_spaces = 2 then substring_index(customer_name,' ',-1)
else null
end as last_name
from cte
Bro just post some easy interview questions in sql
Ok next time
Is Syllabus for Data engineer and Data Analyst the same? How much are the similarities?
its different
@@Tech_world-bq3mw how much SQL and python is same in Data Engineering vs Data Analytics?
There is only medium & complex in your play list
with temp as( select customer_name,
length(customer_name)-length(replace(customer_name,' ','')) ct from customers)
select substring_index(customer_name,' ',1) as first_name
,if(ct=2,substring_index(substring_index(customer_name,' ',-2),' ',1) ,null) as middle_name
,if(ct=1 or ct=2,substring_index(customer_name,' ',-1),null) as last_name
from temp;
Those who are looking for MySQL version Solution - select *, substring_index(customer_name, ' ',1) as First , case when x >= 1 then substring_index(customer_name, ' ',-1) else null end as last ,
case when x >= 2 then substring_index(substring_index(customer_name, ' ',2),' ',-(x-1)) else null end as middle
from (select *, length(customer_name) - length(replace(customer_name,' ','')) as x from customers)x
can use locate as well
with cte as (select *,LEN(customer_name)-len(REPLACE(customer_name,' ','')) as l
,CHARINDEX(' ',customer_name) as f,
CHARINDEX(' ',customer_name,CHARINDEX(' ',customer_name)+1) as s from Customers)
select *,case when l=0 then customer_name else substring(customer_name,1,f-1) end as firstn,
case when l
;with cte as (
select *,
LEN(customer_name)-
len(REPLACE(customer_name,' ','')) as spaces,
CHARINDEX(' ',customer_name) as space_position,
CHARINDEX(' ',customer_name,CHARINDEX(' ',customer_name)+1) as space_position_2
from customers)
select *,
case when spaces=0 then customer_name
when spaces!=0 then left(customer_name,space_position-1) end as first_name
,
case when spaces>1
then SUBSTRING(customer_name,space_position+1,space_position_2-space_position) end as middle_name
,
case when spaces=1
then right(customer_name,len(customer_name)-space_position)
when spaces>1
then right(customer_name,len(customer_name)-space_position_2)
end as last_name
from cte
My solution :
with cte as (
select *,
substring_index(customer_name,' ',1) as a
,substring_index(substring_index(customer_name,' ',2),' ',-1) as b
,substring_index(customer_name,' ',-1) as c
,round((length(customer_name) - length(replace(customer_name,' ','')))/length(' '),0) as leng
from custs
)
select
a as first_name
,case when leng = 2 then b else null end as middle_name
,case when leng = 1 then b
when leng = 2 then c else null end as last_name
from cte;
This is the solution in PostgresSQL
select split_part(customer_name,' ',1) as first_name, split_part(customer_name,' ',2) as middle_name,
split_part(customer_name,' ',3) as last_name from customers;
If there is no middle name then your query will give last name as null and middle name will be last name.
MYSQL
SELECT
substring_index(customer_name," ",1) as first_name,
if (length(customer_name) - length(replace(customer_name, ' ', '')) > 1 ,
substring_index(substring_index(customer_name," ",2), " ",-1), Null) as second_name,
if (length(customer_name) - length(replace(customer_name, ' ', '')) >= 2 ,
substring_index(substring_index(customer_name," ",3), " ",-1), Null) as third_name
from customers
Hello Sir in my sql it is showing error. I have used INSTR Function
Cool
it's look difficult
Just confused from where u find such questions 😅
I feel the same !! lol 😀
@ankitbansal6 please review RIGHT function for last name
case when no_of_space=0 then null
--when no_of_space=1 then SUBSTRING(customer_name,first_space_position+1,first_space_position)
--when no_of_space=2 then SUBSTRING(customer_name,second_space_position+1,second_space_position)
when no_of_space=1 then RIGHT(customer_name,first_space_position)
when no_of_space=2 then RIGHT(customer_name,second_space_position-first_space_position-no_of_space)
end as last_name
*for mysql versions which does not have charindex and its equivalent or if there are multiple middle names* : use below approach
with cte_spaces as (
select * ,
length(customer_name)- length(replace( customer_name, ' ','')) as no_of_spaces
from customers
)
select * ,
substring_index(customer_name, " ",1) as first_name,
case when no_of_spaces > 1 then
substring_index(substring_index(customer_name, " ", (-1 * no_of_spaces)), " ", no_of_spaces -1)
end as middle_name,
case when no_of_spaces > 0 then
substring_index(customer_name, " ",-1) end as last_name
from cte_spaces
Logic fail if two or three space between name
You can trim to single space first
You logic will fail if there is space in starting or in ending of string.
In that case you can just trim the customer name in first cte and then as it is it will work
Hello Ankit , I've attempted another approach. Please inform me if it's functioning correctly in every corner case as well.
WITH CTC AS(
SELECT *,
LEN(CUSTOMER_NAME) - LEN(REPLACE(CUSTOMER_NAME,' ','')) AS NO_OF_SPACES,
LEFT(CUSTOMER_NAME, CHARINDEX(' ',CUSTOMER_NAME)) AS FIRST_NAME,
RIGHT(CUSTOMER_NAME, CHARINDEX(' ',REVERSE(CUSTOMER_NAME))) AS LAST_NAME
FROM CUSTOMERS
)
SELECT CASE WHEN NO_OF_SPACES = 0 THEN CUSTOMER_NAME ELSE FIRST_NAME END AS FIRST_NAME,
CASE WHEN NO_OF_SPACES = 2 THEN SUBSTRING(CUSTOMER_NAME,LEN(FIRST_NAME)+2, LEN(CUSTOMER_NAME)-LEN(FIRST_NAME)-LEN(LAST_NAME)) END AS MIDDLE_NAME,
CASE WHEN LEN(LAST_NAME) = 0 THEN NULL ELSE LAST_NAME END AS LAST_NAME
FROM CTC
MySQL Solution: with cte as (
select customer_name,
(length(customer_name) - length(replace(customer_name, ' ', '')) + 1) as cnt_of_words from customers
)
SELECT
CASE
WHEN cnt_of_words = 1 THEN customer_name -- Only one word, consider it as the first name
WHEN cnt_of_words = 2 THEN SUBSTRING_INDEX(customer_name, ' ', 1) -- Two words, consider the first word as first name
WHEN cnt_of_words = 3 THEN SUBSTRING_INDEX(customer_name, ' ', 1) -- Three words, consider the first word as first name
END AS first_name,
CASE
WHEN cnt_of_words = 3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(customer_name, ' ', 2), ' ', -1) -- Three words, consider the second word as middle name
END AS middle_name,
CASE
WHEN cnt_of_words >= 2 THEN SUBSTRING_INDEX(customer_name, ' ', -1) -- At least two words, consider the last word as last name
END AS last_name
FROM
cte;
SELECT
SUBSTRING_INDEX(name, ' ', 1) AS first_name,
CASE
WHEN LENGTH(name) - LENGTH(REPLACE(name, ' ', '')) > 1
THEN SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', -2), ' ', 1)
ELSE NULL
END AS middle_name,
SUBSTRING_INDEX(name, ' ', -1) AS last_name
FROM
your_table_name;
SELECT
customer_name,
CASE
WHEN size(split(customer_name, ' ')) = 2 THEN split(customer_name, ' ')[0]
ELSE split(customer_name, ' ')[0]
END AS First_Name,
CASE
WHEN size(split(customer_name, ' ')) = 2 THEN NULL
ELSE split(customer_name, ' ')[1]
END AS Middle_Name,
CASE
WHEN size(split(customer_name, ' ')) = 2 THEN split(customer_name, ' ')[1]
ELSE split(customer_name, ' ')[2]
END AS Last_Name
FROM customers;
what if we have more then 2 space then @ankitbansal6 ?
Hii Ankit bansal from my Side small request,
As told by you in UR LINKEDIN PROFILE, Supply chain Analytics,
Could you make one Video of SUPPLY CHAIN ANALYTICS BY TAKING PROCUREMENT SUPPLY CHAIN DATA SET AND Do DATA ANALYSIS so it will help me and even every audience please My Request Sir @ankitbansal6