Airbnb SQL Interview Question | Convert Comma Separated Values into Rows | Data Analytics

In this video we are going to discuss a SQL problem asked in Airbnb interview. The problem is called as Most Popular Room Types. In this problem we will learn how to convert comma separated values into row.

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

script:
create table airbnb_searches
(
user_id int,
date_searched date,
filter_room_types varchar(200)
);
delete from airbnb_searches;
insert into airbnb_searches values
(1,'2022-01-01','entire home,private room')
,(2,'2022-01-02','entire home,shared room')
,(3,'2022-01-02','private room,shared room')
,(4,'2022-01-03','private room')
;
#sql #airbnb #interview

[arpiteshsrivastava]

Note1: string_split and cross apply is not supported in MySQL.
Note2:Refer Below Solution for MySQL.

with room as
(select sum(case when filter_room_types like '%entire%' then 1 else 0 end) as en,
sum(case when filter_room_types like '%private%' then 1 else 0 end) as pr,
sum(case when filter_room_types like '%shared%' then 1 else 0 end) as sh
from airbnb_searches)
select 'entire home' as value, en cnt from room
union all
select 'private room' as value, pr cnt from room
union all
select 'shared room' as value, sh cnt from room
order by cnt desc;

Thanks Ankit!

[TheVaibhavdang]

Hi Ankit. Love the new concept.
Just a suggestion if you can explain the cross apply concept in depth, that would be really great.

[swathijaiganesh]

select unnest(string_to_array(filter_room_types, ', ')) as room_type, count(*) as search_count
from user_searches
group by room_type
order by search_count desc (in postgresql)

[proteetisaikia]

Hi Ankit, thanks for the concept. I tried solving it using cte and group by in MYsql

WITH cte AS (
SELECT
user_id,
SUM(CASE WHEN filter_room_types LIKE '%entire%' THEN 1 ELSE 0 END) AS Entire_home,
SUM(CASE WHEN filter_room_types LIKE '%shared%' THEN 1 ELSE 0 END) AS Shared,
SUM(CASE WHEN filter_room_types LIKE '%private%' THEN 1 ELSE 0 END) AS Private
FROM
airbnb_searches
GROUP BY
user_id
)
select value, count(value) as no_of_searches from
(
SELECT
CASE WHEN Entire_home = 1 THEN 'Entire_home' END AS value
FROM
cte
union all
SELECT
CASE WHEN Shared = 1 THEN 'Shared' END AS value
FROM
cte
union all
SELECT
CASE WHEN private = 1 THEN 'private' END AS value
FROM
cte) as A
where value is not null
group by value;

[rajendramaharjan]

Hi Ankit,
Sometimes the interviewer might ask to solve it with alternate solution without using the SQL built in function. For that case we can go with this solution. Let me know your thought

with cte as
(
select
[entire home] = sum(case when filter_room_types like '%entire home%' then 1 else 0 end),
[private room] = sum(case when filter_room_types like '%private room%' then 1 else 0 end),
[shared room] =sum(case when filter_room_types like '%shared room%' then 1 else 0 end)
from
#airbnb_searches
)
select
value,
cnt
from
cte
unpivot
(
cnt for value in ([entire home], [private room], [shared room])
) as upvt
order by cnt desc

[relaxingsounds]

SOLVED USING CTE IN MYSQL

WITH left_split AS (
SELECT user_id, substring_index(filter_room_types, ', ', 1) AS room_types
FROM airbnb_searches
),
right_split AS(
SELECT user_id, SUBSTRING_INDEX(filter_room_types, ', ', -1) AS room_types
FROM airbnb_searches
WHERE filter_room_types LIKE '%, %'
),
combine_left_And_Right AS (
SELECT * FROM left_split UNION ALL SELECT * FROM right_split
)
SELECT room_types, count(*) AS 'count' FROM combine_left_And_Right GROUP BY room_types ORDER BY count DESC ;

[ayushkashyap]

with cte as(select *, substring_index(filter_room_types, ", ", 1)as p1
from airbnb_searches
UNION
select *, substring_index(filter_room_types, ", ", -1)as p2
from airbnb_searches)

select p1 as value_, count(p1) as cnt
from cte
group by p1
order by cnt desc

[nachiketpalsodkar]

Another Way!!!

Without using the CROSS APPLY STRING_SPLIT() function

;
WITH cte as (
select *
, CASE WHEN CHARINDEX(', ', filter_room_types) = 0 THEN filter_room_types
ELSE SUBSTRING( filter_room_types, 1, LEN(LEFT(filter_room_types, CHARINDEX(', ', filter_room_types))) - 1) END as ROOM_TYPE
from airbnb_searches
UNION
select *
, CASE WHEN CHARINDEX(', ', filter_room_types) = 0 THEN filter_room_types
ELSE SUBSTRING( filter_room_types, LEN(SUBSTRING(filter_room_types, 1, END as ROOM_TYPE
from airbnb_searches )

select ROOM_TYPE, COUNT(ROOM_TYPE) as CNT
from cte
group by ROOM_TYPE
order by CNT DESC

[sathishv]

with cte as
((select user_id, substring_index(filter_room_types, ', ', 1) room_types from airbnb_searches) union
(select user_id, substring_index(filter_room_types, ', ', -1) room_types from airbnb_searches))
select room_types, count(user_id) count_ from cte group by room_types order by count_ desc;

[bullet_babu]

Hi Ankit,
Please check the below query for My SQl db

with cte as (
(select user_id, substring_index(filter_room_types, ', ', 1) as
filter_room_types from
airbnb_searches)
union
(select user_id, substring_index(filter_room_types, ', ', -1) as
filter_room_types from
airbnb_searches)
)
select filter_room_types, count(*) as count from cte group by filter_room_types order by count;

[nothingisreal]

Learned two new things. 1.) string_split - took way to long to add this 2.) ORDER BY can now use column aliases

[yashgarg-nr]

SELECT
room_type, COUNT(distinct unic_room_type) as no_of_searches
FROM
(SELECT user_id, room_type, concat(date_searched, '-', room_type,'-',user_id)as unic_room_type FROM
(select *, substring_index(filter_room_types, ', ', 1) AS room_type
FROM airbnb_searches
UNION ALL
select *, substring_index(filter_room_types, ', ', -1) AS room_type
FROM airbnb_searches) AS A) as B
group by room_type

[MCAMadeEasy]

WITH tbl1 AS (select *, explode(split(filter_room_types, ', ')) room_types FROM airbnb_searches)
SELECT room_types, count(1) cnt from tbl1 GROUP BY room_types ORDER BY cnt DESC

[namanverm]

What is cross apply? Could you please explain?

[MonkeyDLuffy]

Solution in PostgreSQL:

SELECT room_types, COUNT(*) AS total_searches
FROM (
SELECT
user_id,
date_searched,
regexp_split_to_table(filter_room_types, ', ') AS room_types
FROM airbnb_searches
) AS s
GROUP BY 1
ORDER BY total_searches DESC

[saipavanyaratapalli]

with cte as (
select *, substr(filter_room_types, 1, (instr(filter_room_types, ', ' )-1)) as abc, substr(filter_room_types, (instr(filter_room_types, ', ' )+1)) as b from airbnb_searches)
,
cte_1 as(
select user_id, date_searched, abc from cte
Where abc not in ("")
union all
select user_id, date_searched, b from cte
)
select abc, count(1) from cte_1
group by 1

[nakulbarot]

for Mysql
SELECT
CASE
WHEN filter_room_types LIKE '%entire home%' THEN 'entire home'
WHEN filter_room_types LIKE '%private room%' THEN 'private room'
WHEN filter_room_types LIKE '%shared room%' THEN 'shared room'
END AS room_type,
COUNT(*) AS count
FROM airbnb_searches
WHERE filter_room_types LIKE '%entire home%'
OR filter_room_types LIKE '%private room%'
OR filter_room_types LIKE '%shared room%'
GROUP BY room_type;

-- or
SELECT 'entire home' AS room_type, COUNT(*) AS count
FROM airbnb_searches
WHERE filter_room_types LIKE '%entire home%'
UNION ALL
SELECT 'private room' AS room_type, COUNT(*) AS count
FROM airbnb_searches
WHERE filter_room_types LIKE '%private room%'
UNION ALL
SELECT 'shared room' AS room_type, COUNT(*) AS count
FROM airbnb_searches
WHERE filter_room_types LIKE '%shared room%';

[batistamohd]

Hi Ankit, interesting questions & keep doing it.
My approach :

select room_type,
count(*)
from (
select user_id, date_searched,
substring_index(filter_room_types, ', ', 1)as room_type
from airbnb_searches
union
select user_id, date_searched,
substring_index(filter_room_types, ', ', -1) as room_type
from airbnb_searches
order by user_id
) temp
group by room_type

[rohantemgire]

with combined_cols as (
SELECT user_id, substring(filter_room_types, 1, position(", " IN filter_room_types)-1) type1 from airbnb_searches
union all
select user_id, substring(filter_room_types, position(", " IN filter_room_types)+1) type1 from airbnb_searches
)
select type1, count(type1) as count from combined_cols
where type1 not like "" group by type1 order by count desc;

[AmitYadav-sshj]

select room_0, count(room_0) cnt
from
(select *, split(filter_room_types, ', ')[0] as room_0
from airbnb_searches
union all
select *, split(filter_room_types, ', ')[1] as room_0
from airbnb_searches) T1
where room_0 is not null
group by T1.room_0
order by cnt desc