101. Unique Friendships

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 :

1. There are duplicate rows, for example friendship_ids 1 and 4 are redundant
2. 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.

 
Good luck!

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 : -)