FastPrepSocial Network Relationship Statistics
Problems/Social Network Relationship Statistics

Social Network Relationship Statistics

IBM logoIBM● EasyINTERNOA
QuestionConstraints

Problem statement

Source note: The mapped image and caption describe different SQL tasks; this exercise follows the image. The image cuts off most of the schema, so the judged core task matches it at about 85%.

Generate a report of social-network profiles and their relationship statistics.

Return one row for every profile with these columns in order:

  1. full_name, formatted as last_name, one space, then first_name.
  2. email.
  3. total_relations, the total number of rows in relations for the profile.
  4. approved_relations, the number of relationships whose is_approved value is true.
  5. pending_relations, the number of relationships whose is_approved value is false.

Profiles with no relationship rows must still appear with all three counts equal to 0. Sort the result by full_name in ascending order, then by email in ascending order when full names are equal.

Table schema

MySQLPostgreSQLPandas

Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.

profiles

Social-network profile records.

ColumnTypeNullableDescription
idPKIntegerNoProfile ID.
first_nameTextNoProfile first name.
last_nameTextNoProfile last name.
emailTextNoProfile email address.

relations

Relationship rows belonging to profiles.

ColumnTypeNullableDescription
profile_idIntegerNoProfile that owns the relationship.
related_toTextNoIdentifier of the related profile or account.
is_approvedBooleanNoWhether the relationship is approved.

Foreign key: profile_id → profiles(id)

Expected result

Your query or function must return these columns.

ColumnTypeNullableDescription
full_nameTextNo—
emailTextNo—
total_relationsIntegerNo—
approved_relationsIntegerNo—
pending_relationsIntegerNo—

Row order: must match exactly. Numeric tolerance: 0.

Constraints

  • profiles.id is unique and identifies one profile.
  • Every relations.profile_id references an existing profiles.id.
  • relations.is_approved is either true or false.
  • Each row in relations counts as one relationship, even when related_to values repeat.
  • Profiles with no relationship rows have zero total, approved, and pending relationships.
  • Rows with equal full_name values are ordered by email in ascending order.

More IBM problems

  • Count Strictly Increasing Contiguous WindowsOA · Seen Nov 2026
  • Total Each Letter's Count in an Encoded StringOA · Seen Nov 2026
  • Count Servers With No Requests in a Time WindowOA · Seen Nov 2026
All IBM interview questionsSee IBM hiring insights