Mentorship User Pairs

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain
SQL
Difficulty
medium
Seniority
L4
Asked in
Meta

Problem

Mentorship matches pair two people from users who share an account status but sit in different recorded age brackets and signed up in different calendar years. The program coordinator needs every such match listed once, smaller user ID first.

Pair them up. Mentor and mentee.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

users: 10 of 200 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

Expected output

First 5 of 1698 rows
user_id_1user_id_2
10016396
10017560
19712613
29414262
3918151

Worked solution and explanation

This is unordered pair enumeration dressed up as mentorship matching. Every rule compares one user to another, so no scan or GROUP BY over single rows can express it: the table has to meet itself. Anyone writes the self-join. What separates candidates is two small choices: u1.user_id < u2.user_id instead of <>, and comparing the signup year instead of the raw signup_date. Use <> and every match comes back twice, once in each orientation. Compare dates and two people who joined in February and November of the same year count as a cross-year match.

Building the pairs

Step 1: Join users to itself on an ordered key

Alias users twice as u1 and u2 and join on u1.user_id < u2.user_id. That one inequality does three jobs: it drops a user paired with themselves, it keeps exactly one orientation of each match, and it puts the smaller ID in user_id_1, which is what the preview shows.

Step 2: Put the three matching rules on the pair

account_status must be equal, age_bucket must differ, and the signup year must differ. Each rule reads a value from both sides, so it can only be checked on the joined row, never on either copy of the table alone.

Step 3: Compare years, not dates

'Different years' means the calendar year, so extract it with STRFTIME('%Y', signup_date) on both sides. Two seed rows carry a time part ('2025-08-08 16:34:00'); taking the year first makes the format irrelevant. Comparing raw signup_date values would treat '2025-02-02' and '2025-11-11' as different and admit same-year pairs.

Self-join with inequality constraints for unique pairs
SELECT u1.user_id AS user_id_1, u2.user_id AS user_id_2
FROM users u1
JOIN users u2 ON u1.user_id < u2.user_id
WHERE u1.age_bucket <> u2.age_bucket
    AND u1.account_status = u2.account_status
    AND STRFTIME('%Y', u1.signup_date) <> STRFTIME('%Y', u2.signup_date)
Not-equal on the ID
u1.user_id <> u2.user_id removes self-pairs but returns every match twice, (100, 16396) and (16396, 100). The row count doubles and half the rows put the larger ID first.
Less-than on the ID
u1.user_id < u2.user_id keeps one orientation per match with the smaller ID first, so nothing has to be cleaned up afterwards with LEAST, GREATEST and DISTINCT.

Common follow-up questions