Social Network Relationship Statistics
Reported by candidates from IBM's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The constraint that kills brute force in this IBM question, reported September 2026, is simple: you can't loop per profile and count relations one at a time. This is a SQL question, and the whole job is one join and one GROUP BY. You need every profile back, including the ones with zero relationships, plus a formatted name and three counts. If you've got an OA invite for this one, the trick is the LEFT JOIN and conditional counting. StealthCoder sits invisibly on your screen as a safety net if you blank on the syntax mid-assessment.
The problem
Generate a report of social-network profiles and their relationship statistics. Return one row for every profile with these columns in order: full_name, formatted as last_name, one space, then first_name. email. total_relations, the total number of rows in relations for the profile. approved_relations, the number of relationships whose is_approved value is true. 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. Tables profiles: id PK (Integer), first_name (Text), last_name (Text), email (Text) relations: profile_id (Integer), related_to (Text), is_approved (Boolean) 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.
Reported by candidates. Source: FastPrep
Pattern and pitfall
Start with profiles and LEFT JOIN relations on profiles.id = relations.profile_id. The left side matters because profiles with no rows must still show up with zeros. Group by profiles.id, first_name, last_name, email. Build full_name as last_name, a comma and a space... but read the spec again: last_name, one space, then first_name. So concatenate last_name, ' ', first_name with no comma. Count total with COUNT(relations.profile_id), not COUNT(*), because COUNT(*) gives 1 for an unmatched profile. Approved is SUM(CASE WHEN is_approved THEN 1 ELSE 0 END) or COUNT with FILTER, wrapped in COALESCE so NULLs become 0. Pending is the same with false. Order by full_name, then email. The pitfall is COUNT(*) and unwrapped SUMs. If you blank on any of it during the live OA, StealthCoder is the hedge that reads the schema and hands you the query.
If you see this problem in your OA tomorrow, the play is to recognize the pattern in 30 seconds. StealthCoder buys you that recognition.
You can drill Social Network Relationship Statistics cold, or you can hedge it. StealthCoder runs invisibly during screen share and surfaces a working solution in under 2 seconds. The proctor sees the IDE. They don't see what's behind it. Built by an Amazon engineer who passed his OA cold and still thinks the filter is broken.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass IBM's OA.
IBM reuses patterns across OAs. Built by an Amazon engineer who passed his OA cold and still thinks the filter is broken. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Social Network Relationship Statistics FAQ
How hard is the IBM Social Network Relationship Statistics question really?+
Easy to medium. There's one join, one group by, and three counts. Most people lose points on the zero-relation profiles or on the name format, not on the logic. If you know LEFT JOIN and conditional aggregation, it's a few minutes of work.
What's the trick to getting zero counts for profiles with no relations?+
Use LEFT JOIN from profiles to relations, then count a column from relations, not COUNT(*). COUNT(relations.profile_id) ignores NULLs and returns 0 for unmatched profiles. For the conditional sums, wrap them in COALESCE so you don't return NULL.
How do I count approved and pending separately?+
Use conditional aggregation. SUM(CASE WHEN is_approved THEN 1 ELSE 0 END) for approved, and the same with NOT is_approved for pending. Postgres also allows COUNT(*) FILTER (WHERE...). Either way, make sure unmatched profiles come out as 0, not NULL.
How should full_name be formatted?+
The spec says last_name, one space, then first_name. That means no comma. Concatenate with last_name || ' ' || first_name or CONCAT(last_name, ' ', first_name), depending on the dialect. Then order by that alias and email as a tiebreaker.
How do I prepare for this in 48 hours?+
Write this exact query from scratch twice. Then do two variants: a LEFT JOIN with a conditional count, and a group by with an order by on a computed column. Check your output on a profile with no relations, since that's the case the tests hit.