REAL SQL Interview Question by a FAANG company | SQL Interview Query and Solution

hey guys i'm tawfiq few days ago one of my subscribers reached out to me who had just got a job in one of the fang companies he shared with me an sql query which he was given during his interview process with that frank company and he wanted to know what would my solution be for that particular sql query now i was very happy to receive that question uh for two main reasons one because it was a question that was asked by a fan company a very big tech giant and i have never given an interview for a fan company so it was good to know what kind of questions can be asked in this kind of big tech companies secondly the question itself was really very challenging there are a lot of complexities that you needed to consider before writing a query to solve this question now instead of keeping the question just to myself i thought i'll just share this question with each and every one of you and that is why i'm making this video so in order to solve this question or to write the query i'll be using the postgresql database but i understand most of you guys also use mysql microsoft sql server or oracle so i'll also provide you the sql query for all of this rdbms as well you will find the sql query the scripts and also this particular question in my blog i'll leave the link to my blog in the description below as always before i can start if you like my work and if you'd like to support this channel then please make sure to subscribe to the channel and give me a thumbs up thank you and let's begin okay so let's look at the question itself now as you can see there is a lot of information provided here so let's try to read this question in detail let's try to understand them and only then let's try to write the query okay so the question states we want to generate an inventory age report which would show the distribution of remaining inventory across the length of time the inventory has been sitting at the warehouse we are trying to classify the inventory on hand across the below four buckets to denote the time the inventory has been lying in the warehouse okay so there are four buckets uh which we need to fill with the given data okay so we have zero to 90 days 91 to 180 days 181 to 270 days 271 to 365 days okay so in simple words what they are basically asking is there is a warehouse which basically gets some shipments the shipments can either bring some items into the warehouse or the ship point might mean that they are taking some items out of the warehouse so for a particular day depending on the number of items remaining in the warehouse they want to know how many items have been lying in the warehouse for the past 90 days they want to know how many items have been lying in the warehouse for the past 91 to 180 days and they want to know how many items have been lying in the warehouse for the past 181 to 270 days and similarly for how many items have been lying in the warehouse for the past 271 to 365 days and they have given this table here so before we i explain this table let's also look at this so they are telling for example the warehouse received 100 units yesterday and shipped 30 units today that means there are 70 units which are one day old and there's one important point they have mentioned here saying that the warehouse uses fifo method that is first in first out approach to manage the inventory that is the inventory or basically the items that comes first will be the one that will be sent out first and now let's look at this table so this is basically the table data that they have provided and you can see we have five different columns now this table basically represents a warehouse a warehouse where there are different shipments that can come on a particular day now for each shipment that arrives there is a shipment id this is a unique identifier and then the shipment can either be an outbound or an inbound okay this is a type of shipment so outbound means that the items will be taken out from the warehouse inbound means there will be some items or inventory that will be added into the warehouse okay so these are the two types now whenever there is a shipment that comes into the warehouse whether it's inbound or outbound there is a column that is on hand quantity delta which basically indicates how many items or how many inventories have been added or removed from the warehouse so the number of items that are added or removed from the warehouse is mentioned in this column that is on hand quantity delta and then there is another column on hand quantity that is the second column which basically indicates on a particular day how many items are remaining in the warehouse okay that is that column is named as on hand quantity and then finally there is another column that is the last column event date time which basically is the date and the time when the shipment happened so it's not just the date but also there is a timestamp that we need to consider so this is basically the given data now what we are expected to do is we need to write a query or basically build a report which is going to take this data and then provide an output which would look something like this okay and as they told previously they want all of this data to get segregated into these four buckets and this bucket should have values indicating for a particular day for the remaining items in the warehouse they want to know how many items have been in the warehouse for up to 90 days how many items in the warehouse have been in the warehouse from 91 to 180 days how many items have been in the warehouse from 181 to 270 days and similarly for 271 to 365 days so we need to analyze this data and somehow come up with an output which would look like this okay and they have also given you an example here saying that for example as of 20th may 2019 that basically is the very last record in this table that is tr001 that is 20th may 2019 they say 250 units were inbounded into the fc fc basically represents like a warehouse and then on 22nd may 2019 eight units were shipped out from the fc that is warehouse and reducing the inventory hand to 242 so that is what is mentioned here that is on the first day that is 20th may 2019 250 units or 250 inventories were added into the warehouse so the total items in the warehouse were 250 on 22nd may eight of the items were removed from the warehouse so the remaining total remaining count was 242. on the 31st december 2019 three 120 were added into the warehouse that because it is inbound so the total uh number of items in the warehouse increased to 362 that is 242 plus 120 which becomes 362. and similarly on 29th january 2020 there was 27 items there were outbound that means 27 items were removed from the warehouse so the count reduced from 362 minus 27 which was equal to 335 and that is what they have explained here as an example so let's say if the given table only had these four records that is uh the last four records up to 29th jan 2020 then on 29 jan 2020 out of the 35 units remaining in the warehouse 120 were present from the last 90 days and 215 were available in the warehouse from 181 to 271 to 70 days okay so this is just an example that they have given how what is their expectations and they have then explained you all the different columns which i already explained and then they have told the data is sorted within uh with the latest entry at the top so meaning that the the last shipment that happened will be at the top and the first shipment that happened will be at the bottom of this table okay and then finally this is the output so this basically our question i'm not sure how much you got that but on the first glance as soon as you look at this question you see that you have a table and you are basically asked to convert row level data into column level data and when doing so you need to do some aggregation right so this looks like a perfect example for using a pivot table now that's what even i thought and i'm sure we can do this query using pivot table as well but i have come up with a solution without using the pivot table by just using some simple sql queries okay so i'm going to write that query but before we write that query it's very important to understand how did they come up with this calculation okay because once we understand the calculation that they have done then we can build a logic according to that calculation if we don't uh understand the calculation that they have done then we will not be able to build the logic and if you are not build the logic then we cannot basically write the query so i'll be writing the query in a few minutes but before i write the query i'm going to explain you the logic and explain you how we can calculate uh this inventory age so make sure you thoroughly understand this explanation if you're not able to understand it i recommend that you watch this video again and again until you understand it because it's not very simple it might be a slightly confusing thing and i'll try my best to explain it in the simplest way possible okay so in order to build this logic first let's do one thing we'll copy this data into an excel file so i've already copied it here it's the same exact data because here i can show you more clearly what my thinking is okay now in order to do the calculation the very first thing that we need to do is we need to identify day one okay because we need to know what is the day from where we need to go prior to 90 days and then see how many uh items have been lying in the in the warehouse for the last 90 days and same way we do for 180 and 270 and 365 days so the first thing that we need to do is what is our day one okay and just by looking at the query here or the question statement here it's telling the data is sorted with the latest entry at the top that means the last shipment has been at the is basically our first record so that means this basically will be my day one okay i'm just going to mark this in bold uh just saying that this is my day one okay and this record here is very important because based on this day one data we are going to do all our calculations and that is because from this day one that is the date 25 may 2020 we need to subtract 90 days and that will be basically our 0 to 90 days range and during that range we need to somehow count the total number of inventories that have been lying during that period okay so this date is very important for us and also we need to know how many items that have been remaining so it has been told in the question that we need to find the inventory age report we need to build the inventory age report for the remaining inventory across the length of time so basically only for the remaining inventory as of the day one the remaining inventory is 278 okay this is important when we further write the query you will understand why it is important so let's do one thing first let's see out of all of these shipments which were the shipments that happened in the last 90 days okay and how can we do that we can simply do that by just considering the day one date that is 25 may 2020 and subtracting 90 days from it so approximately if i subtract 90 days from may 25th i can say that it will be around 25th february 2020 right so just by doing doing a simple calculation this is may 90 days is around three months prior so 25th may subtract 90 days would be around 25th of february so i can just say that all of this data here or basically all of these shipments here have happened in the last 90 days so i'm just going to mark this in yellow so just that it becomes clear i'm just going to mark this let's say okay merge i'm just going to say this like 0 to 90 days so what i have done is from my day one i have subtracted 90 days and then i have able to identify what are all the shipments that happened in the last 90 days and i have marked them in yellow so basically we need to write a different queries to calculate the age of inventory between 0 to 90 days 91 to 180 days and so on and so forth okay so there would be different queries which would calculate this value uh separately okay and that is why in order to do this calculation we first need to know what is the data that we need to consider so for 0 to 90 days this is the only record that we need to consider similarly what are the shipments that happened from 91 to 180 days how do we do that we can just do that by subtracting this day 1 minus 180 day one that is 25th may 2020 minus 180 that is around six months would be would come around 25th november 2019 right you can see that i have some transactions for february january and december but i do not have any transactions for november but i just need to consider the shipments that happened between three months and six months so i can just say that these are the four records which were the shipments that happened between three to six months so i'm just going to mark them in a different color here let's say orange okay and then i'm going to just mark this like 91 to 180 days so i'm just marking what are the different shipments that happened between 91 to 180 days and i'm just getting this calculation just by using the day one date and subtracting 180 days so basically any shipments that happened after 25th november 2019 i am considering it until the 90th day so this is basically my day 90 right so that's what and i would have marked day 180 as well but i do not have any shipment that happened in 25th november uh 2019 so i am not marking day 180 okay but basically this is what we need to understand we have segregated this data for the shipments that happened in the first 90 days then we have segregated the data for the shipment that happened between 91 to 180 days okay the next thing that we need to find is what are the shipments that happened between 181 to 270 days so how do i do that is i just subtract 270 days or basically nine months from 25th may 2020. so if i do that subtraction i get around i think 25th august 2020 and you can see there are no transaction that happened between uh 25th august 2020 to 25th november 2020. so i can just mark it here saying that 181 to 270 days no shipments okay maybe just to make it more clear i can just say it would be like and same way if i look for shipments that happened between 271 to 360 days i can just subtract 365 days from my day one that is 25 may 2020 minus 365 would come around 25 may 2019 i can see that i have transactions for 22nd may 2019 20th and 20th may 2019 but there is no shipment that happened after 25th may 2019 right so these two shipments that are present happened prior to 25th may 2019 so this is prior to 365 days so again for 365 days i can say that from 271 to 365 days i can say that there are no shipments okay and this date is generally like now what about these two shipments that are present here these shipments happen prior to 365 days so i can just mark it like prior to 365 days okay and because it happened prior to 365 days and as per our requirement anything that happened prior to this 365 days we are not doing any calculations here so we can just ignore these records okay we'll write a query which will automatically ignore this record so we will come to know when when i write this query okay so basically i want to know how many items or inventories have been lying in the warehouse in the last 90 days so in order to calculate that i simply want to know how many inventories or how many items were shipped into the warehouse in the last 90 days how do i know which were shipped into the warehouse i can just use this event type inbound so any shipment where the event type is inbound means that these were the shipments which were added into the warehouse so i have this one so i'm just going to mark this like let's say in a different color dark yellow and then i have these two inbound so in the in all the shipments that happened in the first 90 days or the last 90 days there are only three shipments which were basically inbound basically means that these were the only time when items were added into the warehouse during the last 90 days so if i add 31 plus 102 plus 43 i will get it is 176 as you can see here okay so basically if i just sum all the inbound items during the last 90 days i will get the total items that were added to the inventory in the last 90 days that is fine and if i go back to the output i can see that the expected output is also 176. so looks like this just by summing up all the inbound items during the last 90 days i'm going to get the output that i want but there is one tricky point that you need to consider and that is we are not going to write the query which is only going to work with this particular data the query or the report that we are going to generate should not only work with this data but it should work with any type of data so once we get the sum of all the inbound items in the last 90 days we are going to compare it with the total items that have been remaining as of the day one okay for example let's say on 24th may the total items in the warehouse were 377 and then on 25th may 99 items were removed from the warehouse because it was outbound and so 377 minus 99 it returned 278 so it means that 278 were the total items remaining as of day one okay but let's imagine on this 25th may instead of 99 let's say if they had removed or shipped out 299 items if they had shipped out 299 items then the total items remaining as of 25th may would have been just 78 correct because 299 was removed i'm just changing the data just so that i can explain you what are the different scenarios that we need to handle okay so if they remove 299 items on 25th may then the remaining items in the warehouse as of 25th may would have been 78 and now if i just consider the sum of these three that is 100 243 and 31 that is 176 then it kind of seems like out of the remaining 78 items that are present in the warehouse as of 25th may 2020 176 items have been staying in the warehouse for the last 90 days which seems absolutely wrong right so remaining items is 78 and you are telling 176 out of the 78 176 have been lying in the warehouse for the last 90 days this does not seem correct so that is why once we get the total sum of all the inbound items that have been added in the last 90 days we need to compare this sum with the total remaining items in the warehouse as of day one and if the total items that are remaining in the warehouse as of day one is less than the total items that were added into the warehouse during the last 90 days then we need to basically treat that the total remaining items are the total items that have been added to the warehouse in the last 90 days and why i can say that is because of a statement that is given in this problem statement here saying that the warehouse uses fifo approach meaning that first in first out approach so any item that is added into the warehouse will be the first item that is removed from the warehouse so even though there are 299 items removed it doesn't mean that the 176 items that were added into the warehouse during the last 90 days are part of this 299 okay because first they will remove the items that are already present so first they will remove all the items that were prior to the 90 days and after that only they will remove the items that were added during the first 90 days okay so why i'm telling this is if the total items remaining as of day one is less than the total number of items that were added during the first 90 days i can just treat the total items that are available as of day one to be the total items that have been staying in the warehouse for the last 90 days okay i hope you understand this logic because this is the most important logic that you need to understand okay so with this understanding let's try to write this query let me just change this value to the original value that was 278 and 99 okay now let's try to write the query to basically calculate this for the 0 to 90 days okay so let me go back to my postgresql okay but before i start writing queries first thing what we need is we need this data to be loaded into a table right so what i have done is i have created a table by the name warehouse i have the structure here it's just similar to what are the columns here and the data type here and then i have inserted all those records so i just copied all the values from that word document into this file and i've already created a table and i've already loaded the data you'll find all of this script in my blog so you can download it from there now i'm just going to query this table so i'm just going to say select star from warehouse and if i just run this you can see that this table is having the same data that is present in this file here now we are just going to use this table to come up with our query okay so the very first thing that i am just going to do is i am just going to sort this data based on event time so i am just going to say order by event date time in descending order and this is already mentioned here if i go to the question it's told that the data is sorted with the latest entry at top so the shipment that happened at the last will be at the top that is already given but just to be double sure and just to make sure that our database doesn't mess up the order of these records i'm just going to make sure that i always order this based on the event time in the descending order okay so this is basically the data based on which i'm going to write my logic now as you can see we need to do a lot of things so we need to calculate how long the inventory item has been lying in the warehouse and we need to do this calculation for up to 90 days for 90 to 180 days and so on and so forth so we will need to write a different queries for different section of the data so that means i need to write multiple queries right so what i'm just going to do is instead of using multiple sub queries i'm just going to use the with clause so the first query that i'm going to be taking is this main query itself that is from the warehouse table i'm just going to name it like wh as and then i'm just going to move it to the right and i'm just going to put it inside the bracket so this is basically my first query and that is fine now the very next thing that we need to do is as i explained in the logic here i need to identify what is my day one okay and from this day one i need to fetch what is the total remaining items in the warehouse and then i need to identify what is the day one date because using this day one date i'm going to calculate the 90th the 90th date 180th day 270 earth date and 365th date okay which i need for my further calculation so how do i get the day one date so i can just do that i'm just going to create another with clause here or basically another temp table here i'm going to call it like let's say this and i'm going to use as and here i'll write my query so i'll say select from wh because i'm just taking all the data that was written from this warehouse which are sorted in the event date time order and here i'm just going to say limit one okay limit one means you're just going to take the first record because i know that from this table the very first record itself is my day one right so i'm just going to take that and let's say i'm just going to do a star here for now and let me just show all this data so i'll just do select star from this and if i run this now you can see that i'm only fetching the day one data from this table okay using this logic here so that is fine now from this day one what i need is i want the on hand quantity so i'm just going to fetch that on hand quantity and then i need the event date time event date time okay what else do i need i need uh what is the 90th day okay so i need the 90th day so i can do the segregation of the data and then i need the 180th day so i can do the 180th day segregation etc okay so let's first try to find how do i find the 90th day as i told you we just need to subtract the day 1 minus 90 days right but since i'm using postgresql and since this column here is actually not a date column but it's a timestamp column i will not just subtract minus 90 but i'm just going to use a clause like interval okay and i'm going to mention here like 90 day so this will basically subtract 90 day from this date or from this timestamp okay i'm just going to put this whole thing inside a parenthesis and i'm going to give an alias for this like let's say day 90 okay and now if i just run this okay so now you can see that i just fetched three columns one is the on hand quantity that is a total item remaining in the warehouse as of day one and the even date time is basically the day one date and then day 90 basically is 90 days prior to day one similarly since we have already found day 90 let me also find day 180 270 and 360 as well okay so i'm just going to copy the same thing here and i'm just going to paste it here so for 90 for 180 i'm just going to subtract 180 for 270 i'll subtract 270 and then finally 365. i'm going to name this column also accordingly so day 180 day 270 and day 365. okay so if i just run this okay so you can see that i have day one that is 25th may 2020 uh 90 days prior to that is 25th feb 2020 and then 180 days prior to that is 27th november 2019 and 270th day and 365 day why do i need all of this because i need to do all my calculations between this date range right i hope everything is clear until now now let's try to count the total number of shipments that arrived during the last 90 days okay and that is basically what i have highlighted in dark yellow here which we can identify uh just by finding the event type is inbound at this first 90 days shipments and then summing them together so let's try to write a query for that so how i'm going to write a query for that is i'm just going to name it like let's say inventory 90 days okay and i'm going to say as and here i'm just going to write my query so select and i know that i need to fetch all this data from the warehouse table and then what is it i need to fetch so i know that i need to sum so what i need to sum is i need to sum this one that is on hand quantity delta right so i'm just going to write on hand quantity delta and then i need to sum only when the event type is inbound so i'm just going to put it in the where condition saying that where event type equal to inbound okay this is my first condition i also need to add another condition and another condition is basically the date because i only need to fetch the records uh where the shipments happen between the day 1 and day 90 right so how i can do that is i can just put another condition here saying that wh dot event event date time is greater than or equal to i need to consider this day 90 right because you can see i need to start from this day until this day okay and this day is basically my day 90 and day 90 have already calculated in my about table that is days so i need to join this wh table with this now there is one thing now this days will always fetch just one record because you have i have put a limit of one so it's only fetching the day one record so when i'm trying to join wh with this i can just do a cross join i'm just going to join this wh table and i'm going to join using the cross join i'm just going to say cross join with this i'm going to give an alias for days as d and here i'm going to say day dot d dot day 90 okay so hopefully with this when i put this condition we will only consider these seven records from this table and from that seven records it will only consider where the event type is inbound and from there it will count the total on hand quantity delta that is 31 1 0 2 and 43 okay now let me try to run this query and see how the output would look so i'm at the main query i'm just going to do this alias that i have given here and okay so i'll put a star here let's run this okay so you can see i'm getting the output as 176 which is fine and which is basically what i wanted now as i told you this would work fine if the data is just like this but as i told you if let's say on the last day if the shipment shipped out 299 items then this logic would go wrong right and that is why we need to handle that logic and how we are going to handle that logic is we need to compare the value that we have got here with the value that is the total number of remaining items in the warehouse as of day one okay and then we will need to do some uh checking for that so let's try to write a query to do that first i'm just going to give an alias for this and the alias that i'm just going to give is like let's say days old i just want to mention it like 90 days old because uh this is the value uh basically the number of items that have been lying in the warehouse from the last 90 days but i cannot give a column starting with a number that is why i'm just saying days old 90. and now let's write a query now i could have written a sub query here and try to put that logic or the check but it gets a little messy so i'm just going to create another bit clause so again i'm just going to use the same name but i'm just going to add an final here and here okay i need to use the as clause here and then here i'm going to write my query so again i'm going to say select from i know that i need to fetch the result from this previous query here that is inventory 90 days and then i need to compare the value that was returned from here okay so let me just minimize this okay so i just need to compare the value that was written from here so maybe i'll add an enter here just to separate this out so i need to compare the value that was returned from this with clause that is the days old 90 with the value that is the total remaining items in the warehouse as of day one and the day one total remaining items i can get it from the days table so i need to join this table with the days table again since days table only has one column i'm just going to use a cross join so i'm just saying cross join so cross join with this i'm going to give an alias for that as b and since i'm using a cross join i didn't i don't need to specify any columns to join because every record from this table will get joined with every account from this table and since this has only one record it kind of does not create any cartesian product and now i need to compare these values so i'm just going to use a case statement saying that case when and i'm just going to copy this column name and as i told you whatever value that we have got by summing up these three values okay if these three values is greater than the total items remaining as of day one okay so i'm just going to say if this old 90 is greater than d dot that is a total remaining item that is on hand quantity if it is greater than this then i need to consider the smaller value that is the smaller value will be the total items that have been lying in the warehouse in the last 90 days so i'm just going to treat this and else if it is not greater then i can just treat whatever value i got in my above query as the correct number of items that have been lying in the warehouse in the last 90 days i am just going to take that value itself and after that i need to end the case statement end and i need to given the value for this case statement i'm just going to give it like the same column name that is days old 90 okay i think this query should work and now i'm just going to replace this with in the final table and now if i run this okay you can see that the output that i'm getting still is 176 because for the current data it still works exactly fine what i mean to say is this query would work not just for this particular data but even if you had some any other type of data we have written the logic to handle that okay so this is basically how using these two sub queries which have placed inside the with clause i have been able to identify how many items have been lying in the warehouse in the last 90 days okay so i hope this was clear now let me just change this final column name here so the column that we are getting is this one right but the column that we want to display as per our output is something like this that is 0 to 90 days old i'm just going to use the same column name so i'm going to say 0 to 90 days old so it will be exactly what our output needs to be and i'm just going to run it so i just change the column name here so this is fine now we have identified basically we have solved one part of this query now we need to calculate the age of the inventory for all the other days that is from three to six months six to nine months and nine to 12 months okay so let's try to write the query first let's try to write the query for 91 to 180 days and how we can do that is i can almost follow the same logic so i'm just going to copy the same thing for the inventory 90 days whatever we are written we will try to follow the same thing for the inventories that have been lying the warehouse for 91 to 180 days so i'm just going to paste it here but here instead of inventory 90 days i'm just going to mention like 180 days and again what i need to do is between this record that is the four records here i need to count or basically sum together all the shipments that were inbound so in this case there's only one record but there can be more records as well so i'm just going to do a sum of that and i'm going to name it like let's say 180 okay so for the 180 days old this were the total number of inbounds that have been shipped okay and i'm putting this event type equal to inbound and then the final check that i need to do is i need to fetch only these records so the the shipments that happened after 90 days but before 180 days how can i do that is just by doing a between clause i can just use a between statement here saying that between and d dot day of 90 okay so i hope this is clear now let's try to run this query and okay again since this query here or basically this table here that i have created is only to fetch the first column that is the inventories that have been lying in the first 90 days and this query that i have written is to calculate the inventories that have been lying from 91 to 180 days i need to join this table with the about table so and since i know that all of these queries will only fetch one record because i'm just doing the sum it will only return one record i can just do a cross join okay and i'll just do cross join with this and here i'm going to fetch the second column and that's going to be my days old 180 okay and i'm going to name it like let's say the same thing that is 91 to 180 days old okay and now let me just run this let's see if it works okay so i think i'm getting some output this data is not correct and the data is not correct because we have just written the first part of our query as you saw we returned the first part of the query where we summed all the inbounds together during that date range and then we needed to compare it with the total remaining items in the inventory so we have not done this part for our 180 days so we'll try to do that hopefully after that the output would be correct so what i'm just going to do is again we are going to use the same logic so i'm just going to copy this same thing here i'm going to put a comma here and i'm just going to paste it here i'm going to rename this table name to let's say 180 days final and then here inside i will need to query from the inventory 180 days again i'm going to do a cross join with this and change that i need to do here is okay first of all i need to consider this to be this column that is days old 180 right here the calculation for this 180 days is going to be slightly different from the calculation that we did for the 90 days and that is here during this date range we first need to find how many shipments have been inbound during this date range and we'll get that value and then we need to compare this value with the total number of remaining items in the warehouse as of day one minus the total item that have been lying in the warehouse in the first 90 days so we basically should not be comparing the value that we get from here with this value we should compare the value that we got from here with the value that is present here minus the value that we got from the 90 days okay because any shipments or any items that have been lying in the warehouse for the last 90 days should not be considered as present in the warehouse during the 180 days right so we should not be considering the value that we got in this 90 days query so what we need to do is i need to check if the value that i got in the 180 days is greater than the total remaining items as of day 1 minus the value that we got in the 90th day the 90th day value that we got was basically here that is this old 90 and i can just put it here but this days old 90 is present in a table that is this one that is in inventory 90 days final so here i'll be joining that table so again i'm just going to do cross join of that and from this table i have this column days old 90 okay so this is the comparison that i'm doing so i'm comparing the value that is basically the total number of shipments that is the inbound shipment that happened during 91 to 180 days if that is greater than the total remaining items as of day one minus the total items that were available in the warehouse for the first 90 days then if this value is greater than that then i need to treat this value that is the total remaining items minus the items that have been lying in the barrels for the 90 days as my final output okay and if not i can treat the 180 days as it is okay and i'm going to need rename this column also as this okay so i hope this was clear and now let me try to print uh basically fetch the values from this so i'm just going to replace this with this and i think that's all so now let me run this and now you can see that i'm getting the output correctly so for the first 90 days i see that there are 176 inventories that have been lying from the first 90 days and for the 91 to 180 days uh there are 102 inventories that have been lying in the warehouse between 91 to 180 days okay and now we need to do the same thing for the inventories that have been lying the warehouse from 181 to 270 days so again what i'll just do is i'm just going to copy this main query here so this is the first part of the query and i'm just going to put a comma here and i'm just going to paste it here and i'll replace this 180 with 270 and okay so this column name also i'll make it like 270 and what i need to do here is everything is same only thing is i need to consider different dates right and my date range would be different that is i need to consider the date from 180 until 270 right that is only different so here i am checking for all the shipments that happened between 180 to 270th day between this date any shipment that happened i am counting the total number of inbounds by using the sum of this okay and then whatever value i get okay let me try to first print this again to print this i need to do a cross join so i am just going to say cross join of this and here i am just going to print this and here i'll just replace it with 270 and here i'll say this is 181 to 270 and if i run this okay i'm getting some error okay i think there's a comma here i need to remove and if i run this i'm getting that because there were no shipments that happened between 181 to 270 days i'm getting a null but what output we want is if it is null it should print as zero so how we can do that is i'm just going to use a quality function here so i'm just going to say call list of this whole thing if it is null i want it to return as 0 and now if i run this now it's returning 0 this is fine so what i'm also going to do is i'm going to use this quality function also in my above queries so if in for any other data if the data there was no shipment between uh 180 days or 90 days still this should fetch zero instead of null okay so i'm just going to do put the zero coil is here and i'm going to put the coil is here as well okay so i'll just put this and i'll put 0 and now let me run this okay so the output is still the same okay so so that is fine so we have written the query for the 270 days now we need to also do this additional check right we cannot just sum all the inbound shipments we also need to check this whatever value that we have got here with the total number of remaining items and subtract it with the total number of items which were lying in the warehouse for 90 days and also for 180 days okay so almost a similar query that we have written for 180 so i'm just going to copy this whole thing and i'm just going to paste it here so i'll just put a comma here and i'll paste it here and i'm going to replace this 180 with 270 and here again i need to compare my 270th column so once when i'm doing this comparison previously for 180 i was only comparing with total number of items remaining as of day one with the items that have been lying in the warehouse for 90 days but for 270 i also need to consider the 180th day output right so what i'll do is minus of this plus i need to consider that day 1 80th okay that is this one so the logic and thinking is exactly the same that i explained during this 180th query uh just that for 270 we need to consider the number of items remaining as of day one and also the number of items that have been lying in the warehouse for 90 days and also the items that have been lying the warehouse between 91 to 180 days okay all these together so again uh i'm just going to compare this whole thing with this and if the 270th day value is greater than this then i'm just going to print this whole value as my final output okay and here i'll replace this 180 with 270 and i think yeah even this table should be 270 and i think everything else okay so because i'm also using this days old 180 i need to cross join with the 180 uh yet table also so i'm just going to copy this and paste it here and i'm putting 180 why i'm using this table because this column that is days old 180 is coming from this table which i have basically calculated here that is this table right okay so i think 270 days calculation is also done so let me just put it here and let me run this okay so i'm getting the correct output so that is fine now finally let's do the same thing for the 365 days calculation so i'm just going to use the same query so i'm just going to paste it here and instead of 270 i'm just going to replace it with 365 and then here everything is the same so i'll just change this value to 365 that is the column name and then for all the shipment that happened between 271 to 365 days i am count i'm basically doing a sum of all the on hand quantity delta where the shipment type is inbound and then i need to change this date right i need to consider from 365 365 to 270 right so that is what i have considered and let me now just try to print this again i'll do a cross join with this and then here my last column would be that is 365 so i'm just going to print that 365 and then here i'm just going to say 271 up to 365 right so if i execute this okay so i'm getting the output that i wanted uh but then again this is just i'm summing up all the inbound shipments i also need to compare it with the total number of items remaining as of day one and subtracting it with the 90th day value 180th day value and 278th day value right so i'm again going to copy this final query here and i'm just going to put a comma here and paste it here okay and i'm going to replace all of this 270 with 365 365 and okay so this also will be 365 and then here this will be 365 as well and i'm just going to replace this and this as well and here when i'm comparing i'm comparing the value that i've got from the shipment that happened between 271 to 365 days with the total items as of day one as well as the subtracting it with the total items in the warehouse as of day 90 day 180 and also i need to now consider day 270 right so i'm just going to say day 270 and i'm going to use the same thing here as well okay so day 270 so i think this should work and yeah so because i'm using day 270 i also need to use the table from where i'm fetching that column and that is the 270 column so i'm just going to say it to okay so i think this query should work so i'm just going to replace this 365 days with 365 days final and i'll run this okay so i think this basically is our entire query and we have completed this query now if you see that this query would be a pretty long and it might seem pretty confusing but almost all the logic is repeated across different days once you identify the logic to identify the total number of inventories lying in the warehouse from 0 to 90 days we can then apply the same logic for all the other date frames right so i hope you understood this query so this query is written in postgresql but equivalent query in mysql microsoft sql server and oracle is also available in my blog so you can go to my blog and download it so thank you guys for watching i hope this video was beneficial if you like this video please leave your feedback in the comments below thank you and see you soon in the next one bye

Channel: techTFQ

Views: 45,047

Keywords: sql interview questions and answers, SQL Interview Question, FAANG SQL Interview Question, SQL Interview Question by FAANG company, FAANG SQL Questions, FAANG SQL Interview questions, REAL SQL Interview Question by a FAANG company, SQL Interview Query and Solution, REAL SQL Interview Question asked by a FAANG company, SQL Interview Query, FAANG SQL Interview Query, SQL, SQL Query writing, Practice SQL Query, Solving SQL Interview queries, sql query, sql query tutorial

Id: xN2PRAd8IZQ

Length: 46min 47sec (2807 seconds)

Published: Wed Dec 08 2021