admin@onlinelearningcenter.in (+91) 7 999 01 02 03

Sort the houses in Suraz's SQL City

Ayush Dixit
15 Posts

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
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

Comments