Abstract / Summary

Find the number of users who made at least one trip based on two tables about users and trips.

Note

The full problem description, including helpful examples, can be found on LeetCode.

This problem is ranked as

and has an acceptance rate of about 39% (800k+ submissions).

Typical patterns to solve this problem:

• Database

Every single line of code was written by me, 100% from scratch, between August 2023 and August 2024.
However, two years later I set up this website and used various AI tools to generate most of the walkthrough / explanations below.

See this dedicated page for more details.

Overview

The query computes the daily cancellation rate for rides that involve only non-banned users within a three-day window (October 1-3 2013). It isolates the relevant dates, filters out trips with banned participants, separates completed and non-completed trips, and finally derives the cancellation ratio per day. The use of common table expressions (CTEs) structures the logic into clear, reusable steps while allowing the optimizer to materialize intermediate results efficiently.

Identifying Banned Users

The first CTE, ban, extracts the identifiers of all users flagged as banned. By selecting only the users_id column and renaming it to id, the subsequent joins can reference a single column name, simplifying the NOT IN predicates later in the query.

WITH ban AS (
    SELECT users_id AS id
    FROM Users
    WHERE banned = 'Yes'
)

Determining the Relevant Days

The days CTE builds a distinct list of request dates that fall inside the target interval. Using DISTINCT eliminates duplicate dates early, ensuring that later joins operate on a minimal set of rows.

, days AS (
    SELECT DISTINCT request_at
    FROM Trips
    WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
)

Filtering Trips to Non-Banned Participants

The tours CTE selects all trips that satisfy three conditions: the client is not banned, the driver is not banned, and the request date belongs to the previously identified set of days. By applying the NOT IN filters before any aggregation, the query avoids processing irrelevant rows, which reduces I/O and CPU work.

, tours AS (
    SELECT *
    FROM Trips
    WHERE client_id NOT IN (SELECT id FROM ban)
      AND driver_id NOT IN (SELECT id FROM ban)
      AND request_at IN (SELECT request_at FROM days)
)

Counting Completed Trips

The completed CTE aggregates the filtered trips by date, counting only those with a status of 'completed'. Grouping is performed on the already reduced tours set, so the aggregation touches only the necessary rows.

, completed AS (
    SELECT request_at, COUNT(*) AS num
    FROM tours
    WHERE status = 'completed'
    GROUP BY request_at
)

Counting Cancelled (or Other) Trips

Similarly, the cancelled CTE counts trips whose status is anything other than 'completed'. This captures cancellations, no-shows, or any other non-completion outcomes.

, cancelled AS (
    SELECT request_at, COUNT(*) AS num
    FROM tours
    WHERE status != 'completed'
    GROUP BY request_at
)

Computing the Cancellation Rate

The final SELECT joins the list of days to the two count CTEs using LEFT JOINs. This ensures that days with only completed trips, only cancelled trips, or both are all represented. The expression

ROUND(COALESCE(ca.num / (ca.num + COALESCE(co.num, 0)), 0), 2)

calculates the ratio of cancelled trips to the total number of trips for each day. COALESCE supplies a zero when a count is missing, preventing division by zero and yielding a deterministic result of 0.00 for days without cancellations. The ROUND function formats the ratio to two decimal places, matching typical reporting requirements.

SELECT d.request_at AS `Day`,
       ROUND(COALESCE(ca.num / (ca.num + COALESCE(co.num, 0)), 0), 2) AS `Cancellation Rate`
FROM days d
LEFT JOIN completed co ON (d.request_at = co.request_at)
LEFT JOIN cancelled ca ON (d.request_at = ca.request_at)
WHERE ca.num IS NOT NULL OR co.num IS NOT NULL

Edge Cases Handled

The query gracefully handles days that have no trips at all by using the WHERE clause ca.num IS NOT NULL OR co.num IS NOT NULL. This filters out dates that appear in the days CTE but lack any associated records in either count CTE, preventing spurious rows with null values.

When a day contains only completed trips, ca.num is null, but the denominator ca.num + COALESCE(co.num, 0) resolves to the count of completed trips, and the numerator becomes null. The outer COALESCE then substitutes 0, yielding a cancellation rate of 0.00.

Conversely, if a day contains only cancelled trips, co.num is null; the inner COALESCE(co.num, 0) supplies zero, and the division simplifies to ca.num / ca.num, which evaluates to 1.00.

The use of COALESCE in the denominator also protects against a scenario where both counts are null, though the final WHERE clause eliminates such rows before the division occurs.

Performance Optimizations

The query’s structure leverages several optimization techniques:

• Early exclusion of banned users reduces the row count for all downstream operations. By applying the NOT IN predicates in the tours CTE, the engine avoids scanning banned participants during aggregation.
• The days CTE isolates the date range and deduplicates dates before any join, limiting the join cardinality.
• Using IN (SELECT request_at FROM days) in the tours CTE restricts the trip scan to the three relevant dates, allowing the optimizer to apply index range scans on request_at if such an index exists.
• Separate aggregation CTEs for completed and cancelled trips avoid a single GROUP BY with conditional counting, which can be less efficient on large datasets.
• LEFT JOINs with the pre-aggregated count tables avoid repeated scanning of the tours data when computing the final ratio.
• The final WHERE clause eliminates rows that would otherwise produce null results, reducing the amount of data that must be formatted and returned to the client.

Collectively, these choices keep the amount of data processed at each stage minimal, enable effective use of indexes, and allow the database engine to parallelize the independent CTEs where possible.

Imagine a big toy box filled with records of car rides. Some people are allowed to play, but others are in time-out because they broke the rules. The first part of this code looks through all the people to find those in time-out and sets them aside. Next, it looks at the calendar to pick out three special days where everyone wants to go on a trip. Then, it collects all the rides that happened on those days, making sure that neither the person asking for a ride nor the driver asking for a ride is in the time-out group. After that, it sorts these rides into two piles. One pile is for rides that finished perfectly, and the other pile is for rides that had to be cancelled. Finally, it does some math to figure out how many rides were cancelled compared to the total number of rides for each of those three days so we can see how often that happens.

Several LLMs ranked my code quality on a scale from 1 (very poor) to 10 (outstanding):

not analyzed yet

I wrote a basic crawler to find other solutions to that problem.
Obviously, I did that after I solved the problem myself.

In general, AlgoMonster and WalkCCC solutions are high-quality and always worth a look.
I haven't checked most of the other solutions, but they might be helpful as well.

And of course, the LeetCode forum is always a good place (or even the best) to look for alternative approaches.

(click on a column header to sort the table)

	author
author	solutions
www	AlgoMonster		C++	Java	Python	TS
	WalkCCC	SQL
	leetcode.ca	SQL			Python
	LeetcodeHelp		C++	Java	Python
	Progiez		C++	Java	Python
github	everthis	SQL
	suxrobGM	SQL
AlgoMonster	C++, Python, Java, TypeScript
WalkCCC	SQL
leetcode.ca	Python, SQL
LeetcodeHelp	C++, Java, Python
Progiez	C++, Java, Python
everthis	SQL
suxrobGM	SQL

There are a few extra pages giving you some insights into my setup:

• Tech Stack - my development environment and tools
• AI - how AI helped me create this website (NOT solving the problems)
• Progress - progression over time
• Quality - code quality