Compute percent of first-cancelled users who never rebook

You are interviewing for a health-tech product analytics role.

Assume the following table contains one row per appointment with its final status.

Table: appointments

• appointment_id (BIGINT, primary key)
• user_id (BIGINT, not null)
• scheduled_start_at (TIMESTAMP, not null) — when the appointment was scheduled to start
• status (VARCHAR, not null) — one of: 'cancelled', 'returned', 'confirmed', 'completed', 'no_show'
• status_updated_at (TIMESTAMP, not null) — when the final status was set

Definitions / assumptions

• All timestamps are in UTC.
• A user can have multiple appointments.
• A user’s first appointment is the appointment with the earliest scheduled_start_at (break ties by smallest appointment_id).
• A first-cancelled user is a user whose first appointment has status = 'cancelled'.
• A user is considered to have another future returned/confirmed appointment if they have any appointment with scheduled_start_at strictly after their first appointment’s scheduled_start_at and with status IN ('returned','confirmed').

Task Write a SQL query to compute the percentage of first-cancelled users who never have another future returned/confirmed appointment.

Required output (single row)

• first_cancelled_users (integer)
• never_returned_confirmed_users (integer)
• pct_never_returned_confirmed (decimal between 0 and 1)

(You may use CTEs and window functions.)

Compute percent of first-cancelled users who never rebook

Table appointments

column	type	constraints
appointment_id	BIGINT	PRIMARY KEY
user_id	BIGINT	NOT NULL
scheduled_start_at	TIMESTAMP	NOT NULL
status	VARCHAR	NOT NULL
status_updated_at	TIMESTAMP	NOT NULL

Sample data:

appointment_id	scheduled_start_at	status	status_updated_at	user_id
1001	2023-01-01T10:00:00Z	cancelled	2022-12-31T18:00:00Z	1
1002	2023-01-05T09:00:00Z	confirmed	2023-01-04T08:00:00Z	1
1003	2023-01-02T11:00:00Z	cancelled	2023-01-01T20:00:00Z	2
1004	2023-01-03T12:00:00Z	completed	2023-01-03T13:00:00Z	2
1005	2023-01-06T15:00:00Z	no_show	2023-01-06T16:00:00Z	2
1006	2023-01-02T10:00:00Z	cancelled	2023-01-01T10:00:00Z	3
1007	2023-01-02T10:00:00Z	confirmed	2023-01-01T11:00:00Z	3
1008	2023-01-03T09:00:00Z	cancelled	2023-01-02T08:00:00Z	4
1009	2023-01-03T10:00:00Z	returned	2023-01-03T08:30:00Z	4
1010	2023-01-01T08:00:00Z	completed	2023-01-01T09:00:00Z	5
1011	2023-01-04T13:00:00Z	cancelled	2023-01-03T18:00:00Z	6
1012	2023-01-10T09:00:00Z	no_show	2023-01-10T10:00:00Z	6
1013	2023-01-05T08:00:00Z	cancelled	2023-01-04T08:30:00Z	7
1014	2023-01-07T08:00:00Z	returned	2023-01-06T12:00:00Z	7

Expected output

A single-row result with counts of first-cancelled users, how many of them never have any later appointment with status in ('returned','confirmed'), and the fraction between 0 and 1.

first_cancelled_users	never_returned_confirmed_users	pct_never_returned_confirmed
6	3	0.5

Hints

1. Determine each user's first appointment by the earliest scheduled_start_at; break ties by the smallest appointment_id.
2. Filter to users whose first appointment status is 'cancelled'.
3. For those users, check for any appointment with scheduled_start_at strictly greater than their first and status IN ('returned','confirmed').
4. Use window functions (ROW_NUMBER) and EXISTS/self-join to flag users, then aggregate counts and compute the ratio.