Immediate Food Delivery II | Leetcode 1174 | Crack SQL Interviews in 50 Qs

[@learnwithchirag]

Hey there! 👋 For more interesting content, tutorials, and updates, Feel free to connect with me on Instagram Handles :- @createwithchirag - instagram.com/createwithchirag/ @learn.with.chirag - instagram.com/learn.with.chirag/ LinkedIn: www.linkedin.com/in/chirag-sehgal-9200111b8/ Let's stay connected and keep the creativity flowing! 💡

[@gagandeepbhardwaj9167]

no need to distinct while counting total number of first orders.

[@iamchinmaykr]

yes we are already selecting 4 customer_ids from sub query based on min(order_date).so without DISTINCT it will work just fine.

[@HemangSinghal-op5ep]

Wonderfully explained!

[@a3rdtierguy864]

No need to use distinct since subquery exceutes first and filter kar hi dega so no need.

[@apoorvpradhan5125]

Thanks bhaiya.

[@akhilpratapsingh3223]

Nice Explanation Sir !!!!

[@learnwithchirag]

Thanks for liking :-)

[@ninadthawait4458]

Hi Chirag, My query passed the example but was not right, can you please help me why this is wrong? SELECT ROUND(SUM(IF(min_date = customer_pref_delivery_date, 1, 0))*100/COUNT(customer_id),2) AS immediate_percentage FROM (SELECT customer_id, MIN(order_date) AS min_date, customer_pref_delivery_date FROM Delivery GROUP BY customer_id) AS First_order

[@sanghamitraacharya8771]

same question

[@anmolverma075]

Well explained !

[@learnwithchirag]

Glad it was helpful to you 💯 Keep Learning 💐

[@pranjaltaye]

You have already selected the first orders, so the output would be the same even if we do not add the DISTINCT keyword, given that no customer has ordered two times on their first day.

[@krishnabohidar7226]

select round(100 * (sum(frequency = 'immediate')/count(frequency)),2) as immediate_percentage from (select delivery_id, customer_id, row_number() over (partition by customer_id order by order_date) as rnk, case when order_date = customer_pref_delivery_date then 'immediate' else 'not_immediate' end as frequency from Delivery order by customer_id, order_date) X where X.rnk = 1

[@badnaam-bachelors516]

Thanks to make it simple

[@learnwithchirag]

Happy to help. Keep Learning 💯💐

[@himanshuranjan657]

Hy Chirag ,I have prepared a code for which test cases are failing ,I am not able to figure out.Can you please find what is wrong .I tried but failing ,your approach is great to solve the question. with cte as ( select *, rank() over( partition by customer_id order by order_date ) as ranking, case when ( order_date = customer_pref_delivery_date ) then 1 else 0 end as k from delivery ) select Round( ( COUNT(CASE WHEN k = 1 THEN 1 END)* 100 / count(*) ), 2 ) as immediate_percentage from cte where ranking = 1

[@learnwithchirag]

I am modifying your query a bit - WITH cte AS ( SELECT *, MIN(order_date) OVER (PARTITION BY customer_id) AS first_order_date, CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END AS immediate_order FROM delivery ) SELECT ROUND( 100.0 * SUM(immediate_order) / COUNT(DISTINCT customer_id), 2 ) AS immediate_percentage FROM cte WHERE order_date = first_order_date; Run it yourself and figure out what was wrong in your query !!!!

[@himanshuranjan657]

Sure

[@anirudhsinghsolanki6256]

select ROUND(AVG(order_date = customer_pref_delivery_date) * 100, 2) as immediate_percentage from delivery where (customer_id, order_date) IN (select customer_id, min(order_date) as first_order from delivery group by customer_id)

[@nimbu_03]

what does that "where(customer_id, order_id)" mean?

[@learnwithchirag]

The syntax WHERE (customer_id, order_date) IN (...) is a shorthand way of expressing a condition that involves multiple columns. In this specific query, it's used to filter rows based on a combination of customer_id and order_date. Hence , WHERE (customer_id, order_date) IN (...) condition is ensuring that only rows corresponding to the first order date for each customer are considered in the calculation of the immediate_percentage.

[@SanjogAgarwal]

hey, do we need DISTINCT here because groupby and min will give only one row per customer?

[@arnetto8505]

Without distinct also it will run

[@kingswitch08]

not passing all test cases. please help SELECT ROUND(SUM(CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END)*100/COUNT(customer_id),2) as immediate_percentage FROM Delivery WHERE order_date in ( SELECT MIN(order_date) FROM Delivery GROUP BY customer_id )

[@niluthpalchowdhury]

Can someone explain why this query is not passing the 2nd case test? with cte as ( select customer_id,min(order_date), case when min(order_date)=customer_pref_delivery_date then 1 else 0 end as flag from Delivery group by customer_id) select round(sum(flag)*100/count(distinct customer_id),2) as immediate_percentage from cte

[@krishnabohidar7226]

select round(100 * (sum(frequency = 'immediate')/count(frequency)),2) as immediate_percentage from (select delivery_id, customer_id, row_number() over (partition by customer_id order by order_date) as rnk, case when order_date = customer_pref_delivery_date then 'immediate' else 'not_immediate' end as frequency from Delivery order by customer_id, order_date) X where X.rnk = 1

[@akshanshsharma7415]

Nice Brooooooooooo

[@PAVANKUMAR-gf5hw]

shoutout to chirag

[@abhishekdhakne4879]

Heyy please upload 4-5 videos naa daily please, I have interview on 2nd , I want to complete all 50 questions by. then.

[@learnwithchirag]

Hi abhishek ! We will try our best to upload maximum videos possible by 2nd December. Stay Tuned and All the best for your interview 👍

[@debassss]

woww

[@learnwithchirag]

Glad you liked it. Keep Watching !