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.
@techTFQ
2 жыл бұрын
Thank you so much bro! I am so glad to read this. Happy that my contents have helped you.
@telquel7843
2 жыл бұрын
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!
@techTFQ
2 жыл бұрын
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 :)
@vishalrohila1211
2 жыл бұрын
I got my first SQL related job as a product analyst, all thanks to this big man.
@techTFQ
2 жыл бұрын
Congratulations Vishal 💐 Super happy to read this.. And thank you for the credit ☺️
@mohdmohsin4851
5 ай бұрын
hey brother!! what other tools do you use in your job?
@vulcanstarlight
2 жыл бұрын
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!!
@techTFQ
2 жыл бұрын
So happy to know this. glad you liked it :)
@sanfords
2 жыл бұрын
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...
@techTFQ
2 жыл бұрын
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.
@seantimmons5900
2 жыл бұрын
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
@chrismcgrath7082
2 жыл бұрын
Really interesting question, I couldn't resist having a go by myself. Managed to complete it with standard sql in 10 lines.
@techTFQ
2 жыл бұрын
Amazing Chris.. would you mind sharing your solution here ..
@chrismcgrath7082
2 жыл бұрын
@@techTFQ No problem, I just posted in on the blog link
@professortrog7742
2 жыл бұрын
Yep, that solution is much more in line with what i was thinking of how to solve it. Well done.
@HimanshuKumar-pu4xd
Жыл бұрын
The explanation was too good. I could literally visualize everything that you explained. Thanks a lot for sharing!!
@RiseToGreatness912
2 жыл бұрын
this guy nailed it all... your explanation is so clear and you teach so well.
@techTFQ
2 жыл бұрын
Thank you Jim :)
@sujaa1000
25 күн бұрын
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.
@siddharthm9740
2 жыл бұрын
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.
@techTFQ
2 жыл бұрын
Thank you Siddharth 🙏🏼 Glad you liked it ..
@nirmalaswain4598
11 ай бұрын
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
@fathimafarahna2633
2 жыл бұрын
Amazing... kudos to your efforts for always being proactively replying and helping your subscribers👌🏻👏👏
@techTFQ
2 жыл бұрын
Thank you ❤️
@ernestbethel2035
2 жыл бұрын
Excellent question and analysis. I think I am going to use this question on my engineers and see what they come up with.
@techTFQ
2 жыл бұрын
Great to know that Ernest 🙂
@Datapassenger_prashant
2 жыл бұрын
Wow!! what an amazing question and how beautifully you solved it.
@techTFQ
2 жыл бұрын
Glad you liked it Prashant
@bhooshan25
Жыл бұрын
This question takes time to understand. you have explained it well.
@samcs7217
Жыл бұрын
This is a great problem-solving technique. I enjoyed your video. Appreciated!
@saurav7537
2 жыл бұрын
Amazingly explained.. Learnt quite a few things from this.. Kudos to you
@techTFQ
2 жыл бұрын
Thank you Saurav 🙏🏼 Happy to know this helped you learn new things ..
@alun6362
2 жыл бұрын
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.
@smithdeagle1010
2 жыл бұрын
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
@Quenchedfooty
10 ай бұрын
Exactly my thought.
@ncasmeer1
2 жыл бұрын
I think this can exicute less complexity with the help of substring, case and pivot function. And thank you. your explanation is helpful.
@ahanaroshan7741
2 жыл бұрын
I liked this one.watched it half.I will finish my work and resume. So far its best explaining style on KZitem tech creators .New to your channel Subscribed
@techTFQ
2 жыл бұрын
Thank you Ahana ☺️ Glad you liked it..
@dedeegal
Жыл бұрын
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...
@florincopaci6821
2 жыл бұрын
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!!!
@techTFQ
2 жыл бұрын
Your welcome 🙏🏼 Glad you liked it
@fcoatis
2 жыл бұрын
Awesome! Thank you for your time in explaining this content.
@techTFQ
2 жыл бұрын
Your welcome Fábio. Hope this helped.
@MeirbhinOConaill
2 жыл бұрын
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
@techTFQ
2 жыл бұрын
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.
@honestreview6900
2 жыл бұрын
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...
@techTFQ
2 жыл бұрын
Your welcome buddy .. Glad this helped
@lalithac7544
2 жыл бұрын
Thank You for making these videos. Keep doing Thoufiq. We appreciate your good work
@techTFQ
2 жыл бұрын
Glad you liked it Lalitha
@user-jm8xj8nb9m
3 ай бұрын
Thank you very much this is what i was looking for
@sumitbarde3677
2 жыл бұрын
you should also try to put schema and table definitions in description so that we can try it. BTW great approach.
@BleuSquid
2 жыл бұрын
(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.
@kavururajesh1760
2 жыл бұрын
I really appreciate your work that ur doing. I just love it the way that ur approaching
@techTFQ
2 жыл бұрын
Thank you bro 🙏🏼
@JB-jv6be
2 жыл бұрын
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.
@AnandKumar-dc2bf
2 жыл бұрын
I am using ur videos to Crack interviews. Thanks a ton bro. God Bless......
@techTFQ
2 жыл бұрын
Your welcome bro 🙏🏼 Happy to know my contents have been helpful
@subratdas7357
2 жыл бұрын
Thank you so much for this valuable content.
@techTFQ
2 жыл бұрын
Your welcome bro
@SS-lv8pn
2 жыл бұрын
I had worked on similar on-hand quantity detail report, it's basically under ERP module called Fixed Assets.
@techTFQ
2 жыл бұрын
Good to know bro
@12345abcd_qwerty
2 жыл бұрын
ERP SMEs are under appreciated
@rick2591
2 жыл бұрын
I used buckets when I created an MRP query.
@dattatrayarathi1014
3 ай бұрын
Thank you so much sir for sharing a very great logic for learning.
@rashmipande5098
2 жыл бұрын
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...
@techTFQ
2 жыл бұрын
Let me know what you find from your solution
@pritirani2275
2 жыл бұрын
Your knowledge and concept really helpful ..thank you, and plz make more video based on typical query and usefull query.
@techTFQ
2 жыл бұрын
Thank you 🙏🏼 Glad you liked it
@surodeepdey4158
2 жыл бұрын
Thank you sir for this amazing explanation and knowledge.
@techTFQ
2 жыл бұрын
Your welcome bro 🙏🏼
@ravisharma3069
2 жыл бұрын
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.""
@AV-bp3bc
2 жыл бұрын
Pls make more real interview questions, excellent explanation.thank you.
@techTFQ
2 жыл бұрын
Thank you, I will
@jeffmoden4332
2 жыл бұрын
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.
@saigangadhar5224
2 жыл бұрын
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.👌😍👍😊
@techTFQ
2 жыл бұрын
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
@saigangadhar5224
2 жыл бұрын
@@techTFQ Advance thanks for the tutorials cant wait for them...😊
@techTFQ
2 жыл бұрын
Your welcome sai
@Meuhrlin
2 жыл бұрын
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.
@177silas
2 жыл бұрын
This was also my first solution after reading the problem. I really like that there is multiple ways to do tasks in SQL.
@danielvictoria6272
2 жыл бұрын
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..
@techTFQ
2 жыл бұрын
Thanks for liking the contents.. As for your requested concepts, I’ll consider them for some future videos.. Thanks for the suggestion
@debashishbarik4561
2 жыл бұрын
Thank you sir. Waiting for new video🔥🔥
@techTFQ
2 жыл бұрын
Your welcome Barik :)
@GagicaArian
2 жыл бұрын
Interesting case and good explanation :)
@techTFQ
2 жыл бұрын
Thank you Arian 🙏🏼
@jjohn108
2 жыл бұрын
Great explanation..!! Thanks for the effort
@techTFQ
2 жыл бұрын
Your welcome John. Glad you liked it.
@Mysingh9767
2 жыл бұрын
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
@techTFQ
2 жыл бұрын
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
@LoveWillB4U
2 жыл бұрын
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.
@techTFQ
2 жыл бұрын
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..
@LoveWillB4U
2 жыл бұрын
@@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.
@techTFQ
2 жыл бұрын
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..
@LoveWillB4U
2 жыл бұрын
@@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.
@techTFQ
2 жыл бұрын
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.. 🙏🏼
@sheebaparveen3630
2 жыл бұрын
Feels so great to see you grow this way. Wow. Such a great person and a tutor 💖
@techTFQ
2 жыл бұрын
Thank you dear sheeb 😘❤️
@laurak5196
2 жыл бұрын
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 😬😀?
@techTFQ
2 жыл бұрын
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…
@laurak5196
2 жыл бұрын
@@techTFQ thank you Thoufiq, your encouragement is much appreciated, and I see your subscribership continues to grow! #100KbyMay
@techTFQ
2 жыл бұрын
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
@laurak5196
2 жыл бұрын
@@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 😀👌🏻🙏🏻
@techTFQ
2 жыл бұрын
Awesome , great to hear that
@niroshamaganti3968
2 жыл бұрын
It's veryyy helpful.. thankyou sir..
@techTFQ
2 жыл бұрын
Glad this helped
@carstenb9960
2 жыл бұрын
I think this could also have been accomplished all inline utilizing analytical functions, such as partition by.
@techTFQ
2 жыл бұрын
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
@sankar2885
2 жыл бұрын
Thanks for sharing..very useful..
@techTFQ
2 жыл бұрын
Your welcome 🙏🏼 Glad this helps
@cichy86
2 жыл бұрын
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)
@tejanaidu4625
2 жыл бұрын
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.
@venugopal-nc3nz
2 жыл бұрын
Thank you for taking your time out to make such a long video. How long you have been implementing sql ?
@techTFQ
2 жыл бұрын
Your welcome bro. Been using SQL for over 12 years.
@saigangadhar5224
2 жыл бұрын
@@techTFQ OMG...Then how I can became like you...How many days will I have to try...LOL
@sushovanmondal4746
2 жыл бұрын
Very well explained with all minor details
@techTFQ
2 жыл бұрын
Thank you 🙏🏼
@gabrielcarbajalcarbajal3025
7 ай бұрын
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"
@amolsardesai8427
2 жыл бұрын
I am a regular subscriber of your channel. Kindly create a video on Hadoop (Big data)
@vsmanyamvadali7344
Жыл бұрын
excellent sir for the answer and explanation
@jccoubina
2 жыл бұрын
thank you for the video and the file with the code in Oracle in your blog
@techTFQ
2 жыл бұрын
Your welcome Carlos
@MAYURJAIN49
Жыл бұрын
Amazing what a fantastic explanation.
@ilki9718
2 жыл бұрын
the wrong data in the first warehouse table drives me crazy XD 4th row should be 347 TR006 and 05 are also in the wrong order, watching the timestamp
@acertech1550
2 жыл бұрын
Thank you for this good work. Just wondering if this query will be easier using PIVOT ?
@techTFQ
2 жыл бұрын
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.
@Killers3238s
Жыл бұрын
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
@abhaykumar9806
3 ай бұрын
I am a frontend dev. And I am seeing so many cross joins for the first time. I always assumed cross join hasn't that much use case
@MohitKumarMandhre-tr4gx
Жыл бұрын
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
@fache9226
2 жыл бұрын
great job and please can you make a video on window function
@techTFQ
2 жыл бұрын
Thank you 🙏🏼 I have already made 2 videos on window functions covering all the window functions.. You can find it in my channel
@sindhuri81
2 жыл бұрын
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?
@techTFQ
2 жыл бұрын
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..
@manzell
2 жыл бұрын
My immediate reaction is that I would not do this operation within the database but instead on whatever type of scripting language you're using :)
@techTFQ
2 жыл бұрын
Yes right , if we were going use pl sql or pl pgsql languages then we could write a procedure or function to do it more efficiently but since this question was to write an sql query that I why I came up with this..
@parvejsolkar123
2 жыл бұрын
Mind-blowing
@techTFQ
2 жыл бұрын
Thank you 🙏🏼
@786sakhawt
2 жыл бұрын
Hi Taufiq, Tomorrow is my SQL interview kindly share Some tips and Q&A .
@techTFQ
2 жыл бұрын
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
@princebhardwaj5612
2 жыл бұрын
Amazing explanation!
@techTFQ
2 жыл бұрын
Thank you :)
@saravanakumar8328
2 жыл бұрын
Really awesome
@techTFQ
2 жыл бұрын
Thank you Saravana
@7lhd
2 жыл бұрын
Try with. different data. Just three transactions with zero initial stock. Day 91. 1 in; Day 90. 1 in; Day 1. 2 out. Now check line 29.??
@umeshdussa3196
2 жыл бұрын
Sir, Please Make the videos on SQL for beginners.
@techTFQ
2 жыл бұрын
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
@saifkhan4541
2 жыл бұрын
good one
@techTFQ
2 жыл бұрын
Thank you 🙏🏼
@alicevaleina3479
Жыл бұрын
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
@Freakouts_and_found_Insane
2 жыл бұрын
Sir please make video on CROSS join ...I think ye wala part thoda hard laga muze iss query mai
@techTFQ
2 жыл бұрын
Will cover it in a separate video soon
@soumyaranjanpanigrahi2813
2 жыл бұрын
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
@satyaece2007
2 жыл бұрын
@techTFQ It's really cool to break such a big task and then plug the pieces back together to get the final result, Enjoyed the video. However, I have one question. Can this be done using recursion? Please let me know.
@techTFQ
2 жыл бұрын
Thank you 🙏🏼 I haven’t tried it with recursive queries yet but there can be several different ways to solve this problem
@thndesmondsaid
5 ай бұрын
I wonder if the CROSS JOIN is necessary here, I feel like there is likely an alternative solution that avoids that.
@priyankshasinghal1897
Жыл бұрын
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
@rraviteja
2 жыл бұрын
Excellent explanation
@techTFQ
2 жыл бұрын
Thank you 🙏🏼
@datum1353
Жыл бұрын
Do you have video with pivot approach to solve this?
@fasihahmed507
2 жыл бұрын
Hey Bro, Great work could you please tell me how to download such database (Tables) which you are working on please
@techTFQ
2 жыл бұрын
Thank you Fasih 🙏🏼 You can download the table structure and the scripts and sql query from my blog for free. Link to the blog in the description of the video
@pioneer7161
2 жыл бұрын
Great video for SQL Intermediate level devs. Warning: Beginners don't even try :D
@techTFQ
2 жыл бұрын
Haha you are scraping the beginners .. Even the beginners can benefit from it by getting an idea about what kind of logic to apply to solve this kind of questions
@Harish-gw2cy
2 жыл бұрын
Really nice video...Are u helping for the candidate to build the career related to SQL? I am good in SQL and I am looking for job still I am not getting any job. From my side I am thinking SQL is good for career
@techTFQ
2 жыл бұрын
SQL is good and should get you a job but SQL + Other technologies can open more doors for job and career
@Harish-gw2cy
2 жыл бұрын
@@techTFQ Thanks Bro
@techTFQ
2 жыл бұрын
Your welcome 🙏🏼
@YourYTHUB
Жыл бұрын
is select under dml or dql
@waynehamberg9248
2 жыл бұрын
I believe you misread the question. Consider these items are gallons of milk from your local grocery store and we asking how many gallons of milk has been on the shelf for 1, 2, 3 & 4 days. You want to find out the number of gallons of milk currently in inventory. Then you want to subtract the amounts received for each of the days to get the 1, 2, 3 and 4 days old quantities.
@shikhavarma4049
2 жыл бұрын
Good 👍
@techTFQ
2 жыл бұрын
Thank you Shikha 🙏🏼
@goedzo4361
2 жыл бұрын
Such a nice video!
@techTFQ
2 жыл бұрын
Glad you have liked my contents :)
@surajk9799
2 жыл бұрын
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?
@techTFQ
2 жыл бұрын
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
@nothingisreal6345
2 жыл бұрын
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.
@SatyamKumar-lj5bi
2 жыл бұрын
Thanks a lot ♥️
@techTFQ
2 жыл бұрын
Your welcome Satyam :)
@edhahaz
4 ай бұрын
TODAY- interval. How does last entry - interval make any sense ? (the exercise wouldn't work so we pretend today is the last entry day I guess?)
@quachthetruong
2 жыл бұрын
@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!
@KangoV
2 жыл бұрын
Joe Celko would love this :)
@BrendonParker
2 жыл бұрын
I think this would be much simpler with a ranking function.
@techTFQ
2 жыл бұрын
May be Brendon.. I am sure there are several different ways to solve this.. Perhaps I’ll make a follow up video someday with multiple solutions
@tolulopeesho8103
Жыл бұрын
Thoufiq, please when are you starting the SQL training again? Please me if any one is starting soon.
Пікірлер: 303