I did 2 easy and 1 medium ranked Leetcode challenges since it’s weekend today. Let’s head straight towards the challenges.

Challenge 1:

Given the Cinema table with Id, Movie, Description and Rating as column headers, I had to find out movies that have odd numbers as their ID no. and description that does not contain the word ‘boring’.

id	movie	description	rating
1	War	great 3D	8.9
2	Science	fiction	8.5
3	irish	boring	6.2
4	Ice song	fantasy	8.6
5	House card	interesting	9.1

Expected Output:

id	movie	description	rating
5	House card	interesting	9.1
1	War	great 3D	8.9

The query for the above challenge was as follows.

 SELECT * FROM cinema WHERE MOD(id,2) <> 0 
     AND LOWER(description) <> 'boring'
     ORDER BY rating DESC; 

The MOD() function returns the remainder after the division and therefore, any numbers that returns 0 when divided by 2 are even numbers. Another thing to highlight is that, it is always good to perform a search of a string first by converting the string to a lowercase.

Challenge 2:

Given two tables Person and Address, with (PersonID, FirstName and LastName) and (AddressId, PersonId, City and State) as column headers respectively, I had to query a report that provides the FirstName, LastName, City and State of each of the people.

Column Name	Type
PersonId	int
FirstName	varchar
LastName	varchar

Column Name	Type
AddressId	int
PersonId	int
City	varchar
State	varchar

The query of the above challenge is as follows.

SELECT p.Firstname, p.Lastname, a.City, a.State 
 FROM Person p 
 LEFT JOIN Address a 
 ON p.PersonID = a.PersonID; 

The challenge was solved using simply a LEFT JOIN between the two tables ‘Person’ and ‘Address’.

Challenge 3:

Given a table called ‘Score’, I had to rank the scores. If there is a tie between two scores, both should have the same ranking. If there is a tie, the next ranking number should be consecutive integer value.

ID	Scores
1	3.5
2	3.65
3	4.00
4	3.85
5	4.00
6	3.65

Expected Output:

Score	Rank
4.00	1
4.00	1
3.85	2
3.65	3
3.65	3
3.5	4

The solution to the above challenge was as follows.

SELECT Score, DENSE_RANK() AS Rank 
 FROM Scores
 ORDER BY Score DESC;

The function DENSE_RANK() was explained yesterday in my blog for Day 8. That’s it for today. I took a quite a chill pill today but I plan to solve more of the leetcode challenges tomorrow.

• Facebook
• Instagram
• LinkedIn
• GitHub