Sobes.tech
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 strength value 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);