Відео

In question number 5, you need to find the most popular item for each customer. For that, you need to display customers one by one and the criteria is based on count of orders. For customer B, the count of orders is the same for ramen, sushi and curry. That is the reason all 3 items are shown next to customer B in the output.

Glad it was helpful! :)

Hi, I wasn't keeping well for the past 1 month. I am now working on creating a new series of SQL project videos and interview questions. Glad to know that you are awaiting more videos. Will upload very soon!

​@@TurnToData Sir many of us waiting for your video series we will share it for more audiences please start again humble request

@@sarthakkumarmishra5002 : Yes, Sarthak, for sure. I am working on the new series of SQL. I will start uploading the videos very soon. Thanks for your support so far and I am glad to know that you are eagerly waiting for it.

Glad you enjoyed the video, Akash! Regarding Q.10, the actual output comes out to be A=1270 and B=840 (please remove this line from the code: WHEN m.product_name = 'sushi' THEN m.price*10*2). I will update the video with this update. Here is the logic behind question number 10: Unlike question number 9, where only Sushi gets a 2x multiplier, in question number 10, not only Sushi but every other product gets a 2x points multiplier when the difference between join date and order date is equal to or less than 7 days. A good way to visualise this would be to manually try mapping the data in Excel and then calculate the points for each customer. Hope this helps :)

@@TurnToData Please don't think this as an argument I just want to clarify let me share all the data here itself A's Join Date - 2021-01-07 B's Join Date - 2021-01-09 Orders from their join date till join date + 7 days customer_id | order_date | product_id | join_date | +-------------+------------+------------+------------+ | A | 2021-01-07 | 2 | 2021-01-07 | | A | 2021-01-10 | 3 | 2021-01-07 | | A | 2021-01-11 | 3 | 2021-01-07 | | A | 2021-01-11 | 3 | 2021-01-07 | | B | 2021-01-11 | 1 | 2021-01-09 | | B | 2021-01-16 | 3 | 2021-01-09 | Cost, points of product id (points for each product is 2*10*price) 1 - 10 | 200 2 - 15 | 300 3 - 12 | 240 Now add the points based on customer_id A - 300 + 240 + 240 + 240 = 1020 B - 200 + 240 = 440

Thanks for your inputs, Akash. This is just a healthy discussion :) Your approach to the problem is slightly different than mine and it is totally fine to do it the way you did. My logic in solving the problem was as below: In question number 9, we have already awarded points to customers irrespective of the join date. I have simply continued question number 10 keeping the context from question number 9 in mind. So basically, any orders before the customer became a member will get the standard (10*price) points. Here is the breakup customer wise: A = 100 + 150 + 300 + 240 + 240 + 240 = 1270 B = 150 + 150 + 100 + 200 + 240 + 0 = 840 But again, you can choose to take the context into account or ignore it. I believe the question in the case study could have been more elaborate. It's a win-win. Cheers!

Yes, I'll be uploading more SQL videos very soon!

You can still calculate the speed in km/h when you multiply the speed in km/min by 60. It depends on which method you wish to use. However, you are correct regarding the question for each runner and each delivery. I missed out on that part. Thanks for pointing out.

My assumption is that you are asking about question number 5. You can use the same query in MySQL as well. It will run similar to how it runs on SQL server.

Thanks for your response. The question says "not just sushi". Hence, while you assign points to products purchased in 1st week, you will need to continue to consider points for sushi products as in the previous questions.

Thanks for the encouraging feedback :) I'm glad you found the video helpful!

Hi, I'll try to do a detailed video on how to manage pages efficiently in the future. For the time being, you can refer to any other UA-cam video on the same topic. Thanks!

Sure, you can try keyboard shortcuts as well. It's totally up to you. Thanks for the input.

Good question. In the table, the purchases are arranged in order of product_id. So for the same day, product_id 1 and 2 are available. You can take 1 as the product_id and answer the query since we do not have a timestamp given in the table.

It could occur mostly if you are using an older version of Excel. Here are a few alternatives you can try: 1. Go to Data > Get Data > From File > From Folder, then select your PDF file 2. Use Adobe Acrobat to convert the PDF to Excel format first 3. Copy and paste data from the PDF into Excel (if the PDF allows text selection) 4. Use a third-party PDF to Excel converter

😊 Glad you enjoyed the video. Do subscribe to receive similar videos in the future. Cheers!

In my video, I have covered just the basics of file conversion to PDF. If you need help fixing tiny PDFs, please checkout this video from Leila Gharani: ua-cam.com/video/5hD5xqokDeQ/v-deo.html

Thanks for the feedback. I'm glad you loved the video! Yes, you are correct, the process of data cleaning might be a bit long sometimes via SQL. I myself am still learning SQL every single day. I am sure better techniques to handle strings will come up soon as I explore more.

You are most welcome! I'm glad it was helpful to you :)

I'm glad you liked it. Stay tuned for more such projects!

It's a good question. Thoroughly describe the goals of your project (in this case, it is to analyze the data from a fictional diner to help the owner, Danny, understand his customers' behaviors and preferences.), the SQL queries you executed, and the insights you derived (these are the results of each query). This documentation demonstrates your analytical thinking and ability to communicate effectively. From next video onwards, I will also showcase how you can document your findings. This information can be presented in any form you wish, but a PDF is highly recommended. Just upload your SQL queries onto your github repository as that will be the best source of your portfolio.

Hi, appreciate your request, but I'm presently unavailable for a 1:1 training. I would recommend taking a 1:1 training through an established training center near you (or online) as you are interested in a particular domain.

Where r u from ,,,

@@lenkaladivya5707 :: Vijayawada

You are most welcome! Stay tuned for more such projects.

Thanks, glad you enjoyed the video :)

Thanks. Glad to know you liked it. More videos coming out soon.

Appreciate your feedback. Glad it was helpful!

Thanks, Gana! Glad you liked it :)

Thanks, Ravi. Glad you liked it! Subscribe for more such videos.

You are welcome! Glad you liked the video. More projects coming very shortly. Please subscribe and hit the bell icon to stay updated!

Thanks. I'm glad you enjoyed the video!

I hope u can bring more project like this ❤​@@TurnToData

Definitely. More SQL Projects will be uploaded in the coming days 👍

Glad you liked it!

You would need to be signed in to use all Microsoft 365 apps.

Glad you liked it :)

Glad it was helpful!

You are most welcome! I'm glad you enjoyed the video :)

Welcome! Glad you liked the video :)

Glad you liked the video :)

You are most welcome! Do subscribe to the channel to receive more helpful videos on Excel and other data topics.

@@TurnToData sure