Meta-style Company ChallengeEasyVerified answerSQLite live

Accepted Friend Requests

Show all confirmed friendships with both users' full names and when the friendship was accepted.

  • Joins
  • Date analysis
  • Filtering
  • Sorting

Challenge brief

Understand the request

Social Graph Team is building a 'Friends Since' feature and needs a list of all confirmed friendships with their acceptance dates.

List accepted friendships with friend1_name, friend2_name, and accepted_at date.

Return

  • friend1_name (full name)
  • friend2_name (full name)
  • accepted_at

Constraints

  • Return accepted friendships only
  • Show the most recently accepted friendships first

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

friendships

  • friendship_idINTEGER
  • user_id_1INTEGER
  • user_id_2INTEGER
  • statusVARCHAR(20)
  • accepted_atDATETIME

users

  • user_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

The friendships table stores connections. The status must be 'accepted'. Both user_id_1 and user_id_2 reference users — join the users table twice with different aliases for each side.

Hint 2

JOIN friendships to users twice: u1 ON user_id_1 = user_id, u2 ON user_id_2 = user_id. WHERE f.status = 'accepted'. Concatenate names with || ' ' ||.

Hint 3

Start with the tables that establish the result grain for question 4, select the required output aliases, and add the remaining joins, filters, aggregation, and ordering one clause at a time.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT u1.first_name || ' ' || u1.last_name AS friend1_name, u2.first_name || ' ' || u2.last_name AS friend2_name, f.accepted_at FROM friendships f INNER JOIN users u1 ON f.user_id_1 = u1.user_id INNER JOIN users u2 ON f.user_id_2 = u2.user_id WHERE f.status = 'accepted' ORDER BY f.accepted_at DESC;

Why this works

friendship_id 5 (John to Sarah) has status = 'pending' and is excluded. The remaining 4 are 'accepted'. Two aliases (u1, u2) on the users table let you pull each person's name independently.

Success check

4 accepted friendships — most recent is Mike Wilson and Sarah Jones (May 2020)

Expected result

Use this output to verify values, aliases, ordering, and row count.

friend1_namefriend2_nameaccepted_at
Mike WilsonSarah Jones2020-05-15 13:00:00
Jane SmithSarah Jones2020-04-10 10:00:00
John DoeMike Wilson2020-03-25 15:30:00
John DoeJane Smith2020-02-20 12:00:00

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.