From the “Pointless SQL Queries” file…

Somehow, the question came up on alt.usage.english of the distribution of author surnames (in people’s personal libraries) over the letters of the alphabet. Since I have a library database, I came up with the followng crazy query to answer this question:

SELECT first_letter, total, authors, (100.0 * "count" * auth_count / total)::NUMERIC(4,1) AS "%"
  FROM (SELECT first_letter, "count", COUNT(author.name) AS auth_count, string_agg(author.name, '; ') AS "authors" 
        FROM (SELECT author_num, UPPER(SUBSTR(author.name, 1, 1)) AS first_letter, COUNT(*) as "count", RANK() OVER (PARTITION BY UPPER(SUBSTR(author.name, 1, 1)) ORDER BY COUNT(*) DESC) AS "rank"
              FROM book_author JOIN author ON (author.number = book_author.author_num)
              GROUP BY author_num, author.name) AS foo
          JOIN author ON (author_num = author.number)
        WHERE "rank" = 1 AND "count" > 1
        GROUP BY first_letter, "count") AS bar
    RIGHT OUTER JOIN (SELECT UPPER(SUBSTR(author.name, 1, 1)) AS first_letter, COUNT(*) AS "total"
                      FROM book_author JOIN author ON (author.number = book_author.author_num)
                      GROUP BY first_letter) AS baz
      USING (first_letter)
 ORDER BY first_letter

See if you can figure out what that query (with doubly-nested subselects) is doing, and if there’s a more efficient way to write it. Here are the results: