Give me a list of drivers with a day on day decrease in completed rides, i.e drivers whose total number of completed rides ytd is less than the number of completed rides the day before

Question

Anónimo · Accepted Answer

Construct two temp tables, each with two columns(driver id and completed ride). 1st table completed rides is for the previous day and 2nd table is for the day before. Use GROUP BY and COUNT on booking table.

JOIN these two tables to form a table with four columns. (driver id,  number of previous completed rides, driver id, number of completed rides the day before). Compare the completed rides before the JOIN using ON or WHERE clause

Anónimo · Answer

Step 1: Calculate rides per day for every driver
create table test as
select driver_name, date, sum(rides) as sum_rides
from given_table
group by driver_name, date
order by driver_name, date
;

Step 2: Calculate the difference between consecutive rows using 'lag()' function
create table test_2 as
select driver_name, date,sum_rides, sum_rides - lag(sum_rides) over (partition BY driver_name order_by date) as difference
from test
;

Step 3: Select values where the number of rides decreased 
 create table test_3 as 
select driver_name,date, sum_rides, difference
from test_2
where difference >0
;

PS: Please check the syntax of 'lag()' function.

Grab

Pregunta de entrevista de Grab

Respuestas de entrevistas

Empresas seguidas

Búsquedas de empleo