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

Подписаться 303 тыс.

Просмотров 240 тыс.

50% 1

In this video, we shall see a REAL SQL Interview Question which was asked by a FAANG company (Facebook, Amazon, Apple, Netflix, Google). We shall explain how to solve this SQL Query step by step.
This SQL question was asked during an SQL interview process conducted by one of the tech giants (a FAANG company) recently.
Recently one of my subscribers, got a job in one of the FAANG companies (Facebook, Amazon, Apple, Netflix, Google) and he shared with me an SQL Questions which he was asked during his interview process. He wanted to know what my solution would be for this SQL question.
This SQL question is pretty complex hence in the video, we first try to understand the question and then try to build the logic which can be used to do the required calculation to arrive at the required output. Finally we start writing the SQL query from scratch to solve this question.
The SQL Question and all the SQL scripts and SQL Queries I have written in this video can be downloaded from my blog. You will also find the solution to this query in PostgreSQL, MySQL, Oracle and Microsoft SQL Server (MSSQL).
Link to my blog: techtfq.com/blog/real-sql-int...
Timestamp:
00:00 Intro
01:18 Understanding the SQL Question asked by a FAANG company
08:49 Building the SQL logic to solve the FAANG SQL Interview question
20:20 Writing the SQL Query for the FAANG SQL Interview question
Finally, a special THANK YOU to my subscriber who shared this SQL question with me (Not disclosing his name as requested by him).
If you found value from this video then please consider SUBSCRIBING to my channel and also appreciate a LIKE on this video.
🔴 WATCH MORE VIDEOS HERE 👇
✅ SQL Tutorial - Basic concepts:
• SQL Tutorial - Basic c...
✅ SQL Tutorial - Intermediate concepts:
• SQL Tutorial - Interme...
✅ SQL Tutorial - Advance concepts:
• SQL Tutorial - Advance...
✅ Practice Solving Basic SQL Queries:
• Practice Solving BASIC...
✅ Practice Solving Intermediate SQL Queries:
• Practice Solving INTER...
✅ Practice Solving Complex SQL Queries:
• Practice Solving COMPL...
✅ Data Analytics Career guidance:
• Data Analytics career ...
✅ SQL Course, SQL Training Platform Recommendations:
• SQL Course / Training
✅ Python Tutorial:
• Python Tutorial
THANK YOU,
Thoufiq

Опубликовано:

16 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 300

@kodfam 2 года назад

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 :)

@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 :)

@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 2 месяца назад

hey brother!! what other tools do you use in your job?

@HimanshuKumar-pu4xd Год назад

The explanation was too good. I could literally visualize everything that you explained. Thanks a lot for sharing!!

@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 8 месяцев назад

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

@bhooshan25 11 месяцев назад

This question takes time to understand. you have explained it well.

@fathimafarahna2633 2 года назад

Amazing... kudos to your efforts for always being proactively replying and helping your subscribers👌🏻👏👏

@techTFQ 2 года назад

Thank you ❤️

@RiseToGreatness912 2 года назад

this guy nailed it all... your explanation is so clear and you teach so well.

@techTFQ 2 года назад

Thank you Jim :)

@samcs7217 Год назад

This is a great problem-solving technique. I enjoyed your video. Appreciated!

@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.

@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

@ahanaroshan7741 2 года назад

I liked this one.watched it half.I will finish my work and resume. So far its best explaining style on RU-vid tech creators .New to your channel Subscribed

@techTFQ 2 года назад

Thank you Ahana ☺️ Glad you liked it..

@user-jm8xj8nb9m 12 дней назад

Thank you very much this is what i was looking for

@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.

@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.

@lalithac7544 2 года назад

Thank You for making these videos. Keep doing Thoufiq. We appreciate your good work

@techTFQ 2 года назад

Glad you liked it Lalitha

@MAYURJAIN49 Год назад

Amazing what a fantastic explanation.

@fcoatis 2 года назад

Awesome! Thank you for your time in explaining this content.

@techTFQ 2 года назад

Your welcome Fábio. Hope this helped.

@Datapassenger_prashant 2 года назад

Wow!! what an amazing question and how beautifully you solved it.

@techTFQ 2 года назад

Glad you liked it Prashant

@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 ..

@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 🙂

@dattatrayarathi1014 7 дней назад

Thank you so much sir for sharing a very great logic for learning.

@vsmanyamvadali7344 Год назад

excellent sir for the answer and explanation

@sumitbarde3677 2 года назад

you should also try to put schema and table definitions in description so that we can try it. BTW great approach.

@kavururajesh1760 2 года назад

I really appreciate your work that ur doing. I just love it the way that ur approaching

@techTFQ 2 года назад

Thank you bro 🙏🏼

@veerakkannanp9329 Год назад

Really awesome

@amolsardesai8427 2 года назад

I am a regular subscriber of your channel. Kindly create a video on Hadoop (Big data)

@abinashmandal7925 Год назад

Great sir..

@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

@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

@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

@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

@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.

@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.

@debashishbarik4561 2 года назад

Thank you sir. Waiting for new video🔥🔥

@techTFQ 2 года назад

Your welcome Barik :)

@subratdas7357 2 года назад

Thank you so much for this valuable content.

@techTFQ 2 года назад

Your welcome bro

@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 😘❤️

@surodeepdey4158 2 года назад

Thank you sir for this amazing explanation and knowledge.

@techTFQ 2 года назад

Your welcome bro 🙏🏼

@jjohn108 2 года назад

Great explanation..!! Thanks for the effort

@techTFQ 2 года назад

Your welcome John. Glad you liked it.

@parvejsolkar123 2 года назад

Mind-blowing

@techTFQ 2 года назад

Thank you 🙏🏼

@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

@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.

@Beant1980 2 года назад

You are the Best !!!

@techTFQ 2 года назад

Thank you bro ☺️

@KangoV 2 года назад

Joe Celko would love this :)

@sankar2885 2 года назад

Thanks for sharing..very useful..

@techTFQ 2 года назад

Your welcome 🙏🏼 Glad this helps

@AV-bp3bc 2 года назад

Pls make more real interview questions, excellent explanation.thank you.

@techTFQ 2 года назад

Thank you, I will

@GagicaArian 2 года назад

Interesting case and good explanation :)

@techTFQ 2 года назад

Thank you Arian 🙏🏼

@sushovanmondal4746 2 года назад

Very well explained with all minor details

@techTFQ 2 года назад

Thank you 🙏🏼

@niroshamaganti3968 2 года назад

It's veryyy helpful.. thankyou sir..

@techTFQ 2 года назад

Glad this helped

@goedzo4361 2 года назад

Such a nice video!

@techTFQ 2 года назад

Glad you have liked my contents :)

@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.

@princebhardwaj5612 2 года назад

Amazing explanation!

@techTFQ 2 года назад

Thank you :)

@rraviteja 2 года назад

Excellent explanation

@techTFQ 2 года назад

Thank you 🙏🏼

@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.""

@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.

@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 Год назад

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 7 месяцев назад

Exactly my thought.

@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

@saifkhan4541 2 года назад

good one

@techTFQ 2 года назад

Thank you 🙏🏼

@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

@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..

@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

@abhaykumar9806 7 дней назад

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

@shikhavarma4049 2 года назад

Good 👍

@techTFQ 2 года назад

Thank you Shikha 🙏🏼

@jccoubina 2 года назад

thank you for the video and the file with the code in Oracle in your blog

@techTFQ 2 года назад

Your welcome Carlos

@wonderwoman9895 2 года назад

Thanks a ton Sir

@techTFQ 2 года назад

Thank you 🙏🏼

@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

@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.

@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

@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

@tejanaidu4625 Год назад

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.

@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

@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)

@SatyamKumar-lj5bi 2 года назад

Thanks a lot ♥️

@techTFQ 2 года назад

Your welcome Satyam :)

@tolulopeesho8103 Год назад

Thoufiq, please when are you starting the SQL training again? Please me if any one is starting soon.

@hadireg 2 года назад

👍👍

@techTFQ 2 года назад

Thank you

@datum1353 10 месяцев назад

Do you have video with pivot approach to solve this?

@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

@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!

@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.. 🙏🏼

@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

@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

@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

@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...

@sridhargoud8457 Год назад

Hi brother please upload the all SQL videos

@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

@osazeekhator9112 6 месяцев назад

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.

@YourYTHUB 9 месяцев назад

is select under dml or dql

@thndesmondsaid 2 месяца назад

I wonder if the CROSS JOIN is necessary here, I feel like there is likely an alternative solution that avoids that.

@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

@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

@pritirani2275 2 года назад

And one more thing I want to you that you make video based on n data recovery query.. mean if our data lost somehow so how we can recover

@techTFQ 2 года назад

Ok priti, haven’t really thought of making video about data recovery but will definitely consider it for a future video

@challajeevan4649 2 года назад

THANKS VALUE DATA FOR FRESER

@techTFQ 2 года назад

Your welcome Jeevan

@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

@riteshsharma3627 2 года назад

You said insert data from text document?? Is that CSV or text file?

@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

@gabrielcarbajalcarbajal3025 4 месяца назад

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"

@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

@priyankshasinghal1897 11 месяцев назад

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

@azizmamatov6723 2 года назад

Was it during live interview or home take? It would be pretty hard to complete it live, especially since I don't think that it is allowed to execute multiple queries and peek at preliminary results from draft queries which are so helpful when compiling the final query.

@techTFQ 2 года назад

Not really sure about this .. I’ll need to check and get back