GeezerVison

WITH tail AS (
    SELECT lower(nullif(right(filename, position('.' in reverse(filename))), '')) AS last_ext,
           lower(filename) AS lf
    FROM release_files
), exts AS (
    SELECT CASE
               WHEN last_ext IN ('.gz', '.bz2', '.xz', '.z', '.lz', '.lzma')
                    AND lf LIKE '%.tar' || last_ext
                   THEN '.tar' || last_ext
               ELSE COALESCE(last_ext, '(no extension)')
           END AS ext
    FROM tail
)
SELECT ext,
       count(*) AS files,
       round(100.0 * count(*) / sum(count(*)) OVER (), 4) AS pct_files
FROM exts
GROUP BY ext
ORDER BY files DESC;