This advanced SQL exercise is part of the book: Advanced SQL – Practical Techniques and Use-Cases, which includes 100 day-to-day SQL challenges (and solutions), need-to-know advanced features, and key concepts.
Consider the following table:
CREATE TABLE friendships (friendship_id int IDENTITY(1,1) PRIMARY KEY, member_id_1 int, member_id_2 int) GO
The Friendships table describes relationship between different users, for example we can see that user_id 1 is a friend of user_id 2 , user_id 3 is a friend of user_id 2 , and so on.
By taking a closer look at the data, we can identify two problems :
- There are duplicate rows, for example friendship_ids 1 and 4 are redundant
- There are duplicate friendships, for example friendship ids 1 and 2 actually describe the same relationship
Your mission, should you choose to accept it 😉 is to fix those issues, and create a unique list of friendships.
My solution will be published in the near future.
CREATE TABLE friendships (friendship_id int IDENTITY(1,1) PRIMARY KEY, member_id_1 int, member_id_2 int) GO INSERT INTO friendships VALUES (1,2),(2,1),(3,2),(1,2),(2,3),(3,2),(4,1)
Not yet : -)