Hi sir really appreciate for ur efforts. I have waiting for this type of videos and solution. Thank u sir..pls make this type of more video with advance question.
@TurnToData thank u so much 😊 sir for your reply and eagerly waiting for the playlist upload with continuity really helpful to regular follow up allong with ur video for practice kudos 🎉 really for ur help
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, 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!
@@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.
for 9th question solution can be select customer_id ,sum( case when product_name='sushi' then price*20 else price*10 end ) as points from sales s join menu m on s.product_id=m.product_id group by customer_id;?
Brilliant! You just need to change the text "points" to "total_points". Rest all is good. You can rewrite the same query in many different ways :) I'm glad you tried a better and different approach!
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.
Informative video. But I believe Q10 ans is wrong and correct ans should be A = 1020 and B = 440. Please add points manually considering A&B's join date.
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!
I think you made a mistake in the 10th question. You don't need to check if product_name = 'sushi', you just need to check for all products if they were purchased within the first week to get twice as many points, and in all other cases get regular number of points.
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.
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.
Thanks for your time and effort. Awesome explanation.
Appreciate your feedback. Glad it was helpful!
Full Projects are always helpful. Thank you so much for this Full Project.
You are most welcome! Stay tuned for more such projects.
Thank you Anna , you are really so kind to share such quality information
Thanks. Glad to know you liked it. More videos coming out soon.
I am thankful for your time and effort ,it was very useful .Looking forward for more such SQL Projects .
I'm glad you liked it. Stay tuned for more such projects!
Hi sir really appreciate for ur efforts. I have waiting for this type of videos and solution. Thank u sir..pls make this type of more video with advance question.
I'm glad you enjoyed the video! I'm working on more challenging SQL portfolio projects. Stay tuned!
Highly appreciated ..expecting to upload more videos regarding real life projects.
Thanks, glad you enjoyed the video :)
Thanks for the video👏, waiting for next project, Good work
You are welcome! Glad you liked the video. More projects coming very shortly. Please subscribe and hit the bell icon to stay updated!
Thanks alot! could you please continue one solving SQL problems?
Yes, I'll be uploading more SQL videos very soon!
Only number 10 that gives me hard time to answer. Thanks for the dataset and the question
Great work, keep them coming 👏👏
Thanks, Gana! Glad you liked it :)
Sir kindly made video basic to advanced 😊 way u teaching nice kudos for u efforts 🎉😊
I'm glad you like the way I teach! I appreciate your feedback.
I'm working on a basics to advanced SQL playlist. Will share soon.
@TurnToData thank u so much 😊 sir for your reply and eagerly waiting for the playlist upload with continuity really helpful to regular follow up allong with ur video for practice kudos 🎉 really for ur help
How do you document your findings for the portfolio? What format is the information presented in?
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.
Why you stopped this case study videos and SQL interview questions series ??
Pls start again sir 🙏🙏🙏
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.
for 9th question solution can be select customer_id ,sum( case when product_name='sushi' then price*20 else price*10 end ) as points
from sales s join menu m on s.product_id=m.product_id
group by customer_id;?
Brilliant! You just need to change the text "points" to "total_points". Rest all is good. You can rewrite the same query in many different ways :)
I'm glad you tried a better and different approach!
good explanation
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 👍
this is how you teach sql
Hi Sir, I need SQL training for Azure Data engineering. Any possibility to provide 1:1 training. I will pay accordingly. Please reply 🙏.
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
Informative video. But I believe Q10 ans is wrong and correct ans should be A = 1020 and B = 440. Please add points manually considering A&B's join date.
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!
I think you made a mistake in the 10th question. You don't need to check if product_name = 'sushi', you just need to check for all products if they were purchased within the first week to get twice as many points, and in all other cases get regular number of points.
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
Glad you liked it!
Most popular item is ramen not all sushi ramen curry
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.