Skip to content
Advertisement

SQL – create SQL to join lists

I have the following table:

Add Data:

Find users who share addresses.

Expected Results:

Question:

How do I formulate a SQL query to obtain the expected result?

Much appreciated.

More info:

PostgreSQL 9.5.19

Advertisement

Answer

I don’t know if this is the most efficient method, but I can’t come up with something better right now.

I assume this will have a terrible performance on a larger table.


What does it do?

The first CTE collects all users that share at least one address. The output of the userlist CTE is:

Now this can be used to aggregate those user lists that share at least one address. The output of the shared_users CTE is:

As you can see we now have groups with the same list of usr_ids. In the final step we can group by those and aggregate the addresses, which will then return:

Online example

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement