24 lines
696 B
SQL
24 lines
696 B
SQL
-- 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;
|