Leetcode SQL II

262. Trips and Users

The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).

+----+-----------+-----------+---------+--------------------+----------+
| 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|
+----+-----------+-----------+---------+--------------------+----------+

The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).

+----------+--------+--------+
| 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 |
+----------+--------+--------+

Write a SQL query to find the cancellation rate of requests made by unbanned users (both client and driver must be unbanned) between Oct 1, 2013 and Oct 3, 2013. The cancellation rate is computed by dividing the number of canceled (by client or driver) requests made by unbanned users by the total number of requests made by unbanned users.

For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.

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

Logic:

  1. Use CASE WHEN to calculate the nominator of the “cancellation rate”
  2. We have to create a temp table t whose Client_Id and Driver_Id would be the Users_Id in the Users table whose Status is not Banned
  3. Remember the double quote for the variable names with space in between, as well as “No”

Solution:

select t.Request_at as Day, round(sum(case when Status != "cancelled
then 1 else 0 end)/count(*),2) as "cancellation rate" from Trips as t where
t.Client_Id in (select Users_Id from Users where Banned = "No") and
t.Driver_Id in (select Users_Id from Users where Banned = "No") and
t.Request_at between "2013-10-01" and "2013-10-03"
group by Request_at;

Link

--

--

--

My homepage to record my thought processes for solving SQL and Algorithm questions

Recommended from Medium

Build custom Backend/Admin authentication in Laravel

Learning.com’s State of DevOps

How Data Science apply in Canto-pop?

Mobile Test Strategy Tips using XP

Reactor — Handle Error

Externalizing Dataweave Script — Why and How?

Week of April 3rd

Remote Mob Programing Ask the Experts: Key Takeaways

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Isabelle

Isabelle

In love with telling stories with data

More from Medium

Leetcode SQL 10 Days Study Plan(Day2) With Oracle SQL(Delete, Decode, Case When, Update)

Easy SQL Case Study: Filtering, If-Else logic, and Subquery

SQL Query optimizer

How to sort result set in custom order in SQL?