Get the time for which a driver has logged into the driving app | Ola interview question
Question
Following is the table structure, I am running the code at 10 am on 26th May 2021
Driver_id , status, timestamp
123,Logged_in,24/05/2021 7:00 am
123,Logged_out,24/05/2021 19:00 pm
234,Logged_in,25/05/2021 6:00 am
234,Logged_out,25/05/2021 7:00 am
234,Logged_in,26/05/2021 9:00 am
Output
123 – 24/05/2021- 12 hrs
234 – 25/05/2021 – 1 hr
234 – 26/05/2021 – 1 hr
solved
1
SQL
3 years
5 Answers
930 views
Grand Master 0
Answers ( 5 )
Query performed as on 2pm (26/5/21)
1) Create two tables for login and logout containing driver_id, status and timestamp columns. Additionally can create another column which records current timestamp (currently).
CREATE TABLE login
(Driver_id INTEGER NOT NULL,
status VARCHAR(50) NOT NULL,
timestamp TIMESTAMP,
currently TIMESTAMP);
Similarly for logout table
2) PERFORM LEFT OUTER JOIN FUNCTION
SELECT login.driver_id,DATE(login.timestamp),
(CASE WHEN DATE(login.timestamp)=DATE(logout.timestamp) THEN logout.timestamp- login.timestamp
ELSE login.currently- login.timestamp
END) AS time FROM login
LEFT OUTER JOIN logout
ON login.driver_id=logout.driver_id
One way is to create two separate tables – one for logins and one for logouts and then join them on driver id. After this, we can group the rows according to date of the timestamp (obtained through strftime) and then driver_id. The output would show as the following columns in the order-
1. Date
2. Driver ID
3. Duration (log_out-log_in time)
With T1 AS
(
SELECT driver_id, status, convert(date, time_stamp) as log_date, time_stamp as login_time from T
WHERE Status=’Logged_in’
)
SELECT T1.driver_id, T1.log_date, datediff(hour,T1.login_time,T2.time_stamp from T as T2
Inner Join T1
on T1.Driver_id=T2.Driver_id and T1.log_date = convert(date, T2.time_stamp)
WHERE T2.status=’Logged_out’
GROUP by T1.driver_id, T1.log_date, T1.login_time,T2.time_stamp
1) Create two tables for login and logout containing driver_id, status and timestamp columns. Additionally can create another column which records current timestamp (currently).
CREATE TABLE login
(Driver_id INTEGER NOT NULL,
status VARCHAR(50) NOT NULL,
timestamp TIMESTAMP,
currently TIMESTAMP);
Similarly for logout table
2) PERFORM LEFT OUTER JOIN FUNCTION
SELECT login.driver_id,DATE(login.timestamp),
(CASE WHEN DATE(login.timestamp)=DATE(logout.timestamp) THEN logout.timestamp- login.timestamp
ELSE login.currently- login.timestamp
END) AS time FROM login
LEFT OUTER JOIN logout
ON login.driver_id=logout.driver_id
tablename is oladriver, columns are driver_id, status & datetime. A driver can login/logout more than 1 once.
With logintable as (SELECT driver_id, datetime as login_dt, RANK() OVER(partition by driver_id order by datetime) as log_rank from oladriver
where status = ‘login’),
logouttable as (SELECT driver_id, datetime as logout_dt, RANK() OVER(partition by driver_id order by datetime) as log_rank from oladriver
where status = ‘logout’),
t1 as (SELECT i.driver_id,login_dt, logout_dt, (case when logout_dt is null then “loggedin” ELSE DATE_DIFF(login_dt,logout_dt, “hours”) END) as duration from logintable i
left join logouttable o on i.driver_id=o.driver_id and i.log_rank=o.log_rank
ORDER by i.driver_id, login_dt, logout_dt),
t2 as (SELECT driver_id, login_dt, duration, CAST(login_dt AS DATE) as login_date from t1)
SELECT login_date, driver_id, count(login_date) as login_times, SUM(duration) from t2
where login_date < cast(now() as DATE)
GROUP by login_date, driver_id
ORDER by login_date desc, driver_id