Compute percent of first-cancel users who never return
Company: Pinterest
Role: Data Analyst
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
You’re analyzing appointment behavior for a scheduling product.
## Table
### `appointments`
- `appointment_id` (STRING, PK)
- `user_id` (STRING)
- `scheduled_start_ts` (TIMESTAMP, UTC) — when the appointment was scheduled to happen
- `status` (STRING) — one of: `confirmed`, `cancelled`, `returned`
- Assume each appointment appears once with its final status.
## Definitions
- A user’s **first cancelled appointment** is the cancelled appointment with the earliest `scheduled_start_ts` for that user.
- A **future returned/confirmed appointment** means any appointment for the same user with:
- `scheduled_start_ts` strictly greater than the `scheduled_start_ts` of their first cancelled appointment, and
- `status IN ('returned', 'confirmed')`.
## Task
Write a SQL query to compute the **percentage of users whose first cancelled appointment is followed by _no_ future returned or confirmed appointment**.
### Output
Return exactly one row with:
- `percent_never_returned` (DECIMAL) — 100 * (number of qualifying users / number of users who have at least one cancelled appointment).
## Notes / Edge cases
- Users with no cancelled appointments are excluded from the denominator.
- If a user has a returned/confirmed appointment at the exact same timestamp as the first cancellation, do **not** count it as “future”.
Overview: This question evaluates a candidate's competency in data manipulation and temporal analysis using SQL/Python, focusing on computing user-level metrics and correctly ordering time-based events.
You are given an appointments table where each row is an appointment and its final status. A user is considered a "first-cancel user" if their earliest (first-ever) appointment has status = 'cancelled'.
For those first-cancel users, compute the percentage who never have any later appointment (strictly after that first appointment date) with status IN ('confirmed', 'completed').
Return one row with:
- total_first_cancel_users
- never_returned_users
- percent_never_returned (0-100, rounded to 2 decimals)
Tables
appointments(appointment_id INT, user_id INT, appointment_date DATE, status VARCHAR(20))
Hints
- Use ROW_NUMBER() to identify each user's first-ever appointment.
- After identifying first-cancel users, check for any later appointments with status confirmed/completed.