CODEWITHSUNDEEP
sqlsql-server
sk1426
sk1426

Reputation: 93

SQL Server Union All on 2 tables and Except

I have 2 tables Hackers and Challenges. The hackers create challenges which are stored in challenges table with hacker_id.

 ___________________
|Column    | Type   |
|-------------------|
|hacker_id |integer |
|-------------------|
|name      |string  |
_____________________

 ______________________
|Column       | Type   |
|----------------------|
|challenge_id |integer |
|----------------------|
|name         |string  |
________________________

If more than one hacker created the same number of challenges and the count is less than the maximum number of challenges created, then I have to exclude those hackers from the result.

I want to solve it using the concept of SET theory where I thought of using UNION ALL first to get count of challenges which are equal and then using minus to exclude it from the main table.

Here is what I have tried:

SELECT H.name, C.Hacker_id, count(C.challenge_id) AS total_cnt
FROM Hackers H INNER JOIN Challenges C 
ON H.Hacker_id= C.hacker ID
GROUP BY C.hacker_id, H.name

Except 

Select * FROM
(
   (SELECT H2.name, C2.Hacker_id, count(C2.challenge_id) AS total_cnt2
   FROM Hackers H2 INNER JOIN Challenges C2 
   ON H2.Hacker_id= C2.hacker ID
   where total_cnt2!= max(count(C.challenge_id))
   GROUP BY C2.hacker_id, H2.name) t1

 UNION ALL 

   (SELECT H.name, C.Hacker_id, count(C.challenge_id) AS total_cnt3
   FROM Hackers H INNER JOIN Challenges C 
   ON H.Hacker_id= C.hacker ID
   where total_cnt3!= max(count(C.challenge_id))
   GROUP BY C.hacker_id, H.name) t2
)
/* I know this is not correct but I want only where count of challenge is same*/
WHERE t1. total_cnt2= t2. total_cnt3
;

The sample input after joining is say:

enter image description here

I want this exact first table Except Challenge counts which are equal count (4 for Rose and 4 for Frank).

The question is from one of the Hackerrank challenges (https://www.hackerrank.com/challenges/challenges/problem?isFullScreen=true)

Upvotes: 1

Views: 86

Answers (1)

jakdep
jakdep

Reputation: 872

Quite an interesting challenge.

I am not sure the using EXCEPT really gains you anything in this specific case, at least not as far as I could work it out since you could just change the EXISTS (see script below) to NOT EXISTS without having to use the EXCEPT, but your results might vary.

Here is an SQL that uses EXCEPT that fulfills the requirements:

SELECT hacker_ID, [Name], total_cnt FROM 
(
    SELECT H.[Name], C.Hacker_id, COUNT(C.challenge_id) AS total_cnt
    FROM Hackers H INNER JOIN Challenges C 
    ON H.Hacker_id = C.hacker_ID
    GROUP BY C.hacker_id, H.[Name]
) t1

EXCEPT

SELECT hacker_ID, [Name], total_cnt FROM 
(
    SELECT H.[Name], C.Hacker_id, COUNT(C.challenge_id) AS total_cnt
    FROM Hackers H INNER JOIN Challenges C 
    ON H.Hacker_id = C.hacker_ID
    GROUP BY C.hacker_id, H.[Name]
) t2
WHERE EXISTS (SELECT 1 FROM (SELECT H2.[Name], C2.Hacker_ID, COUNT(C2.challenge_id) AS total_cnt
                             FROM Hackers H2 INNER JOIN Challenges C2 
                             ON H2.Hacker_id = C2.hacker_ID
                             GROUP BY C2.hacker_id, H2.[Name]) T3 WHERE T2.Hacker_ID <> T3.Hacker_ID AND T2.total_cnt = T3.total_cnt
                             AND T3.total_cnt < (SELECT MAX(total_cnt) FROM (SELECT COUNT(*) AS total_cnt 
                                                                    FROM Hackers H3 INNER JOIN Challenges C3 
                                                                    ON H3.Hacker_id = C3.hacker_ID
                                                                    GROUP BY C3.hacker_id, H3.[Name]) T4))
ORDER BY total_cnt DESC, hacker_id

The most concise answer I found among the other solutions from other users was the following one: https://www.hackerrank.com/rest/contests/master/challenges/challenges/hackers/dotnokato/download_solution?primary=true

Upvotes: 1

Related Questions