Определение самого сильного пароля для каждого пользователя

Условие

Даны две взаимосвязанные таблицы users и passwords. Требуется написать SQL‑запрос, который для каждого пользователя выводит:

• идентификатор пользователя;
• пароль с максимальным значением strength среди всех его записей. Если у пользователя несколько паролей имеют одинаковую максимальную надёжность, допускается вернуть любой из них.

CREATE TABLE users (
    id INT,
    name varchar(50)
);
INSERT INTO users (id,name) VALUES (1,'Ivan')
INSERT INTO users (id,name) VALUES (2,'Tom')
INSERT INTO users (id,name) VALUES (3,'Jonh')
INSERT INTO users (id,name) VALUES (4,'Daren')
INSERT INTO users (id,name) VALUES (5,'Oliver')
INSERT INTO users (id,name) VALUES (6,'Leo')
INSERT INTO users (id,name) VALUES (7,'Olivia')


CREATE TABLE passwords (
    id INT,
    userId INT,
    hash varchar(255),
    strength INT
);

INSERT INTO passwords (id,userId,hash,strength) VALUES (1,1 '123213abc123abc',15);
INSERT INTO passwords (id,userId,hash,strength) VALUES (2,1 '33333333a33b33',2);
INSERT INTO passwords (id,userId,hash,strength) VALUES (3,1 'aadsadadadwas',3);
INSERT INTO passwords (id,userId,hash,strength) VALUES (4,1 'wererqwrewrwrwe',4);
INSERT INTO passwords (id,userId,hash,strength) VALUES (5,1 '455aaaaaaa',7);
INSERT INTO passwords (id,userId,hash,strength) VALUES (6,1 '455aaaa564a',10);

Ответ

Пронумеруйте пароли каждого пользователя по убыванию strength и оставьте rn = 1. LEFT JOIN сохранит в выдаче пользователей без записей паролей.

WITH ranked AS (
    SELECT userId, hash,
           ROW_NUMBER() OVER (
               PARTITION BY userId
               ORDER BY CASE WHEN strength IS NULL THEN 1 ELSE 0 END,
                        strength DESC, id
           ) AS rn
    FROM passwords
)
SELECT u.id, r.hash
FROM users u
LEFT JOIN ranked r ON r.userId = u.id AND r.rn = 1;

Для пользователя 1 выбран первый hash со strength=15; у остальных будет NULL. В исходных INSERT пропущены запятые и разделители операторов. Это учебная выборка: хеши настоящих паролей не следует выводить в API или логи, и по ним нельзя восстановить исходный пароль.