Extremely good example of binning and working with date-times (something everyone will no doubt have to do in SQL). I agree with some who have mentioned that you could probably solve this using partitions in a more tidy way, but what I love about this tutorial is that you work through the logic of the problem as you go which helps people understand WHY your query works in the end. It would be sort of cool to do a follow-up version of this report using partitions or other alternative methods to compare the query design and determine which is best. Great content and patient explanation. Worth a sub for me!
I love this comment. Thank you Tristan. You raised a great point to make a follow up video using some alternate approach and I am sure we can solve this problem in many different ways. And I will plan to make a follow up video sometime in the future. Thank you for the sub :)
this video really helped me for exploring many areas thanks, and I have a small question about the filter you used here. According to the requirements, the ranges should be 0-90, 91-180, 181-270, and 271-360 days. In the first filter, you correctly included days up to 90, but in the second filter, you included the 90th day again, while they specifically requested it to start from the 91st day. This same issue appears in subsequent filters as well.
I have always struggled with SQL because it's not procedural. The logic breakdown you do here is SO GOOD. It really helps me think in the way I need to for SQL work. Now I have to learn what coalese means...
To add a little bit, there is a function called ISNULL(). This function takes two values. The function evaluates the first value. If the first value is NULL, it will return the second value, regardless of its value. So, ISNULL(NULL,'Hello') returns Hello. Coalesce uses this same exact logic but allows you to include multiple values, always in left to right order. So, COALESCE(NULL,'HI','BYE') returns Hi. It never gets to evaluate BYE, as it falls to the right of a valid return value. If all of the COALESCE values are NULL, it will return NULL. Just to add flexibility, 6ou can use a CASE statement to do the same thing as ISNULL OR COALESCE. For ISNULL: CASE WHEN MyValue is NULL THEN MySecondValue ELSE MyValue END For COALESCE: CASE WHEN MyValue is NULL THEN CASE WHEN MySecondValue is NULL THEN MyThirdValue ELSE MySecondValue END ELSE My first value END
You really are my greatest tutor, I just can't learn enough from you!, everyday I learn a little from you and I can say that practicing your queries has improved my logical and sql query writing skills.
The four-coulumn conversation from the numeric ranges was extremely helpful, I have an extremely relevant thing I am trying to do (sort various data into columns depending on thresholds). Thank goodness I found your channel. You’ve changed my tech world!!
Interesting question, I haven't written any SQL for a few years and thought I'd have a go. This is the logic that I came up with, reading the comments, others have suggested something similar: Find the total quantity of all the outbound stock. This can be found using a windowing/analytic function rather than a separate query. For the inbound stock group into a number of buckets 90 days wide. This avoids having to repeat code. The bucket number is simply the latest event date minus the event date all divided by 90. The latest event date can be found using a windowing/analytic function rather than a separate query. Now, starting from the last bucket, subtract the total outbound stock until it has all been accounted for. At this stage the results are in a column where they can easily be pivoted into the required row. Happy to share the code if you're interested.
This is the right way of doing it even though the results might be similar from a supply chain point of we calculate inventory from oldate to new date as demand is in future and supply is in present and plan the supply to meet the demand present in future
TR005 and TR006 are in the wrong order. TR0010 and TR0011 .... have same OnHandQuantity ??? 348-1 346 and 346-1 346 This means that you cannot rely on the content of the OnHandQuantity column for the general case. Here it fits in the end because of a double error...
I would have put the ranges (0,90 & description..etc.) into a cte table then left join it in a subsequent cte to categorise the dataset rows. Then use dynamic sql to pivot on the category column. In the future you can then add a new category and range to the first cte and the subsequent queries would remain unchanged. Also case statements run on each row so as your dataset grows the query scalling is affected. A table of category values left joined should scale better but that's not tested
Hi Meirbhin, Good suggestion and yes we can have so many different solutions to this problem. I do like your suggestion, would you mind sharing the query with your solution. If I come across some really better solutions then I may plan to make a follow up video on this.
Thank you for making all this tough query easy for us really your way of explanation thanks a lot if possible can you please explain this below problem from stratascratch this might be easy but i tried lot of time : ""Find the customer with the highest daily total order cost between 2019-02-01 to 2019-05-01. If customer had more than one order on a certain day, sum the order costs on daily basis. Output their first name, total cost of their items, and the date.
For simplicity, you can assume that every first name in the dataset is unique.""
I have not completed the video yet ..but as per my understanding the days calculation should be from the last record, the last record should be day 1. tnis understanding of mine seems true with the days range mentioned in the question itself...
Only 1/3rd of the way through but loving the lucid explanation- subscribed! Also I REALLY appreciate the fact that you’ve stuck to basic operations and commands while carrying this out - understanding how to work with fundamentals is so crucial for learners like us. Thank you for your work.
Thanks for great video... I have a little bit confusion in 1 question... If there are two columns A which contains values 1,1,1,2,3,3,3 and second column B which contains values 1,1,2,2,4,Null then what is the output for Left, right, inner, cross join
Great explanation. but I have one doubt, at TR0010, the Onhand quantity should be 347, why 346? from 348 -1=347, again 347-1=346. as outbound represents minus and inbound represents add an item. please correct me if i am wrong.
Well done! I found it interesting that within the CTEs you put the comma after the statement, but in your select statements your subsequent rows start with the comma. I noticed the inconsistency, and just wanted to comment on it. Personally I will either put the commas at the end OR at the beginning, but I don't mix them. My preference is at the beginning of the statements or row. Also, why use coalesce instead of isnull? isnull came to mind first for me as I was following along and didn't even think of coalesce.
Oh my. What a very, VERY clever test and I'm NOT talking about the problem definition. There are 3 faults in this test and I strongly suspect that a hidden part of the test is... did you find the 3 faults? Tricky, tricky, tricky. Either my hat is off to the person that wrote the test or they should be fired for phat-phingering an important test. There's a lesson to be learned in all of this when you're taking a test, especially if it's a BIG company that you want to work for. The numbers they posted as the correct numbers do work out right if you manage to avoid the 3 faults in the test.
I have an question maybe I am missing something, but why day 1 is first row in dataset ? shouldn't it be that day 1 is a day of running query ? I mean there is no chance that Your query will ever return 0 in day 1-90 bucket (except when whole dataset is empty)
When calculating the Between days should you not have to add a day? Dateadd(day, 1, d.day90) instead of just d.day90? if this was a larger data set this would add duplications
Bro, @techTFQ Acc. to me the sum of all items in resultant table equals to the current onhandquantity So, the #items from 91-180days are coming 102 instead it should be 2 Am I missing something? @all
(at 5 minutes in) I actually work with SQL in a warehouse, and I would stop here and say the question and scenario is flawed. The warehouse management system should be redesigned first as it will have major issues in real warehousing scenarios. The assumption that a warehouse that is designed to use FIFO will actually always do so assumes that no mistakes ever happen, no inventory ever gets misplaced, and nothing is ever manually moved outside the system design specifications. Among other things, a warehouse management system should know the current location, receipt date (or expiration date), and quantity for each pallet/location individually. When you're already storing this information, the original question becomes trivial. Also, the question does not account for inventory over a year old. Rare though it may be, it might still happen. A report I use regularly identifies product over 2 years old, since at that point we have to start worrying about age-related effects.
I liked this one.watched it half.I will finish my work and resume. So far its best explaining style on UA-cam tech creators .New to your channel Subscribed
My solution: with cte as( select *, case when datediff((select max(event_datetime) from warehouse),event_datetime ) between 0 and 90 then 1 when datediff((select max(event_datetime) from warehouse),event_datetime ) between 91 and 180 then 2 when datediff((select max(event_datetime) from warehouse),event_datetime ) between 181 and 270 then 3 when datediff((select max(event_datetime) from warehouse),event_datetime ) between 271 and 365 then 4 end as d, case when event_type="InBound" then OnHandQuantityDelta else 0 end as inb from warehouse order by event_datetime desc), cte2 as( select d, sum(OnHandQuantityDelta) as sum_inb from cte where d in (1,2,3,4) and event_type="Inbound" group by d, event_type ), cte3 as( select d, case when sum(-sum_inb) over(order by d) + (select OnHandQuantity from warehouse order by event_datetime desc limit 1)>=0 then sum_inb else sum_inb+sum(-sum_inb) over(order by d) + (select OnHandQuantity from warehouse order by event_datetime desc limit 1) end as new from cte2) select ifnull((select new from cte3 where d=1), 0) as "0-90 days old", ifnull((select new from cte3 where d=2), 0) as "91-180 days old", ifnull((select new from cte3 where d=3), 0) as "181-270 days old", ifnull((select new from cte3 where d=4), 0) as "271-365 days old"
Nice explanation on the problem. An issue I would point out in your solution is that Day 1 is not the first entry but the current time (In an actual interview, someone would mention this and you can easily change it). The warehouse could have not done anything for 4 months and your logic would be incorrect. Besides that, I would say it meets the bare minimum requirement to get a passing grade.
Thank you Rei 🙏🏼 But I’ll have to disagree with your analysis.. Actually the logic would work even if no shipment happened for last 4 months.. you can modify the data and try it out yourself.. Secondly, day1 is just a reference to the last shipment date and time.. I don’t see any issue with that consideration..
@@techTFQ What you are missing is that you set event_datetime as your first day instead of using CURRENT_TIMESTAMP() (or equivalent in your database of choice). What you are doing is you are getting the earliest datetime from the table. No where in the problem did it say that the first entry is the first day. That is your assumption and it is a wrong assumption. This has nothing to do with the data in the table. If you run your query today vs 200 days later, you will get the same results. Unfortunately, that isn't what the question asked. If you were asked what the inventory age in relation to the last inbound/outbound receieved, then your code will work.
Hi Rei, Was it mentioned in the question that you need to find the age of inventory as of current day ? If they specified explicitly that we needed to find the age as per current day then yes, using the current date from database would make sense but in this question they just asked for the age as per the given data .. I fact the last shipment is on May 2020 and if I assumed current day as Day 1 then the output would be not as expected by this question.. As per their given output they kind of wanted us to treat last event date time as the Day 1 from when we needed to calculate the age.. This question was asked to my subscriber just last month so it’s not like this question was asked during May 2020.. I hope this clears your confusion..
@@techTFQ We can agree to disagree but the question was to generate an sql that will create a report for how long the inventory has been in the warehouse. It does not have to give you every detail. As part of the interview process, it is your job to ask questions. Since you weren't doing the interview and couldn't ask questions I can understand your logic. Also, the sample output is just that, a sample output. That sample output was valid for some amount of days but isn't valid today if you run the script today. Unless specifically stated, sample outputs are not your goal. As an engineer, you are expected to come up with your own set of tests and think about when/how your solution does and does not work. I'm just saying if you were asked to generate an sql that would be used to generate reports, your sql will not generate the correct report the longer a shipment has not gone in or out of the warehouse. If this is a busy warehouse then your solution is not a problem.
I understand your point of view but I am just saying for this question in the interview based on the expected output the last shipment date should have been the day 1(since the question was asked in Nov 2021 and the data was as per May 2020) If this was a real project then I would have done this using a procedure and a function and considering a lot more scenarios than what was given in the sample data here.. Anyways, you did make a good point and I appreciate your effort in explaining it.. 🙏🏼
The way you explain is awesome. Though Date parts are always been so confusing for me, I have to go through your video couple of times :-) Thanks Man.. Keep sharing...
I have watched your videos and its awesome...you are the best in explaining the complex queries...I watched so many SQL Videos but your are the best…but when I checked your playlist for SQL Videos there are none...only one SQL full video is there...please upload SQL Tutorials Videos in Playlist...by explaining each and every topic of it. Thanks in Advance.👌😍👍😊
Thank you so much Sai 🙏🏼 So happy to know you like my contents ☺️ As for playlist, there are other playlist covering SQL videos.. but for basic SQL tutorial agree there is only one as of now.. I am planning to make a complete sql tutorial covering all basic concepts soon
@techTFQ i know the main idea that we need to find the remain in (90-180) by (0-180) -(0-90). But i still wonder why you compare DaysOld_180 to (onhandquantity-DaysOld_90) which DaysOld_90 in inv_90_days_final not inv_90_days. Can you tell the different between them? i try the second way, i know it wrong but i don't know why (onhandquantity-DaysOld_90= negative number in some case if DaysOld_90 in inv_90_days). Thank you in advance!
Column 1 material names Column 2 material qty Material A, qty - 10 nos After consumption of 2 nos What query to write to auto update If any new stock comes with 5 nos Similarly how to add up How can we write in sql
Great stuff. I would have done it the other way around: Sum all the inbound having (today-event_datetime) > [section age] and build the table considering nothing went out. And since this is FIFO, I would then have substracted the total outbound from the oldest category, and what is left over from the 3rd, etc. until there is nothing left to substract resulting in having what is left in the first age categories. I love how these kind of problems usually have multiple solutions and seeing how the candidates tackle them.
Hello. I don't undestand why are we going backwards? 90days from last entry , is just that time since that date . Shouldn't our start date be date shipment first inbounded. so inbound-outond? BECAUSE ITS SAYS HERE For example, on 20th May 2019, 250 units were inbounded into the FC. On 22nd May 2019, 8 units were shipped out (outbound) from the FC, reducing inventory on hand to 242 units. On 31st December, 120 units were further inbounded into the FC increasing the inventory on hand from 242 to 362.On 29th January 2020, 27 units were shipped out reducing the inventory on hand to 335 units. On 29th January, of the 335 units on hands, 120 units were 0-90 days old (29 days old) and 215 units were 181-270 days old (254 days old). So from 20May-29 Jan 2020, 215 units were 254 old by you logic we would use date backwards starting 25/05/2020 and that would put it in 117 range
Yea Carsten, I am sure we can solve this using several different ways and using analytical function is one of them.. I’ll make sure to update my blog with alternate solution to this problem (perhaps with analytical functions) in the future
I used datediff, see below. ;with baselinedate as( select top 1 *, 'matching' as matching from #Inventory order by event_datetime desc ), buckets as( select a.*, b.event_datetime as Baseline_Event, abs(datediff(day, b.event_datetime,a.event_datetime)) AS DayDiff, case when abs(datediff(day, b.event_datetime,a.event_datetime)) between 0 and 90 then '0-90 days old' when abs(datediff(day, b.event_datetime,a.event_datetime)) between 91 and 180 then '91-180 days old' when abs(datediff(day, b.event_datetime,a.event_datetime)) between 181 and 270 then '181-270 days old' when abs(datediff(day, b.event_datetime,a.event_datetime)) between 271 and 365 then '271-365 days old'
else 'Over 365 Days' end as DateGroup from #Inventory a inner join baselinedate b on b.matching = 'matching' where a.event_type = 'inbound' )
Your explanations are very clear, as always, and thank you so much for sharing your thought process, because that really helps us, Thoufiq. I think the hardest part of writing a query like this is the part you do at the beginning, where you think it all through and then you figure out the best way to get there. After that, as you said, a lot of it is just repetitive and following that same logic all the way through. I get a little frustrated when I can’t get through that beginning stage when trying to solve a problem, and I get stuck. Is practice the most important thing for a fresher, do you think? Any other advice for the freshers 😬😀?
Thank you Laura ☺️ Yes you just need to solve as many sql problems as possible.. the more you solve, the more easy it becomes to get the logic to solve any sql queries.. It’s just practice that u need.. with time u will automatically start getting the solutions as soon as u look at a sql question…
Thank you Laura ☺️☺️☺️ It is and people have been very kind and supportive just like you ☺️.. Hey, forgot to inform you.. I updated that previous blog about 25 sql interview questions with pointers mentioning if the question is suitable for beginners, intermediate or senior role .. not sure if you checked it
Your welcome bro. I haven't tried it using PIVOT as yet. I may try it in the future and post it in case that solution would be easier than this. As for now, i thought this was a good solution for this question.
I’ll make it Umesh .. I am also planning to conduct a live session on SQL for beginners.. I’ll announce this in a few days .. free session in a different platform.. Hope you can join it and benefit from it
would be great if you could explain star schema and other database concepts and techniques. BTW .. Thanks for your great videos and sharing your knowledge..
for id tr0010 i think onhand_quantity must be 347 please correct me if i am wrong by the way I have seen this question given for a 2 years experienced person in real time thank YOU
Not able to under Else part here. if daysold_180 is not greater than (d.onhandquantity - daysold_90) means there is no old unit left in 180 days period.why are we printing daysold_180 whose value is 120 as per the table Inv_180_days_final as( Select case when daysold_180 > (d.onhandquantity - daysold_90) then (d.onshandquantity - daysold_90) Else daysold_180
Super 👌, thank you so much, by the way, couldn't we use max(date) to get the date or most recent date and avoid all the cross-joins where possible, because they take a toll on the server if the data is huge?
Thank you Ravi.. We not only need max date but also need the remaining inventories as of Last shipment day hence used this logic.. But of course there can be many different ways of writing this query..
I have started working after my graduation . Im making repotrs for small financial company using sql and pentaho reportdesigner.. do u have any suggestions to me?
Hi Sakhawat, Go through the 25 questions I’ve listed in my previous video and also all the queries I worked out in my Practice SQL queries video.. Those should be good enough to clear sql interview.. But if you do not have the time to cover all those then cover whatever u can till then and then just be confident.. More than answering all questions, how you face a question matters.. be confident and humble All the best
@@techTFQ I saw that but assumed they would have formulated the question and the expected output at the same time. They probably didn't think it through as a general case question to be asked in 6 months from when it was written. I would think that in real world practice the query they would probably want would be from the current date rather than what they asked for. Your answer was correct for what they asked for. But I wonder if it might be more impressive to look beyond what they specifically ask for and speak to using the current date as more appropriate to a likely business case. It risks irritating the interviewer but savvy management appreciates developers that can spot bad questions as well as provide correct answers. Just a thought.
Is the challenge to put everything in to one query? In e.g. MS SQL you also simply write a stored procedure that wouldn't be that elegant but much easier to read and maintain. I mean that fact that allo queries only return a single row, so you can use CROSS JOINS (you couldn't use any other join as there is nothing to join upon) isn't that obvious. I doubt that some who has not written this query him/herself would be able to maintain it.
I really appreciate your knowledge and teaching style. I'm transitioning from DBA to SQL Developer and you've been a great blessing to me.
Thank you so much bro! I am so glad to read this. Happy that my contents have helped you.
I got my first SQL related job as a product analyst, all thanks to this big man.
Congratulations Vishal 💐
Super happy to read this..
And thank you for the credit ☺️
hey brother!! what other tools do you use in your job?
Extremely good example of binning and working with date-times (something everyone will no doubt have to do in SQL). I agree with some who have mentioned that you could probably solve this using partitions in a more tidy way, but what I love about this tutorial is that you work through the logic of the problem as you go which helps people understand WHY your query works in the end.
It would be sort of cool to do a follow-up version of this report using partitions or other alternative methods to compare the query design and determine which is best.
Great content and patient explanation. Worth a sub for me!
I love this comment. Thank you Tristan.
You raised a great point to make a follow up video using some alternate approach and I am sure we can solve this problem in many different ways. And I will plan to make a follow up video sometime in the future.
Thank you for the sub :)
this guy nailed it all... your explanation is so clear and you teach so well.
Thank you Jim :)
this video really helped me for exploring many areas thanks, and I have a small question about the filter you used here. According to the requirements, the ranges should be 0-90, 91-180, 181-270, and 271-360 days. In the first filter, you correctly included days up to 90, but in the second filter, you included the 90th day again, while they specifically requested it to start from the 91st day. This same issue appears in subsequent filters as well.
I have always struggled with SQL because it's not procedural. The logic breakdown you do here is SO GOOD. It really helps me think in the way I need to for SQL work. Now I have to learn what coalese means...
Thank you Sanford 🙏🏼
Happy to know this helped you.
As for Coalesce, it’s just a function that replaces NULL (blank) value with any other given value.
To add a little bit, there is a function called ISNULL(). This function takes two values. The function evaluates the first value. If the first value is NULL, it will return the second value, regardless of its value.
So, ISNULL(NULL,'Hello') returns Hello.
Coalesce uses this same exact logic but allows you to include multiple values, always in left to right order.
So, COALESCE(NULL,'HI','BYE') returns Hi. It never gets to evaluate BYE, as it falls to the right of a valid return value. If all of the COALESCE values are NULL, it will return NULL.
Just to add flexibility, 6ou can use a CASE statement to do the same thing as ISNULL OR COALESCE.
For ISNULL:
CASE
WHEN MyValue is NULL THEN MySecondValue
ELSE MyValue
END
For COALESCE:
CASE
WHEN MyValue is NULL THEN CASE
WHEN MySecondValue is NULL THEN MyThirdValue
ELSE MySecondValue
END
ELSE My first value
END
Really interesting question, I couldn't resist having a go by myself. Managed to complete it with standard sql in 10 lines.
Amazing Chris.. would you mind sharing your solution here ..
@@techTFQ No problem, I just posted in on the blog link
Yep, that solution is much more in line with what i was thinking of how to solve it. Well done.
This question takes time to understand. you have explained it well.
You really are my greatest tutor, I just can't learn enough from you!, everyday I learn a little from you and I can say that practicing your queries has improved my logical and sql query writing skills.
Excellent question and analysis. I think I am going to use this question on my engineers and see what they come up with.
Great to know that Ernest 🙂
The explanation was too good. I could literally visualize everything that you explained. Thanks a lot for sharing!!
The four-coulumn conversation from the numeric ranges was extremely helpful, I have an extremely relevant thing I am trying to do (sort various data into columns depending on thresholds). Thank goodness I found your channel. You’ve changed my tech world!!
So happy to know this. glad you liked it :)
Wow!! what an amazing question and how beautifully you solved it.
Glad you liked it Prashant
Interesting question, I haven't written any SQL for a few years and thought I'd have a go.
This is the logic that I came up with, reading the comments, others have suggested something similar:
Find the total quantity of all the outbound stock.
This can be found using a windowing/analytic function rather than a separate query.
For the inbound stock group into a number of buckets 90 days wide.
This avoids having to repeat code.
The bucket number is simply the latest event date minus the event date all divided by 90.
The latest event date can be found using a windowing/analytic function rather than a separate query.
Now, starting from the last bucket, subtract the total outbound stock until it has all been accounted for.
At this stage the results are in a column where they can easily be pivoted into the required row.
Happy to share the code if you're interested.
This is the right way of doing it even though the results might be similar from a supply chain point of we calculate inventory from oldate to new date as demand is in future and supply is in present and plan the supply to meet the demand present in future
Exactly my thought.
I think this can exicute less complexity with the help of substring, case and pivot function.
And thank you. your explanation is helpful.
Thank you so much sir for sharing a very great logic for learning.
TR005 and TR006 are in the wrong order.
TR0010 and TR0011 .... have same OnHandQuantity ??? 348-1 346 and 346-1 346
This means that you cannot rely on the content of the OnHandQuantity column for the general case. Here it fits in the end because of a double error...
I would have put the ranges (0,90 & description..etc.) into a cte table then left join it in a subsequent cte to categorise the dataset rows.
Then use dynamic sql to pivot on the category column. In the future you can then add a new category and range to the first cte and the subsequent queries would remain unchanged.
Also case statements run on each row so as your dataset grows the query scalling is affected. A table of category values left joined should scale better but that's not tested
Hi Meirbhin,
Good suggestion and yes we can have so many different solutions to this problem.
I do like your suggestion, would you mind sharing the query with your solution.
If I come across some really better solutions then I may plan to make a follow up video on this.
Thank you for making all this tough query easy for us really your way of explanation thanks a lot
if possible can you please explain this below problem from stratascratch this might be easy but i tried lot of time :
""Find the customer with the highest daily total order cost between 2019-02-01 to 2019-05-01. If customer had more than one order on a certain day, sum the order costs on daily basis. Output their first name, total cost of their items, and the date.
For simplicity, you can assume that every first name in the dataset is unique.""
Excellent query!Thank you i think it will work also that cross join replacing with join days on 1=1.Thank you once again for all the videos!!!
Your welcome 🙏🏼
Glad you liked it
This is a great problem-solving technique. I enjoyed your video. Appreciated!
I have not completed the video yet ..but as per my understanding the days calculation should be from the last record, the last record should be day 1. tnis understanding of mine seems true with the days range mentioned in the question itself...
Let me know what you find from your solution
Only 1/3rd of the way through but loving the lucid explanation- subscribed!
Also I REALLY appreciate the fact that you’ve stuck to basic operations and commands while carrying this out - understanding how to work with fundamentals is so crucial for learners like us. Thank you for your work.
Thank you Siddharth 🙏🏼
Glad you liked it ..
sir i am from india and i recently finished my BSC. IN PHYSICS can i become a data analyst or data scientist in india please sir clear my dout
Thanks for great video...
I have a little bit confusion in 1 question... If there are two columns A which contains values 1,1,1,2,3,3,3 and second column B which contains values 1,1,2,2,4,Null then what is the output for Left, right, inner, cross join
Your welcome Dhananjai ..
I’ve received a few such queries from others too. I’ll be making JOINS video soon and will try to explain this example then
Pls make more real interview questions, excellent explanation.thank you.
Thank you, I will
I really appreciate your work that ur doing. I just love it the way that ur approaching
Thank you bro 🙏🏼
Great explanation. but I have one doubt, at TR0010, the Onhand quantity should be 347, why 346? from 348 -1=347, again 347-1=346. as outbound represents minus and inbound represents add an item. please correct me if i am wrong.
Thank you so much for this valuable content.
Your welcome bro
Thank you sir for this amazing explanation and knowledge.
Your welcome bro 🙏🏼
Thank you very much this is what i was looking for
you should also try to put schema and table definitions in description so that we can try it. BTW great approach.
Well done! I found it interesting that within the CTEs you put the comma after the statement, but in your select statements your subsequent rows start with the comma. I noticed the inconsistency, and just wanted to comment on it. Personally I will either put the commas at the end OR at the beginning, but I don't mix them. My preference is at the beginning of the statements or row. Also, why use coalesce instead of isnull? isnull came to mind first for me as I was following along and didn't even think of coalesce.
I had worked on similar on-hand quantity detail report, it's basically under ERP module called Fixed Assets.
Good to know bro
ERP SMEs are under appreciated
I used buckets when I created an MRP query.
Oh my. What a very, VERY clever test and I'm NOT talking about the problem definition. There are 3 faults in this test and I strongly suspect that a hidden part of the test is... did you find the 3 faults? Tricky, tricky, tricky. Either my hat is off to the person that wrote the test or they should be fired for phat-phingering an important test. There's a lesson to be learned in all of this when you're taking a test, especially if it's a BIG company that you want to work for.
The numbers they posted as the correct numbers do work out right if you manage to avoid the 3 faults in the test.
Amazingly explained.. Learnt quite a few things from this.. Kudos to you
Thank you Saurav 🙏🏼
Happy to know this helped you learn new things ..
Very well explained with all minor details
Thank you 🙏🏼
I have an question maybe I am missing something, but why day 1 is first row in dataset ? shouldn't it be that day 1 is a day of running query ? I mean there is no chance that Your query will ever return 0 in day 1-90 bucket (except when whole dataset is empty)
When calculating the Between days should you not have to add a day?
Dateadd(day, 1, d.day90) instead of just d.day90? if this was a larger data set this would add duplications
Bro, @techTFQ
Acc. to me the sum of all items in resultant table equals to the current onhandquantity
So, the #items from 91-180days are coming 102 instead it should be 2
Am I missing something?
@all
I am using ur videos to Crack interviews. Thanks a ton bro. God Bless......
Your welcome bro 🙏🏼
Happy to know my contents have been helpful
excellent sir for the answer and explanation
Do you have video with pivot approach to solve this?
Awesome! Thank you for your time in explaining this content.
Your welcome Fábio. Hope this helped.
(at 5 minutes in) I actually work with SQL in a warehouse, and I would stop here and say the question and scenario is flawed. The warehouse management system should be redesigned first as it will have major issues in real warehousing scenarios.
The assumption that a warehouse that is designed to use FIFO will actually always do so assumes that no mistakes ever happen, no inventory ever gets misplaced, and nothing is ever manually moved outside the system design specifications. Among other things, a warehouse management system should know the current location, receipt date (or expiration date), and quantity for each pallet/location individually.
When you're already storing this information, the original question becomes trivial.
Also, the question does not account for inventory over a year old. Rare though it may be, it might still happen.
A report I use regularly identifies product over 2 years old, since at that point we have to start worrying about age-related effects.
Thank You for making these videos. Keep doing Thoufiq. We appreciate your good work
Glad you liked it Lalitha
I liked this one.watched it half.I will finish my work and resume. So far its best explaining style on UA-cam tech creators .New to your channel Subscribed
Thank you Ahana ☺️
Glad you liked it..
thank you for the video and the file with the code in Oracle in your blog
Your welcome Carlos
My solution:
with cte as(
select *, case when datediff((select max(event_datetime) from warehouse),event_datetime ) between 0 and 90 then 1
when datediff((select max(event_datetime) from warehouse),event_datetime ) between 91 and 180 then 2
when datediff((select max(event_datetime) from warehouse),event_datetime ) between 181 and 270 then 3
when datediff((select max(event_datetime) from warehouse),event_datetime ) between 271 and 365 then 4
end as d,
case when event_type="InBound" then OnHandQuantityDelta
else 0 end as inb
from
warehouse order by event_datetime desc),
cte2 as(
select d, sum(OnHandQuantityDelta) as sum_inb from cte where d in (1,2,3,4) and event_type="Inbound" group by d, event_type ),
cte3 as(
select d, case when sum(-sum_inb) over(order by d) + (select OnHandQuantity from warehouse order by event_datetime desc limit 1)>=0 then sum_inb
else sum_inb+sum(-sum_inb) over(order by d) + (select OnHandQuantity from warehouse order by event_datetime desc limit 1) end
as new from cte2)
select ifnull((select new from cte3 where d=1), 0) as "0-90 days old",
ifnull((select new from cte3 where d=2), 0) as "91-180 days old",
ifnull((select new from cte3 where d=3), 0) as "181-270 days old",
ifnull((select new from cte3 where d=4), 0) as "271-365 days old"
Nice explanation on the problem. An issue I would point out in your solution is that Day 1 is not the first entry but the current time (In an actual interview, someone would mention this and you can easily change it). The warehouse could have not done anything for 4 months and your logic would be incorrect. Besides that, I would say it meets the bare minimum requirement to get a passing grade.
Thank you Rei 🙏🏼
But I’ll have to disagree with your analysis..
Actually the logic would work even if no shipment happened for last 4 months..
you can modify the data and try it out yourself..
Secondly, day1 is just a reference to the last shipment date and time.. I don’t see any issue with that consideration..
@@techTFQ What you are missing is that you set event_datetime as your first day instead of using
CURRENT_TIMESTAMP() (or equivalent in your database of choice). What you are doing is you are getting the earliest datetime from the table. No where in the problem did it say that the first entry is the first day. That is your assumption and it is a wrong assumption. This has nothing to do with the data in the table. If you run your query today vs 200 days later, you will get the same results. Unfortunately, that isn't what the question asked. If you were asked what the inventory age in relation to the last inbound/outbound receieved, then your code will work.
Hi Rei,
Was it mentioned in the question that you need to find the age of inventory as of current day ?
If they specified explicitly that we needed to find the age as per current day then yes, using the current date from database would make sense but in this question they just asked for the age as per the given data ..
I fact the last shipment is on May 2020 and if I assumed current day as Day 1 then the output would be not as expected by this question..
As per their given output they kind of wanted us to treat last event date time as the Day 1 from when we needed to calculate the age..
This question was asked to my subscriber just last month so it’s not like this question was asked during May 2020..
I hope this clears your confusion..
@@techTFQ We can agree to disagree but the question was to generate an sql that will create a report for how long the inventory has been in the warehouse. It does not have to give you every detail. As part of the interview process, it is your job to ask questions. Since you weren't doing the interview and couldn't ask questions I can understand your logic. Also, the sample output is just that, a sample output. That sample output was valid for some amount of days but isn't valid today if you run the script today. Unless specifically stated, sample outputs are not your goal. As an engineer, you are expected to come up with your own set of tests and think about when/how your solution does and does not work.
I'm just saying if you were asked to generate an sql that would be used to generate reports, your sql will not generate the correct report the longer a shipment has not gone in or out of the warehouse. If this is a busy warehouse then your solution is not a problem.
I understand your point of view but I am just saying for this question in the interview based on the expected output the last shipment date should have been the day 1(since the question was asked in Nov 2021 and the data was as per May 2020)
If this was a real project then I would have done this using a procedure and a function and considering a lot more scenarios than what was given in the sample data here..
Anyways, you did make a good point and I appreciate your effort in explaining it..
🙏🏼
Thank you sir. Waiting for new video🔥🔥
Your welcome Barik :)
Amazing... kudos to your efforts for always being proactively replying and helping your subscribers👌🏻👏👏
Thank you ❤️
The way you explain is awesome.
Though Date parts are always been so confusing for me, I have to go through your video couple of times :-)
Thanks Man.. Keep sharing...
Your welcome buddy ..
Glad this helped
Thanks for sharing..very useful..
Your welcome 🙏🏼
Glad this helps
It's veryyy helpful.. thankyou sir..
Glad this helped
I have watched your videos and its awesome...you are the best in explaining the complex queries...I watched so many SQL Videos but your are the best…but when I checked your playlist for SQL Videos there are none...only one SQL full video is there...please upload SQL Tutorials Videos in Playlist...by explaining each and every topic of it. Thanks in Advance.👌😍👍😊
Thank you so much Sai 🙏🏼
So happy to know you like my contents ☺️
As for playlist, there are other playlist covering SQL videos.. but for basic SQL tutorial agree there is only one as of now.. I am planning to make a complete sql tutorial covering all basic concepts soon
@@techTFQ Advance thanks for the tutorials cant wait for them...😊
Your welcome sai
Mind-blowing
Thank you 🙏🏼
good one
Thank you 🙏🏼
Amazing what a fantastic explanation.
@techTFQ i know the main idea that we need to find the remain in (90-180) by (0-180) -(0-90). But i still wonder why you compare DaysOld_180 to (onhandquantity-DaysOld_90) which DaysOld_90 in inv_90_days_final not inv_90_days. Can you tell the different between them? i try the second way, i know it wrong but i don't know why (onhandquantity-DaysOld_90= negative number in some case if DaysOld_90 in inv_90_days). Thank you in advance!
Interesting case and good explanation :)
Thank you Arian 🙏🏼
I wonder if the CROSS JOIN is necessary here, I feel like there is likely an alternative solution that avoids that.
Column 1 material names
Column 2 material qty
Material A, qty - 10 nos
After consumption of 2 nos
What query to write to auto update
If any new stock comes with 5 nos
Similarly how to add up
How can we write in sql
Your knowledge and concept really helpful ..thank you, and plz make more video based on typical query and usefull query.
Thank you 🙏🏼
Glad you liked it
Excellent explanation
Thank you 🙏🏼
Great stuff.
I would have done it the other way around: Sum all the inbound having (today-event_datetime) > [section age] and build the table considering nothing went out.
And since this is FIFO, I would then have substracted the total outbound from the oldest category, and what is left over from the 3rd, etc. until there is nothing left to substract resulting in having what is left in the first age categories.
I love how these kind of problems usually have multiple solutions and seeing how the candidates tackle them.
This was also my first solution after reading the problem. I really like that there is multiple ways to do tasks in SQL.
Really awesome
Thank you Saravana
great job and please can you make a video on window function
Thank you 🙏🏼
I have already made 2 videos on window functions covering all the window functions..
You can find it in my channel
Thoufiq, please when are you starting the SQL training again? Please me if any one is starting soon.
Hello. I don't undestand why are we going backwards? 90days from last entry , is just that time since that date . Shouldn't our start date be date shipment first inbounded. so inbound-outond?
BECAUSE ITS SAYS HERE For example, on 20th May 2019, 250 units were inbounded into the FC. On 22nd May 2019, 8 units were shipped out (outbound) from the FC, reducing inventory on hand to 242 units. On 31st December, 120 units were further inbounded into the FC increasing the inventory on hand from 242 to 362.On 29th January 2020, 27 units were shipped out reducing the inventory on hand to 335 units.
On 29th January, of the 335 units on hands, 120 units were 0-90 days old (29 days old) and 215 units were 181-270 days old (254 days old). So from 20May-29 Jan 2020, 215 units were 254 old by you logic we would use date backwards starting 25/05/2020 and that would put it in 117 range
I think this could also have been accomplished all inline utilizing analytical functions, such as partition by.
Yea Carsten, I am sure we can solve this using several different ways and using analytical function is one of them..
I’ll make sure to update my blog with alternate solution to this problem (perhaps with analytical functions) in the future
Is that table definitely correct at the start? Shouldn't TR0010 show 347 OnHandQty?
You have two rows that -1 but they both show 346 after 348.
how long are these interviews?
Not really sure but perhaps 1-2 hrs time may be given to solve this kind of questions
Amazing explanation!
Thank you :)
Joe Celko would love this :)
Could you please tell me what we can use instead of interval in SSMS?
I used datediff, see below.
;with baselinedate as(
select top 1 *, 'matching' as matching from #Inventory order by event_datetime desc
),
buckets as(
select a.*,
b.event_datetime as Baseline_Event,
abs(datediff(day, b.event_datetime,a.event_datetime)) AS DayDiff,
case
when abs(datediff(day, b.event_datetime,a.event_datetime)) between 0 and 90 then '0-90 days old'
when abs(datediff(day, b.event_datetime,a.event_datetime)) between 91 and 180 then '91-180 days old'
when abs(datediff(day, b.event_datetime,a.event_datetime)) between 181 and 270 then '181-270 days old'
when abs(datediff(day, b.event_datetime,a.event_datetime)) between 271 and 365 then '271-365 days old'
else 'Over 365 Days' end as DateGroup
from #Inventory a
inner join baselinedate b on b.matching = 'matching'
where a.event_type = 'inbound'
)
Great explanation..!!
Thanks for the effort
Your welcome John. Glad you liked it.
Sir please make video on CROSS join ...I think ye wala part thoda hard laga muze iss query mai
Will cover it in a separate video soon
I am a regular subscriber of your channel. Kindly create a video on Hadoop (Big data)
Your explanations are very clear, as always, and thank you so much for sharing your thought process, because that really helps us, Thoufiq. I think the hardest part of writing a query like this is the part you do at the beginning, where you think it all through and then you figure out the best way to get there. After that, as you said, a lot of it is just repetitive and following that same logic all the way through. I get a little frustrated when I can’t get through that beginning stage when trying to solve a problem, and I get stuck. Is practice the most important thing for a fresher, do you think? Any other advice for the freshers 😬😀?
Thank you Laura ☺️
Yes you just need to solve as many sql problems as possible.. the more you solve, the more easy it becomes to get the logic to solve any sql queries..
It’s just practice that u need.. with time u will automatically start getting the solutions as soon as u look at a sql question…
@@techTFQ thank you Thoufiq, your encouragement is much appreciated, and I see your subscribership continues to grow! #100KbyMay
Thank you Laura ☺️☺️☺️
It is and people have been very kind and supportive just like you ☺️..
Hey, forgot to inform you.. I updated that previous blog about 25 sql interview questions with pointers mentioning if the question is suitable for beginners, intermediate or senior role .. not sure if you checked it
@@techTFQ I did notice it, it looks good and thanks for that. It’ll help me when preparing and I’m sure it’s helping people as we type 😀👌🏻🙏🏻
Awesome , great to hear that
Thank you for this good work. Just wondering if this query will be easier using PIVOT ?
Your welcome bro.
I haven't tried it using PIVOT as yet. I may try it in the future and post it in case that solution would be easier than this.
As for now, i thought this was a good solution for this question.
Sir, Please Make the videos on SQL for beginners.
I’ll make it Umesh ..
I am also planning to conduct a live session on SQL for beginners..
I’ll announce this in a few days .. free session in a different platform..
Hope you can join it and benefit from it
would be great if you could explain star schema and other database concepts and techniques. BTW .. Thanks for your great videos and sharing your knowledge..
Thanks for liking the contents..
As for your requested concepts, I’ll consider them for some future videos..
Thanks for the suggestion
THANKS VALUE DATA FOR FRESER
Your welcome Jeevan
I was planning to learn sql.
1.Is ssis ssrs and ssas are parts of sql or not?
2. Dax language used in power bi and ssas Dax are same or not?
Taufiq sir, is this question are for freshers or for the person having som experience in feild of data analytics, sir plese reply
for id tr0010 i think onhand_quantity must be 347 please correct me if i am wrong by the way I have seen this question given for a 2 years experienced person in real time thank YOU
Such a nice video!
Glad you have liked my contents :)
You said insert data from text document?? Is that CSV or text file?
Thank you for taking your time out to make such a long video. How long you have been implementing sql ?
Your welcome bro. Been using SQL for over 12 years.
@@techTFQ OMG...Then how I can became like you...How many days will I have to try...LOL
is select under dml or dql
Not able to under Else part here. if daysold_180 is not greater than (d.onhandquantity - daysold_90) means there is no old unit left in 180 days period.why are we printing daysold_180 whose value is 120 as per the table
Inv_180_days_final as(
Select
case when daysold_180 > (d.onhandquantity - daysold_90) then (d.onshandquantity -
daysold_90)
Else daysold_180
Super 👌, thank you so much, by the way, couldn't we use max(date) to get the date or most recent date and avoid all the cross-joins where possible, because they take a toll on the server if the data is huge?
Thank you Ravi..
We not only need max date but also need the remaining inventories as of Last shipment day hence used this logic..
But of course there can be many different ways of writing this query..
I have started working after my graduation . Im making repotrs for small financial company using sql and pentaho reportdesigner.. do u have any suggestions to me?
Just keep learning Suraj and always look for alternative ways to build the same sql query which should help you discover the various features in sql
How can I reach you, I am having a complex question also in my company and I don't know how to go about it.
You are the Best !!!
Thank you bro ☺️
Good 👍
Thank you Shikha 🙏🏼
Hi Taufiq, Tomorrow is my SQL interview kindly share Some tips and Q&A .
Hi Sakhawat,
Go through the 25 questions I’ve listed in my previous video and also all the queries I worked out in my Practice SQL queries video..
Those should be good enough to clear sql interview..
But if you do not have the time to cover all those then cover whatever u can till then and then just be confident..
More than answering all questions, how you face a question matters.. be confident and humble
All the best
When they ask for something 0 to 90 days old wouldn't that be calculated from the current date/time?
My solution is based on the expected output they showed hence my assumption of the days .
You can solve the problem using many other solutions
@@techTFQ I saw that but assumed they would have formulated the question and the expected output at the same time. They probably didn't think it through as a general case question to be asked in 6 months from when it was written. I would think that in real world practice the query they would probably want would be from the current date rather than what they asked for. Your answer was correct for what they asked for. But I wonder if it might be more impressive to look beyond what they specifically ask for and speak to using the current date as more appropriate to a likely business case. It risks irritating the interviewer but savvy management appreciates developers that can spot bad questions as well as provide correct answers. Just a thought.
Agreed 100% Stephen ..
Definitely something we can ask the interviewer when we are presented with such questions
Is the challenge to put everything in to one query? In e.g. MS SQL you also simply write a stored procedure that wouldn't be that elegant but much easier to read and maintain. I mean that fact that allo queries only return a single row, so you can use CROSS JOINS (you couldn't use any other join as there is nothing to join upon) isn't that obvious. I doubt that some who has not written this query him/herself would be able to maintain it.
Logic is same for PL SQL and MS SQL ? I think logic will be same for both... As a biginner i asked u that please also mention for MS SQL also
Yes logic is the same..
And I have already provided the query for MSSQL, Oracle, MySQL and PostgreSQL .
You can download it from my blog