Compute percent of first-cancelled users who never rebook

Q: Compute percent of first-cancelled users who never rebook

This question evaluates proficiency in SQL data manipulation—specifically use of window functions, CTEs, deduplication by earliest timestamp, temporal comparisons, and cohort-level aggregation—within the Data Manipulation (SQL/Python) domain for a Data Analyst role in health‑tech product analytics.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Q: What difficulty level is this interview question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Pinterest.

Q: What role is this question designed for?

This question is commonly asked for Data Analyst candidates at Pinterest during technical interviews.

Question

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

Quick Overview

Comments (0)