← Pinterest Interview Insights

Pinterest·Data Analyst·Technical Phone Screen·Intermediate

Intermediate
Jun 2026

Summary

Pinterest data analyst interview, one SQL question focused on cancellation behavior in a scheduling product. Pretty technical for an analyst role, more like a data engineering problem than the usual metrics stuff.

Questions Asked (1)

Q1

Given an appointments table with user IDs, scheduled timestamps, and a status field, write a SQL query to find the percentage of users whose first cancelled appointment was never followed by a returned or confirmed appointment (strictly after, same timestamp doesn't count).

Product Analytics & MetricsData Modeling
Author's notes

The 'strictly greater than' part is where I almost tripped up.

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 cancelled appointment timestamp; second, check if there exists any subsequent appointment with status 'returned' or 'confirmed' strictly after that timestamp. Then compute the percentage of users who have no such subsequent appointment.

Pro tip: Clarify the definition of 'first cancelled appointment'—it should be the earliest cancelled appointment per user, not just any cancelled appointment. Also, explicitly state how you handle ties (same timestamp) and users with no cancelled appointments.

1. Identify first cancelled appointment per user

Use a window function like ROW_NUMBER() partitioned by user_id and ordered by scheduled_timestamp to find the earliest cancelled appointment for each user.

2. Find subsequent returned/confirmed appointments

For each user, check if there is any appointment with status 'returned' or 'confirmed' and scheduled_timestamp strictly greater than the first cancelled timestamp.

3. Determine users with no subsequent appointment

Use a LEFT JOIN or NOT EXISTS to filter users who have no such subsequent appointment after their first cancellation.

4. Compute the percentage

Count the number of such users and divide by the total number of users (or total users with at least one cancelled appointment, depending on interpretation), then multiply by 100.

Key Points to Mention

  • Use of window functions (e.g., ROW_NUMBER, RANK) to identify the first cancelled appointment per user.
  • Handling of timestamp comparisons: strictly greater than (>) to exclude same timestamp.
  • Definition of 'returned or confirmed' statuses and how to filter them.
  • Use of LEFT JOIN or NOT EXISTS to find users without subsequent appointments.
  • Denominator choice: total users vs. users with at least one cancelled appointment—clarify with interviewer.
  • Edge cases: users with no cancelled appointments, multiple cancellations at the same timestamp, and data types for timestamps.

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