SQL Puzzle | -Ve +Ve Pair Puzzle | SQL Interview Question

In this puzzle you have to identify the positive and negative pairs for each Id value. Now the requirement is that we cancel and the positive and negative pairs for each Id and show the remaining data to the end user.

Please check the sample input and the expected output.

Sample Input

ID Vals
101 1
101 -1
101 2
101 3
101 -2
101 -3
101 1
101 2
102 1
102 -1
103 1
104 1

Expected Output

Id Vals
101 1
101 2
103 1
104 1

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE Pairs ( ID INT ,Vals INT ) GO INSERT INTO Pairs VALUES (101,1), (101,-1), (101,2), (101,3), (101,-2), (101,-3), (101,1), (101,2), (102,1), (102,-1), (103,1), (104,1) GO SELECT * FROM Pairs GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

SOLUTION – 1

 ```-- ;WITH CTE AS ( SELECT * , ROW_NUMBER() OVER(PARTITION BY ID,Vals ORDER BY Vals) rnk FROM Pairs ) ,CTE1 AS ( SELECT * , COUNT(*) OVER (PARTITION BY ID,REPLACE(Vals, '-', ''),rnk) cnt FROM CTE ) SELECT Id,Vals FROM CTE1 WHERE cnt = 1 -- ```

Output-1

 ```-- Id Vals ----------- ----------- 101 1 101 2 103 1 104 1 (4 rows affected) -- ```

SOLUTION – 2

 ```-- SELECT ID ,CASE WHEN SUM(VALS) > 0 THEN MAX(vals) ELSE MIN(VALS) END Vals from pairs GROUP BY id,ABS(vals) HAVING (COUNT(*) > 1 and SUM(Vals) > 0) OR COUNT(*) = 1 -- ```

Output-2

 ```-- ID Vals ----------- ----------- 101 1 103 1 104 1 101 2 (4 rows affected) -- ```

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/