262. Trips and Users
Explanation
To solve this problem, we need to calculate the cancellation rate of requests with unbanned users each day between specified dates. We will filter out the banned users and their corresponding trips, then calculate the cancellation rate based on the remaining trips.
- Join the Trips table with the Users table on client_id and driver_id to filter out banned users.
- Count the total number of requests and the number of canceled requests for each day.
- Calculate the cancellation rate for each day by dividing the number of canceled requests by the total number of requests.
- Round the cancellation rate to two decimal points.
Time Complexity: O(n) Space Complexity: O(n)
# Write your Java solution here
SELECT request_at AS Day,
ROUND(SUM(CASE WHEN status = 'cancelled_by_client' OR status = 'cancelled_by_driver' THEN 1 ELSE 0 END) / COUNT(*), 2) AS 'Cancellation Rate'
FROM Trips t
JOIN Users u1 ON t.client_id = u1.users_id AND u1.banned = 'No'
JOIN Users u2 ON t.driver_id = u2.users_id AND u2.banned = 'No'
WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY request_at;Code Editor (Testing phase)
Improve Your Solution
Use the editor below to refine the provided solution. Select a programming language and try the following:
- Add import statement if required.
- Optimize the code for better time or space complexity.
- Add test cases to validate edge cases and common scenarios.
- Handle error conditions or invalid inputs gracefully.
- Experiment with alternative approaches to deepen your understanding.
Click "Run Code" to execute your solution and view the output. If errors occur, check the line numbers and debug accordingly. Resize the editor by dragging its bottom edge.