Interview question - SQL Puzzle…how many even numbers are there in a row

REATE TABLE #Puzzle(Col1 int, Col2 int, Col3 int, Col4 int, Col5 int)   INSERT  #Puzzle VALUES (1,2,3,4,5) INSERT  #Puzzle VALUES (0,1,2,3,4) INSERT  #Puzzle VALUES (2,2,2,2,2) INSERT  #Puzzle VALUES (1,1,1,1,1) INSERT  #Puzzle VALUES (3,2,3,2,3) INSERT  #Puzzle VALUES (2,3,2,3,2)

And here is the expected output

Col1 Col2 Col3 Col4 Col5 Even
1 2 3 4 5 2
0 1 2 3 4 3
2 2 2 2 2 5
1 1 1 1 1 0
3 2 3 2 3 2
2 3 2 3 2 3

That should be pretty easy right?

How about a solution that doesn’t use a CASE statement, you think you can do it (I can think of 2 ways without using a CASE statement)

Select Col1, Col2, Col3, Col4, Col5, 
5 – (Col1 % 2 + Col2 % 2 + Col3 % 2 + Col4 % 2 + Col5 % 2) As Even

SELECT #Puzzle.*, 5 – SUM(remainder) AS EVEN
from #Puzzle
CROSS APPLY (VALUES 
(Col1 % 2),
(Col2 % 2),
(Col3 % 2),
(Col4 % 2),
(Col5 % 2) ) Even (remainder)
GROUP BY Col1, Col2, Col3, Col4, Col5

SELECT Col1,Col2,Col3,Col4,Col5, 
(~Col1%2 + ~Col2%2 + ~Col3%2 + ~Col4%2 + ~Col5%2 )*-1 as EVENCOUNT
FROM #Puzzle

The Solution is: Use (Modulo) %2 which gives remainder after dividing Value by 2 : So for odd numbers it will be 1 and even numbers it will be 0 .. we want to count the even numbers so use Tilde ~ to get a Binary NOT of the value and apply Mod.. This will give -1 for all even numbers and 0 for all odd numbers.. sum all this and multiply result by -1 to get final count of even numbers !!

There is a lot of debate whether zero is odd or even .. but many sources agree zero is even number. This solution will also count zero as an even number.