SQL query to find the total ride of a driver & profit ride

15-day challenge
DAY-1
SQL Question-1
Date-17TH DEC 2022

-- 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 the same as the end location for the constitutive  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 REPLICATE('*',800) as'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