sql_question-4

create table booking_table (
    booking_id varchar(10),
    booking_date date,
    user_id varchar(10),
    line_of_business varchar(20)
);

insert into booking_table (booking_id, booking_date, user_id, line_of_business) values
('b1',  '2022-03-23', 'u1', 'Flight'),
('b2',  '2022-03-27', 'u2', 'Flight'),
('b3',  '2022-03-28', 'u1', 'Hotel'),
('b4',  '2022-03-31', 'u4', 'Flight'),
('b5',  '2022-04-02', 'u1', 'Hotel'),
('b6',  '2022-04-02', 'u2', 'Flight'),
('b7',  '2022-04-06', 'u5', 'Flight'),
('b8',  '2022-04-06', 'u6', 'Hotel'),
('b9',  '2022-04-06', 'u2', 'Flight'),
('b10', '2022-04-10', 'u1', 'Flight'),
('b11', '2022-04-12', 'u4', 'Flight'),
('b12', '2022-04-16', 'u1', 'Flight'),
('b13', '2022-04-19', 'u2', 'Flight'),
('b14', '2022-04-20', 'u5', 'Hotel'),
('b15', '2022-04-22', 'u6', 'Flight'),
('b16', '2022-04-26', 'u4', 'Hotel'),
('b17', '2022-04-28', 'u2', 'Hotel'),
('b18', '2022-04-30', 'u1', 'Hotel'),
('b19', '2022-05-04', 'u4', 'Hotel'),
('b20', '2022-05-06', 'u1', 'Flight');

create table user_table (
    user_id varchar(10),
    segment varchar(10)
);

insert into user_table (user_id, segment) values
('u1', 's1'),
('u2', 's1'),
('u3', 's1'),
('u4', 's2'),
('u5', 's2'),
('u6', 's3'),
('u7', 's3'),
('u8', 's3'),
('u9', 's3'),
('u10', 's3');


------------------------------------------------------------------------------------------------

Q1 . user booked between '2022-04-01' and '2022-04-30'

select 
	u.segment,
	count( distinct u.user_id ) as total_users,
	count( distinct case when booking_date between '2022-04-01' and '2022-04-30' then u.user_id else NULL end ) as total_booked_users
from 
	User_table u
left join 
	Booking_table b on u.user_id = b.user_id
group by 
	u.segment;

---------------------------------------------------------------------------------------------

Q2. find users who's first booking is Hotel.

select 
	*
from (
  select 
      *,
      row_number() over( partition by user_id order by booking_date ) as rn 
  from 
	  Booking_table
) as a
where
	a.rn = 1 and line_of_business = 'Hotel';

---------------------------------------------------------------------------------------------------------------


Q3.  write a query to calculate the days between first and last booking of the user_id = 1

select 
	user_id,	
	DATEDIFF(max(booking_date), MIN(booking_date)) as no_of_days
from 
	Booking_table
group by
	user_id;


---------------------------------------------------------------------------------------------------------------------


Q4 . Count the number of flight and hotel booking in each of the user segment for the year 2022

select
	u.segment,
    sum(case when b.line_of_business = 'FLight' then 1 else 0) as flight_count,
	sum(case when b.line_of_business = 'Hotel' then 1 else 0) as Hotel_count,
from 
	User_table u
left join 
	Booking_table b on u.user_id = b.user_id
group by 
	u.segment;


----------------------------------------------------------------------------------------------------------------------------