-- List all players odered by scores considering all games.

SELECT row_number() OVER (ORDER BY sum(score) DESC) || ' ' || status_user || ' con ' || sum(score) || ' ⭐'
FROM (
  SELECT
    CASE
      WHEN accounts.domain IS NULL
      THEN '@' || accounts.username
      ELSE
        '@' ||  accounts.username || '@' || accounts.domain
    END AS status_user,
      CASE
      WHEN text ~ '1/6!' THEN 16
      WHEN text ~ '2/6!' THEN 8
      WHEN text ~ '3/6!' THEN 4
      WHEN text ~ '4/6!' THEN 2
      WHEN text ~ '5/6!' THEN 1
      ELSE 0
    END AS score
  FROM statuses, accounts
  WHERE text ~ 'ladeldia.cl' AND account_id = accounts.id
)
GROUP BY status_user
ORDER BY sum(score) DESC;