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.

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

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

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

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

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

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

[@badnaam-bachelors516]

Thanks to make it simple

[@learnwithchirag]

Happy to help. Keep Learning 💯💐

[@anmolverma075]

Well explained !

[@learnwithchirag]

Glad it was helpful to you 💯 Keep Learning 💐

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

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

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