Challenge DetailHardReviewed 2026-04-20

Trips and Users

Find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between '2023-10-01' and '2023-10-03'. Round to 2 decimal places.

JoinAggregationCase WhenDate Logic

This challenge now meets the current minimum standard for sitemap inclusion and independent indexing.

2025-12-22Hard

Trips and Users

Find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between '2023-10-01' and '2023-10-03'. Round to 2 decimal places.

Available Tables:

SQL Editor

What This Challenge Is Testing

This is a classic multi-filter reporting problem. In production analytics, the hard part is often not the formula itself, but making sure you filter the population correctly before aggregating. Fraud filters, test-account exclusions, and status normalization all follow the same pattern.

Topic Tags

JoinsAnalyticsCase When

Schema At A Glance

trips

idclient_iddriver_idcity_idstatusrequest_at

users

users_idbannedrole

Solution Approaches

Join users twice and aggregate cancellations

SELECT
  request_at AS Day,
  ROUND(
    SUM(CASE WHEN status <> 'completed' THEN 1 ELSE 0 END) * 1.0
    / 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 request_at BETWEEN '2023-10-01' AND '2023-10-03'
GROUP BY request_at

This is the clearest version for most readers. The joins enforce the unbanned-user constraint for both roles, and the CASE expression turns each trip into a 1 or 0 cancellation flag before averaging by day.

Filter eligible trips in a CTE first

WITH eligible_trips AS (
  SELECT
    t.request_at,
    t.status
  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 '2023-10-01' AND '2023-10-03'
)
SELECT
  request_at AS Day,
  ROUND(AVG(CASE WHEN status <> 'completed' THEN 1.0 ELSE 0.0 END), 2) AS "Cancellation Rate"
FROM eligible_trips
GROUP BY request_at

The CTE version separates row eligibility from the metric calculation. That can make the query easier to audit in real dashboards because you can inspect the filtered population before you compute the final rate.

Common Pitfalls

The biggest mistake is filtering banned users after aggregation or only filtering one side of the trip. Another common bug is integer division: if you divide two integers without forcing a decimal, many databases truncate the rate to 0 instead of a fraction.

Frequently Asked Questions

Why join the users table twice?

Each trip references two different roles from the same users table: the client and the driver. You need both joins because each role has its own banned status and both must be unbanned for the trip to count.

Can AVG replace SUM divided by COUNT?

Yes. If you convert completed versus cancelled into a 1.0 or 0.0 flag, AVG gives the same cancellation rate and is often easier to read.

Concept Guides

Use these guides when you want the fuller explanation behind the SQL pattern this challenge is testing.

SQL CASE Statements: Adding Logic to Your Queries

Learn how to add conditional logic to your SQL queries using CASE statements. Transform data and categorize results with ease.

Time Series Analysis with SQL: Trends, Growth, and Moving Averages

Turn raw timestamps into business insights. Learn how to calculate Month-over-Month growth and smooth out noisy data with 7-day moving averages.

Mastering SQL Joins: An Interactive Guide

Stop guessing what JOINs do. Learn Inner, Left, and Cross Joins with live, interactive examples and animations running right in your browser.

Topic Hubs

If this problem exposed a deeper weakness, use a topic hub to move through a curated cluster instead of random article hopping.

Analytics

Analytics SQL and business metrics

A practical cluster for cohorts, funnels, customer value, and the kinds of reporting questions teams actually ask every week.

Advanced SQL

Advanced query patterns

Use this path when you are moving beyond beginner SELECT queries into CTEs, window functions, and conditional logic.

Practice Path

If you want the rotating practice flow, open this same question from the daily stream. If you want interview-style packaging, continue into the interview pool after this rep.

Back to Challenges Open In Daily Browse Interviews

Related Challenges

Rising TemperatureMedium

Compare each weather row with the previous day and return only the IDs where temperature increased day over day.

Percentage of Users AttendedMedium

Calculate contest participation rate by dividing each contest’s registrations by the total user base, not by contest-local counts.

Friend Requests I: Overall Acceptance RateMedium

Calculate overall friend request acceptance rate while separating request volume from accepted pairs cleanly.

SQL Challenges/Trips and Users

Challenge DetailHardReviewed 2026-04-20

Trips and Users

Find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between '2023-10-01' and '2023-10-03'. Round to 2 decimal places.

JoinAggregationCase WhenDate Logic

This challenge now meets the current minimum standard for sitemap inclusion and independent indexing.

2025-12-22Hard

Trips and Users

Find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between '2023-10-01' and '2023-10-03'. Round to 2 decimal places.

Available Tables:

SQL Editor

What This Challenge Is Testing

Topic Tags

JoinsAnalyticsCase When

Schema At A Glance

trips

idclient_iddriver_idcity_idstatusrequest_at

users

users_idbannedrole

Solution Approaches

Join users twice and aggregate cancellations

SELECT
  request_at AS Day,
  ROUND(
    SUM(CASE WHEN status <> 'completed' THEN 1 ELSE 0 END) * 1.0
    / 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 request_at BETWEEN '2023-10-01' AND '2023-10-03'
GROUP BY request_at

Filter eligible trips in a CTE first

WITH eligible_trips AS (
  SELECT
    t.request_at,
    t.status
  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 '2023-10-01' AND '2023-10-03'
)
SELECT
  request_at AS Day,
  ROUND(AVG(CASE WHEN status <> 'completed' THEN 1.0 ELSE 0.0 END), 2) AS "Cancellation Rate"
FROM eligible_trips
GROUP BY request_at

Common Pitfalls

Frequently Asked Questions

Why join the users table twice?

Can AVG replace SUM divided by COUNT?

Yes. If you convert completed versus cancelled into a 1.0 or 0.0 flag, AVG gives the same cancellation rate and is often easier to read.

Concept Guides

Use these guides when you want the fuller explanation behind the SQL pattern this challenge is testing.

SQL CASE Statements: Adding Logic to Your Queries

Learn how to add conditional logic to your SQL queries using CASE statements. Transform data and categorize results with ease.

Time Series Analysis with SQL: Trends, Growth, and Moving Averages

Turn raw timestamps into business insights. Learn how to calculate Month-over-Month growth and smooth out noisy data with 7-day moving averages.

Mastering SQL Joins: An Interactive Guide

Stop guessing what JOINs do. Learn Inner, Left, and Cross Joins with live, interactive examples and animations running right in your browser.

Topic Hubs

If this problem exposed a deeper weakness, use a topic hub to move through a curated cluster instead of random article hopping.

Analytics

Analytics SQL and business metrics

A practical cluster for cohorts, funnels, customer value, and the kinds of reporting questions teams actually ask every week.

Advanced SQL

Advanced query patterns

Use this path when you are moving beyond beginner SELECT queries into CTEs, window functions, and conditional logic.

Practice Path

If you want the rotating practice flow, open this same question from the daily stream. If you want interview-style packaging, continue into the interview pool after this rep.

Back to Challenges Open In Daily Browse Interviews

Related Challenges

Rising TemperatureMedium

Compare each weather row with the previous day and return only the IDs where temperature increased day over day.

Percentage of Users AttendedMedium

Calculate contest participation rate by dividing each contest’s registrations by the total user base, not by contest-local counts.

Friend Requests I: Overall Acceptance RateMedium

Calculate overall friend request acceptance rate while separating request volume from accepted pairs cleanly.