By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE skills (`id` INTEGER, `idUser` INTEGER, `idSkill` INTEGER);
INSERT INTO skills (`id`, `idUser`, `idSkill`) VALUES
('1', '4', '1'),
('2', '8', '4'),
('3', '8', '9'),
('4', '13', '9'),
('5', '18', '2'),
('6', '22', '1'),
('7', '27', '2'),
('8', '32', '4'),
('9', '11', '2'),
('10', '32', '9'),
('10', '32', '7');
Records: 11 Duplicates: 0 Warnings: 0
SELECT idUser
FROM skills
WHERE idSkill IN (9, 4, 7)
GROUP BY idUser
HAVING SUM(idSkill IN (9, 4)) = 2 -- for the 2 mandatory skills
| idUser |
|---|
| 8 |
| 32 |
SELECT idUser
FROM skills
WHERE idSkill IN (9, 4, 7)
GROUP BY idUser
HAVING SUM(idSkill IN (9, 4)) = 2 -- for the 2 mandatory skills
ORDER BY COUNT(*) DESC
LIMIT 1
| idUser |
|---|
| 32 |
SELECT idUser
FROM (
SELECT idUser, RANK() OVER (ORDER BY COUNT(*) DESC) rnk
FROM skills
WHERE idSkill IN (9, 4, 7)
GROUP BY idUser
HAVING SUM(idSkill IN (9, 4)) = 2 -- for the 2 mandatory skills
) t
WHERE rnk = 1
| idUser |
|---|
| 32 |