MakeMyTrip SQL Interview Question | 16/100 SQL Interview Tips #shorts

Hinzugefügt: 2026-05-07

[00:00:00]Welcome to the 16th video of SQL interview tips and this question was asked in MakeMyTrip. So, save, share, and follow to learn effectively. This input two tables, users and the users booking, count the reservations where it was both the guest's very first booking and the listing's very first booking.

[00:00:18]Listing means the property. So, here if you look at this data and question, this table does not comes into picture as you look at the data. So, for this particular guest ID, which is two, the very first booking this guy made, it was on the 1st of August in 2011. And if you look at this listing ID, means the property itself, the 10 and 10, first booking for this property was made on this particular day itself, where this guest also made its very first booking. So, eventually this is going to be the valid record for the counting. This record, where guest ID is three and the property is 30, means the ID for this one is 30. So, this is the only record where the guest three also made the first booking and this property ID also had its first booking on this.

[00:01:13]So, this is how we are getting the total output as two here. How we are going to solve it because question says that you need to identify something where it was the first booking of guest and it was the first booking for the property itself. We are going to solve it with the help of the window function. So, what our logic is, first what we can do, we can generate the ranking keeping the guest ID in the partition by and the booking time stamp in the order. So, it will generate the ranking for the particular guest itself when he made the first booking. So, that booking will be ranked one.

[00:01:51]Similarly, we are generating another column and deriving the ranking for the listing or the property, keeping the listing ID in the partition by and the particular booking time stamp in the order by. This way, with this particular window function, we are able to identify the first booking made for each property ID or listing ID. This is how the intermediate data will look like. You can see for this particular guest, okay? For this particular guest itself, this guest made the first booking on this time stamp, that's why guest ranking is one. And if you take this two all together and arrange the date, so this will come on the top, that is why list ranking is also one. And in the final query, this is what exactly we are doing, finding out those records where the guest ranking and list ranking is equal and we are simply taking the count of it. If you want to solve this level of SQL interview questions, then do check out my platform Grok Data Skills.

[00:02:50]I have already created something for you.