Junior — Senior
Determining the strongest password for each user
livecode
Task condition
Given two related tables users and passwords. Write an SQL query that for each user outputs:
- user identifier;
- password with the maximum
strengthvalue among all their records. If a user has multiple passwords with the same maximum strength, any of them can be returned.
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);