Find top-5 most similar rows across datasets
Company: Databricks
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
You can solve this in **SQL or Python**.
You are given two datasets with the same feature columns:
### Tables
**`target_rows`** (rows you want to match)
- `target_id` (STRING, PK)
- `f1, f2, ..., fk` (DOUBLE; numeric features; may contain NULLs)
**`candidate_rows`** (rows to search)
- `candidate_id` (STRING, PK)
- `f1, f2, ..., fk` (DOUBLE; numeric features; may contain NULLs)
### Task
For **each** row in `target_rows`, find the **top 5** most similar rows in `candidate_rows` using **all features**.
1. Define a similarity/distance metric using all features (e.g., **MSE** across features).
2. Compute the distance between each target row and candidate row.
3. Return the 5 candidates with the smallest distance per target.
### Distance definition (use this unless you clearly state an alternative)
Let the feature set be \(\{f_1,\dots,f_k\}\). Define distance as:
\[
\text{MSE}(t,c) = \frac{1}{k}\sum_{j=1}^{k} (t.f_j - c.f_j)^2
\]
Assumptions you should clarify in your solution:
- How you handle **NULL** features (e.g., drop those dimensions for that pair, or impute).
- Whether you need **feature scaling/standardization** before computing distances.
### Output
Return:
- `target_id`
- `candidate_id`
- `distance` (smaller = more similar)
- `rank` (1 to 5 per `target_id`, where 1 is most similar)
Order results by `target_id`, then `rank`.
Overview: This question evaluates understanding and implementation of similarity/distance metrics (e.g., MSE), data preprocessing issues such as handling NULLs and feature scaling, and the ability to perform efficient cross-dataset comparisons and top-k ranking.
You are given two datasets: a small target dataset and a larger candidate dataset. Using SQL, find the top 5 candidate rows most similar to a specific target row (target_id = 101), considering ALL features.
Similarity metric:
- Use Mean Squared Error (MSE) across the numeric features (f1, f2, f3, f4).
- If a feature is NULL in either the target row or the candidate row, exclude that feature from the MSE calculation (do not treat NULL as 0).
- If a candidate row has 0 comparable features (i.e., all features are NULL where the target is also NULL), exclude that candidate row.
Output columns:
- candidate_id
- comparable_feature_count
- mse
Sort by mse ascending, then candidate_id ascending, and return only the top 5 rows.
Tables
target_rows(target_id INT, f1 DECIMAL(10,2), f2 DECIMAL(10,2), f3 DECIMAL(10,2), f4 DECIMAL(10,2))
candidate_rows(candidate_id INT, f1 DECIMAL(10,2), f2 DECIMAL(10,2), f3 DECIMAL(10,2), f4 DECIMAL(10,2))
Hints
- CROSS JOIN the single target row (target_id=101) to all candidate rows, then compute per-feature squared errors.
- To ignore NULL features, compute both (1) the sum of squared errors and (2) the count of comparable features, and divide using NULLIF/filters.