UBER Interview for Data Engineer Analytics position._SQL_Intreview_Question

 -- SQL interview question asked in UBER Interview for Data Engineer Analytics position. 

---Write a SQL query to find the total ride of a driver & profit ride (When the start location is same as end location for the consititve  ride for each driver )

create table drivers(id varchar(10), start_time time, end_time time, start_loc varchar(10), end_loc varchar(10));

insert into drivers values('dri_1', '09:00', '09:30', 'a','b'),('dri_1', '09:30', '10:30', 'b','c'),('dri_1','11:00','11:30', 'd','e');

insert into drivers values('dri_1', '12:00', '12:30', 'f','g'),('dri_1', '13:30', '14:30', 'c','h');

insert into drivers values('dri_2', '12:15', '12:30', 'f','g'),('dri_2', '13:30', '14:30', 'c','h');

----------------------------------------SAMPLE TABLE CREATED--------------------------

SELECT * FROM drivers

--------------------------------------------SOLUTION ----------------------------------------------------

SELECT A.id ,count (1) as 'Total Ride',
SUM (case when end_loc=A.[Next_Ride_Location] THEN 1 ELSE 0 end ) AS 'Profit_Rides'
FROM 
(
SELECT * ,LEAD(start_loc,1) OVER (PARTITION BY id ORDER BY start_time )as 'Next_Ride_Location'   FROM drivers
) A
GROUP BY A.id