← Tools For Humanity Interview Insights

Tools For Humanity·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
May 2026

Summary

SQL-heavy technical screen for a Data Scientist role at Tools For Humanity. Two back-to-back problems on a microblogging schema, both requiring window logic and some careful thinking about what the denominator actually means. Nothing behavioral, just pure query writing.

Questions Asked (2)

Q1

Given a table of posts and replies with timestamps, find the number of distinct original post authors who have at least one post that received 2 or more replies within 7 days of the original post being created.

Product Analytics & MetricsData Modeling
Author's notes

The 7-day window tripped me up a bit because I kept second-guessing whether 'inclusive' meant I should use <= 7 or < 7 in the date diff.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the table schema and definitions (e.g., how replies are linked to posts, timestamp granularity). Then, use a self-join or window function to count replies per post within 7 days, filter posts with ≥2 replies, and finally count distinct authors of those posts.

Pro tip: Mention that you would validate the 7-day window using inclusive bounds and consider time zones; also, discuss how to handle posts with no replies efficiently.

1. Clarify schema and definitions

Confirm the structure of the posts and replies tables, including how replies reference original posts, timestamp formats, and what constitutes a 'reply within 7 days' (e.g., <= 7 days).

2. Identify replies within 7 days

For each reply, determine if it occurred within 7 days of the original post's creation timestamp. This can be done by joining replies to posts on post ID and filtering on the time difference.

3. Count replies per post and filter

Group by original post ID to count the number of qualifying replies, then filter to posts with at least 2 such replies.

4. Count distinct authors

From the filtered posts, select the distinct author IDs of the original posts and count them.

Key Points to Mention

  • Use of self-join or window functions (e.g., COUNT OVER PARTITION BY) to count replies per post.
  • Handling of timestamp differences and ensuring the 7-day window is correctly applied (e.g., using DATEDIFF or interval arithmetic).
  • Consideration of edge cases: replies exactly at the 7-day boundary, time zones, and posts with no replies.
  • Efficiency: indexing on post ID and timestamps, and avoiding unnecessary data shuffling.
  • Validation: checking results with a small sample or using a subquery to verify counts.
  • Clarification of 'distinct original post authors' – ensuring we count authors, not posts.

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

Q2

Among users who received at least one reply to any of their original posts, what percentage received replies from at least two distinct US-based repliers?

Product Analytics & MetricsData Modeling
Author's notes

The denominator clarification is the whole puzzle here.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into two parts: first, identify the cohort of users who received at least one reply to any original post; second, within that cohort, compute the percentage who received replies from at least two distinct US-based repliers. Use SQL with CTEs to join posts, replies, and user location data, applying DISTINCT counts and careful filtering.

Pro tip: Clarify the definition of 'original posts' and 'US-based repliers' upfront—e.g., whether original posts exclude replies and how to handle users with missing location data—to avoid ambiguity and show attention to detail.

1. Define and identify original posts

Filter the posts table to include only original posts (e.g., posts that are not replies to other posts). Ensure you understand the schema and how to distinguish original posts from replies.

2. Identify users who received at least one reply

Join original posts with replies to find all users who received at least one reply on any of their original posts. Use DISTINCT to avoid duplicates.

3. Determine US-based repliers

Join replies with user data to identify repliers located in the US. Be clear on how location is determined (e.g., profile country, IP-based) and handle missing or ambiguous data.

4. Count distinct US repliers per user

For each user in the cohort, count the number of distinct US-based repliers who replied to any of their original posts. Use COUNT(DISTINCT replier_id) with appropriate filters.

5. Compute the percentage

Calculate the percentage of users in the cohort who have at least two distinct US-based repliers. Divide the count of such users by the total cohort size and multiply by 100.

Key Points to Mention

  • Use of CTEs or subqueries to structure the multi-step logic clearly.
  • Handling of edge cases: users with no replies, replies from non-US users, and users with missing location data.
  • Importance of DISTINCT counts to avoid double-counting repliers who replied multiple times.
  • Definition of 'original posts' and how to identify them in the schema (e.g., parent_post_id IS NULL).
  • Potential data quality issues: location data accuracy, time zone vs. country, and bot accounts.
  • Validation of results: sanity checks like ensuring the percentage is between 0 and 100, and cross-checking with sample data.

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