SQL Interview Question - Solution (Part - VI) |

#sql #dataengineers #dataanalyst #interview #datascience #sqlinterveiw
#sqlfunctions
My medium profile: / mahendraee204
My github: github.com/mah...
Here are the create and insert statements for the given data:
-------------------------------------------------------------------------------------------
create table bookings(
Booking_id varchar(5)
,Booking_date date
,userid varchar(5)
,Line_of_business varchar(10)
);
insert into bookings values ('b1','2022-05-23','u1','Flight'),
('b2','2022-05-27','u2','Flight'),('b3','2022-03-28','u1','Hotel'),
('b4','2022-02-11','u4','Flight'),('b5','2022-04-02','u1','Hotel'),('b6','2022-04-02','u2','Flight'),
('b7','2022-01-26','u5','Flight'),('b8','2022-03-06','u6','Hotel'),('b9','2022-05-06','u2','Flight'),
('b10','2022-07-10','u1','Flight'),('b11','2022-04-12','u4','Flight'),('b12','2022-05-16','u1','Flight'),
('b13','2022-08-19','u2','Flight'),('b14','2022-04-20','u5','Hotel'),('b15','2022-08-22','u6','Flight'),
('b16','2022-02-26','u4','Hotel'),('b17','2022-03-28','u2','Hotel'),('b18','2022-07-30','u1','Hotel');
('b19','2022-04-04','u4','Hotel'),
('b20','2022-05-06','u1','Flight');
create table users(userid varchar(5) ,Slot varchar(5))
insert into users values ('u1','s1'),('u2','s1'), ('u3','s1'),('u4','s2'),
('u5','s2'),('u6','s3'), ('u7','s3'),('u8','s3'),('u9','s3'), ('u10','s3');

[@VARUNTEJA73]

with cte as (select u.slot,datename(m,booking_date)as months,count(booking_id)as counts,dense_rank()over (partition by slot order by count(booking_id))ranks from bookings b join users u on b.userid=u.userid group by u.slot,datename(m,booking_date)) select slot,months from cte where ranks=1