← Pinterest Interview Insights

Pinterest·Data Analyst·Technical Phone Screen·Intermediate

Intermediate
Jun 2026

Summary

Pinterest health-tech product analytics interview, one SQL question that looked clean on the surface but had a few layers to it once you got into the tie-breaking and the 'strictly after' condition. Felt like a take-home style problem delivered live.

Questions Asked (1)

Q1

Given an appointments table with statuses like cancelled, completed, no-show, etc., write a SQL query to find what percentage of users whose first appointment was cancelled never went on to book another returned or confirmed appointment afterward.

Product Analytics & MetricsData Modeling
Author's notes

The tie-breaking rule tripped me up a bit.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into two parts: first, identify each user's first appointment and check if it was cancelled; second, for those users, determine if they ever had a returned or confirmed appointment after that first appointment. Then compute the percentage of users who never had such a subsequent appointment.

Pro tip: Clarify the definition of 'returned or confirmed' statuses and consider edge cases like multiple first appointments or ties in appointment dates. Also, discuss how you would handle users with no subsequent appointments in the denominator.

1. Identify first appointment per user

Use a window function like ROW_NUMBER() partitioned by user_id and ordered by appointment date to find each user's first appointment. Ensure you handle ties appropriately.

2. Filter users whose first appointment was cancelled

From the first appointments, select only those where status = 'cancelled'. This gives the cohort of users for the analysis.

3. Find subsequent returned/confirmed appointments

For each user in the cohort, check if they have any appointment after their first appointment with status in ('returned', 'confirmed'). Use a LEFT JOIN or EXISTS subquery.

4. Compute the percentage

Count the number of users in the cohort who have no such subsequent appointment, divide by the total number of users in the cohort, and multiply by 100 to get the percentage.

Key Points to Mention

  • Use of window functions (e.g., ROW_NUMBER) to identify first appointment
  • Handling of ties in appointment dates (e.g., using additional ordering criteria)
  • Definition of 'returned or confirmed' statuses and how to filter them
  • Use of LEFT JOIN or NOT EXISTS to find users without subsequent appointments
  • Calculation of percentage with proper denominator (only users whose first appointment was cancelled)
  • Consideration of time frame or other filters (e.g., only appointments after a certain date)

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.