Anagrams

Remember the english_words table from the Palindromes challenge?

Table Name:

english_words

`column_name`	`type`
id	INTEGER
word	VARCHAR

This time, instead of selecting palindromes, we'll select anagrams. Anagrams are pairs of words where the letters of 1 word can be rearranged into the arrangement of the other word. For example, "state" and "taste" are anagrams because you can rearrange the letters of "state" to spell "taste" and vice versa.

Select all pairs of anagrams from english_words. Name the columns word_a and word_b. Do not consider a word an anagram of itself, e.g. "state" and "state" are NOT anagrams. Do not include duplicate pairs, e.g. if your result includes "state" and "taste", do not also include "taste" and "state" transposed. Always order a pair such that the word which is alphabetically first is word_a.

Try running a query…

	`word_a` `VARCHAR`	`word_b` `VARCHAR`
1	act	cat
2	below	elbow
3	bored	robed
4	cheap	peach
5	chin	inch
6	dreads	sadder
7	parsley	players
7 rows

You haven't solved this challenge yet!
Are you sure you want to reveal the answer?

WITH words_with_char_count AS (
  SELECT
    word,
    (
      SELECT json_object_agg(ch, count)
      FROM (
        SELECT
          ch,
          count(*)
        FROM regexp_split_to_table(word, '') ch
        GROUP BY ch
      ) c
    ) AS char_counts
  FROM english_words
),
counts_compared AS (
  SELECT
    a.word AS word_a,
    b.word AS word_b,
    (
      SELECT every(coalesce(char_counts_a.value = char_counts_b.value, FALSE))
      FROM json_each_text(a.char_counts) char_counts_a
      FULL OUTER JOIN json_each_text(b.char_counts) char_counts_b
        ON char_counts_a.key = char_counts_b.key
    ) AS counts_equal
  FROM words_with_char_count a
  CROSS JOIN words_with_char_count b
  WHERE
    -- this condition isn't logically required, but it helps efficiency a lot
    -- it prevents the need for the correlated subquery where the character
    -- counts are compared
    length(a.word) = length(b.word)
    AND a.word < b.word
)
SELECT word_a, word_b
FROM counts_compared
WHERE counts_equal;