Now Suraz has to solve a complex problem. He has to count total pairs of house numbers in the database such that they follow the following rules -
- a is smaller than b
- a is odd but b is even
- the rank of a is even and the rank of b is odd
There is no pair of houses that satisfies the given conditions
Before moving to the final solution please try this once and then jump to the solution.
WITH CTE AS (
SELECT house_number hn, ROW_NUMBER() OVER(ORDER BY house_number) AS rnk
SELECT COUNT(*) FROM CTE a, CTE b
WHERE a.hn < b.hn AND MOD(a.hn,2) = 1 AND MOD(b.hn,2)=0 AND MOD(a.rnk,2) = 0 AND MOD(b.rnk,2)=1
Thanks for reading. I hope you enjoyed this fun little case study - it was fun for me to create!. You can also share your approach to solve this problem.