Compute percent of first-cancel users who never return

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”.