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
TheDataMonk 3 years 5 Answers 930 views Grand Master 0

Answers ( 5 )

  1. Sanjanaa
    3
    Reply

    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

    Best answer
  2. Raahul
    0
    Reply

    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)

  3. Jatin
    1
    Reply

    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

  4. khushnuma
    1
    Reply

    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

  5. gauravmani26
    0
    Reply

    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

Leave an answer

Browse
Browse