How can i make this Complex query for SQL database

Question

I have a database that looks like this:

Player (UserId, Mail, Password, Username, LastLogin)

Leaderboard (UserId*, Level, Kills, Deaths)

Match (MatchId, HostId*, ServerIp, StartTime, Team1Points, Team2Points)

MatchStats (UserId*, MatchId*, Kills, Deaths)

Weapons (IdWeapon, Name, Damage, FireRate, ReloadTime, Range)

Inventory(UserId*, WeaponType*, Skin)

---

I need a query that selects the LastLogin of every Player that is dead more than 12 times (MatchStats.Deaths > 12) in at least 3 matches. Can someone help me? I'm having troubles with possibles Joins or Subqueries.

I tried something like:

SELECT LastLogin FROM Player WHERE UserId=(SELECT UserId FROM (SELECT * FROM MatchStats WHERE Deaths>12 AS TAB) WHERE COUNT(UserId)>3)) 

or

SELECT LastLogin FROM Player
WHERE (SELECT COUNT(SELECT Count(*) FROM MatchStats WHERE Deaths>12 GROUP BY UserId)>3 GROUP BY UserId;

But i feel i'm quite far from having the corret query, maybe I'm missing a Join or maybe I'm doing it totally wrong..

Answer 1

You can use exists to check whether a player have 12 death in at least three matches

select lastlogin from Player p 
where exists 
(  select UserIdfrom MatchStats m 
   where deaths>=12 and p.UserId=m.UserId 
   group by userid 
   having count(distinct matchid)>=3 
)