Katkat
Back

Find Top-5 Similar Rows

DatabricksData Scientist · Technical Screen
HardData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This question tests the ability to implement a pairwise similarity comparison between rows using a custom distance metric while handling missing data and ranking results.

Problem Statement

You are given a dataset df containing user profiles with numerical features. Your goal is to find, for each target user in the dataset, the 5 other users who are most similar to them, based on the Mean Squared Error (MSE) of their common non-null features.

Requirements

  1. For each pair of rows (A, B), calculate the MSE using only features where both A and B have non-null values.
  2. If fewer than 2 features are common between two rows, consider them non-comparable (distance = infinity).
  3. Return a table mapping each user_id to its top 5 most similar user_ids, sorted by distance ascending. Break ties using the user_id lexicographically.

Function Signature

def get_top_similar(df: pd.DataFrame) -> pd.DataFrame

Input Format

  • df: A pandas DataFrame with columns user_id (string) and numerical feature columns (float).

Constraints

  • n (number of rows) <= 1,000.
  • m (number of features) <= 50.
  • Ignore user_id in the MSE calculation.

Example

Input:

user_idf1f2
A1.02.0
B1.22.1
C5.05.0

Output: For user A, the MSE with B is ((1.0-1.2)^2 + (2.0-2.1)^2) / 2 = 0.025. The output should rank B as the most similar to A.

Edge Cases

  • Rows with all null values.
  • Datasets where no rows have enough common features to compare.

Hints

Hint 1

Consider using a Cartesian product (cross-join) to generate all pairs, but be mindful of memory constraints given the row limit.

Hint 2

You can use a vectorized approach by iterating through features or by utilizing pandas `merge` / `melt` operations to align non-null values.

Hint 3

Ensure the final ranking logic uses a groupby-apply pattern to keep only the top 5 records per user.

Follow-up questions

  • How would you optimize this if the dataset size grows to 100,000 rows, making a cross-join computationally infeasible?
  • How would you handle categorical features in this similarity calculation (e.g., using Jaccard similarity or Gower's distance)?
  • If this were a production system, how would you cache these results to avoid recomputing similarity for users who haven't updated their profiles?