262. Trips and Users

November 19, 2022

262. Trips and Users

Trips and Users

SQL Schema

Create table If Not Exists Trips (id int, client_id int, driver_id int, city_id int, status ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client'), request_at varchar(50))

Create table If Not Exists Users (users_id int, banned varchar(50), role ENUM('client', 'driver', 'partner'))

Truncate table Trips

insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('1', '1', '10', '1', 'completed', '2013-10-01')

insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01')

insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('3', '3', '12', '6', 'completed', '2013-10-01')

insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('4', '4', '13', '6', 'cancelled_by_client', '2013-10-01')

insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('5', '1', '10', '1', 'completed', '2013-10-02')

insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('6', '2', '11', '6', 'completed', '2013-10-02')

insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('7', '3', '12', '6', 'completed', '2013-10-02')

insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('8', '2', '12', '12', 'completed', '2013-10-03')

insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('9', '3', '10', '12', 'completed', '2013-10-03')

insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('10', '4', '13', '12', 'cancelled_by_driver', '2013-10-03')

Truncate table Users

insert into Users (users_id, banned, role) values ('1', 'No', 'client')

insert into Users (users_id, banned, role) values ('2', 'Yes', 'client')

insert into Users (users_id, banned, role) values ('3', 'No', 'client')

insert into Users (users_id, banned, role) values ('4', 'No', 'client')

insert into Users (users_id, banned, role) values ('10', 'No', 'driver')

insert into Users (users_id, banned, role) values ('11', 'No', 'driver')

insert into Users (users_id, banned, role) values ('12', 'No', 'driver')

insert into Users (users_id, banned, role) values ('13', 'No', 'driver')

Table: Trips

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| client_id   | int      |
| driver_id   | int      |
| city_id     | int      |
| status      | enum     |
| request_at  | date     |     
+-------------+----------+

id is the primary key for this table.
The table holds all taxi trips. Each trip has a unique id, while client_id and driver_id are foreign keys to the users_id at the Users table.
Status is an ENUM type of ('completed', 'cancelled_by_driver', 'cancelled_by_client').

Table: Users

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| users_id    | int      |
| banned      | enum     |
| role        | enum     |
+-------------+----------+

users_id is the primary key for this table.
The table holds all users. Each user has a unique users_id, and role is an ENUM type of ('client', 'driver', 'partner').
banned is an ENUM type of ('Yes', 'No').

The cancellation rate is computed by dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day.

Write a SQL query to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between "2013-10-01" and "2013-10-03". Round Cancellation Rate to two decimal points.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Trips table:
+----+-----------+-----------+---------+---------------------+------------+
| id | client_id | driver_id | city_id | status              | request_at |
+----+-----------+-----------+---------+---------------------+------------+
| 1  | 1         | 10        | 1       | completed           | 2013-10-01 |
| 2  | 2         | 11        | 1       | cancelled_by_driver | 2013-10-01 |
| 3  | 3         | 12        | 6       | completed           | 2013-10-01 |
| 4  | 4         | 13        | 6       | cancelled_by_client | 2013-10-01 |
| 5  | 1         | 10        | 1       | completed           | 2013-10-02 |
| 6  | 2         | 11        | 6       | completed           | 2013-10-02 |
| 7  | 3         | 12        | 6       | completed           | 2013-10-02 |
| 8  | 2         | 12        | 12      | completed           | 2013-10-03 |
| 9  | 3         | 10        | 12      | completed           | 2013-10-03 |
| 10 | 4         | 13        | 12      | cancelled_by_driver | 2013-10-03 |
+----+-----------+-----------+---------+---------------------+------------+

Users table:
+----------+--------+--------+
| users_id | banned | role   |
+----------+--------+--------+
| 1        | No     | client |
| 2        | Yes    | client |
| 3        | No     | client |
| 4        | No     | client |
| 10       | No     | driver |
| 11       | No     | driver |
| 12       | No     | driver |
| 13       | No     | driver |
+----------+--------+--------+

Output: 
+------------+-------------------+
| Day        | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33              |
| 2013-10-02 | 0.00              |
| 2013-10-03 | 0.50              |
+------------+-------------------+

Explanation: 
On 2013-10-01:
  - There were 4 requests in total, 2 of which were canceled.
  - However, the request with Id=2 was made by a banned client (User_Id=2), so it is ignored in the calculation.
  - Hence there are 3 unbanned requests in total, 1 of which was canceled.
  - The Cancellation Rate is (1 / 3) = 0.33
On 2013-10-02:
  - There were 3 requests in total, 0 of which were canceled.
  - The request with Id=6 was made by a banned client, so it is ignored.
  - Hence there are 2 unbanned requests in total, 0 of which were canceled.
  - The Cancellation Rate is (0 / 2) = 0.00
On 2013-10-03:
  - There were 3 requests in total, 1 of which was canceled.
  - The request with Id=8 was made by a banned client, so it is ignored.
  - Hence there are 2 unbanned request in total, 1 of which were canceled.
  - The Cancellation Rate is (1 / 2) = 0.50

Approach) Easy solution! Broken into two steps

We only need to perform aggregation on trip tables!
Eliminate banned users from the equation.
Inner join for valid drivers and valid clients to gather all the trips that count as valid.
I added a case when to determine if the trip is canceled to avoid a filter at the final stop it should be possible to optimize this case when.
Finally, we group by date and find the rate sum(is_cancelled) / count(1)

# step 1 - get all valid users
with valid_users as
(
    select
        *
    from
        users
    where
        banned <> "Yes"
),
# step 2 - get all the valid(non-banned) trips
valid_trips as
(
    select
        t.*,
        case 
            when t.status like "cancelled%" then 1
            else 0
        end as is_cancelled
    from
        trips t
        inner join valid_users v1
        on (t.driver_id = v1.users_id )
        inner join valid_users v2
        on (t.client_id = v2.users_id )
    
)
# step 3 - final operation
select
    request_at as Day,
    round(sum(is_cancelled) / count(1), 2) as "Cancellation Rate"
from
    valid_trips
where
    request_at between "2013-10-01" and "2013-10-03"
group by 
    request_at
order by 1

Approach) filtered COUNT

select 
    t.Request_at as Day,
    round(count(case when t.status!='completed' then t.id end) / count(*), 2) as "Cancellation Rate"
from Trips t 
join Users c on t.Client_Id = c.Users_Id
join Users d on t.Driver_Id = d.Users_Id
where 
    c.Banned = 'No' and 
    d.Banned = 'No' and
    t.Request_at between '2013-10-01' and '2013-10-03'
group by t.Request_at;

Approach) CTE

# In t1 we basically find everything in the Trip table associated with unbanned clients and drivers, and between 2013-10-01 and 2013-10-03.

with t1 as (select trips.*
       from trips
       where client_id not in (select users_id
                              from users 
                              where banned = "Yes")
       and driver_id not in (select users_id
                              from users 
                              where banned = "Yes")
       and request_at between "2013-10-01" and "2013-10-03")
 
 
 # use sum and case when we can find how many are cancelled each day. 
 # use count(*) we have the total number of trips each day. Then their division rounded to 2 decimal is the answer.
 select request_at as 'Day', 
        round(sum(case when status = "completed" then 0 else 1 end)/count(*), 2) as 'Cancellation Rate'
        from t1
        group by 1;

Conclusion

That’s all folks! In this post, we solved LeetCode problem #262. Trips and Users

I hope you have enjoyed this post. Feel free to share your thoughts on this.

You can find the complete source code on my GitHub repository. If you like what you learn. feel free to fork 🔪 and star ⭐ it.

In this blog, I have tried to solve leetcode questions & present the most important points to consider when improving Data structure and logic, feel free to add, edit, comment, or ask. For more information please reach me here
Happy coding!

Search This Blog

Leetcode Solutions | Platform for learning to code