Tight Collaborations
Problem
A research platform records which scholars have co-authored together.
Table: coauthors
| Column Name | Type |
|-------------|------|
| author1_id | int |
| author2_id | int |
(author1_id, author2_id) is the primary key for this table. Each row means the two authors have co-authored. Co-authorship is mutual but each pair is stored once with author1_id < author2_id.
A co-authorship between authors x and y is tight if x and y have at least three common co-authors (a common co-author is a third author who has co-authored with both x and y).
Write a query to find every tight co-authorship and the number of common co-authors. Return author1_id, author2_id, and common_count, with author1_id < author2_id, in any order.
Tables
Example rows — the live problem includes the full dataset.
| author1_id | author2_id |
|---|---|
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
Expected output
Your answer should return 3 rows with the columns author1_id, author2_id, common_count.
Starter code (SQL)
SELECT *
FROM coauthors;Solve this SQL question free
Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.
Solution & explanation
Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.
Sign up free to unlock