Problem Statement:-
Suraz has planned to create a new SQL city for his Student. In his SQL city, he got a list of house numbers in a database.
Now Suraz wants to sort all the house numbers and wants to give them a particular rank that starts from 1 based on their values in ascending order.
Suppose that the house numbers are 145, 60, and 82 then house no 60 gets rank 1, house 82 gets rank 2, and house 145 gets rank 3.
Now Suraz has to solve a complex problem. He has to count total pairs of house numbers (a,b) 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
Explanation:-
There is no pair of houses that satisfies the given conditions
My Solution:-
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
FROM houses
)
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.
Published By : Ayush Dixit
