Postgres - how to sort the row with the most intersecting array-CodePudding

How do I return a row with the most intersecting rows? Let's say, for instance, I have a table like this:

ID   INTERESTS
---- ------------------
1    {cat,kid,dragon}
2    {orange,apple,kid}
3    {dragon,cat,politics}
4    {php,java,javascript}

Say I wanted to sort the row with the most intersecting array as row 1: It would return something like

ID   INTERESTS              INTERSECTS
---- ---------------------- ------
3    {dragon,cat,politics}  2
2    {orange,apple,kid}     1
4    {php,java,javascript}  0

So far, I haven't seen any code samples that suggest this is possible in Postgres - I don't need the intersects column, but I do need it to be sorted with the most # of intersects.

CodePudding user response：

SELECT
  *
FROM
  yourTable   l
CROSS JOIN
  yourTable   r
CROSS JOIN LATERAL
(
  SELECT
    COUNT(*) AS interest_count
  FROM
  (
    SELECT UNNEST(l.interests)
    INTERSECT
    SELECT UNNEST(r.interests)
  )
    AS common_interests
)
  AS i

DEMO : https://dbfiddle.uk/?rdbms=postgres_14&fiddle=47fba6ed42cc22da486b17c68373d531