Leetcode SQL - 262. Trips and Users

Database Description:

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 Userstable. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).

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

Question:

Write a SQL query to find the cancellation rate of requests made by unbanned clients between Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.

You can find the details about this question in Leetcode website following this link below:

https://leetcode.com/problems/trips-and-users/#/description

Solution 1:

Step 1: Initialize Table

Step 2: Insert Values

Step 3: LEFT JOIN two tables contain the total number of orders per day and the count of cancelled orders, respectively. Both tables are also created using LEFT JOIN from the Trips and Users table.

NOTES:

It is important to use LEFT JOIN instead of JOIN here, because you would want count(*) to return 0 even if no values were found/NULL values. Using JOIN/INNER JOIN would not return count() = 0, rather it will simply ignore the row with NULL value. But here, since it is required to return 0 for days without cancellation, we chose to use LEFT JOIN.

There is a easier way to go about this problem, which is shown in Solution 2 below.

Solution 2:

NOTES:

The reason it only needs to use LEFT JOIN for once here is that you can use: SUM(IF(CONDITION, VALUE 1, VALUE 2)). This way, we can get the total number of not completed(cancelled) orders, which is a perfect replacement for what the first LEFT JOIN in solution 1 is for.

OUTPUT:

#Database #SQL #Leetcode