LeetCode Medium 1174 Interview SQL Question with Detailed Explanation

In this video I solve and explain a medium difficulty leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question includes points to keep in mind to develop SQL queries.

LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.

If you found this helpful, Like and Subscribe to the channel for more content.

#LeetCodeSQL #FAANG #SQLinterviewQuestions

[venkateshb]

Your explaining way is beautiful and impressive. Beautifully you write sql in step by step by step. Very nicely u explaining how to write sqls.

[maheshwaroli]

with cte as(select delivery_id, customer_id, order_date,
CASE WHEN THEN "immediate" ELSE "scheduled" END AS del_type,
RANK() OVER(PARTITION BY customer_id order by order_date) as rnk
from delivery)
select ROUND((SUM(del_type="immediate")/count(*))*100, 2) as immediate_percentage from cte where rnk=1;

[meenayegan]

A minor variation:

select round(count(case when order_date = cutomer_pref_delivery_date then 1 end)/
cast(count(*)as float)* 100, 2)
from
(
select * from
(
select *,
row_number()
over (partition by customer_id order by order_date) as first_order
from Delivery
)x

where first_order=1
)y

[mickyman]

# Write your MySQL query statement below

with cte1 as
(select *, first_value(order_date) over(partition by customer_id order by order_date)
as first_order from delivery)
select round((select count(*) from cte1 where
and order_date=first_order)/(
select count(*) from cte1 where order_date=first_order)*100, 2) as immediate_percentage;

[apiitg]

in an actual interview what is the time expectation in which the candidate needs to solve the problem?

[gbemisolaadewuya]

The Logic must be really thought through

[yashnikhare]

with data1 as (
select *, min(order_date) over(partition by customer_id order by customer_id) as min_ord,
over(partition by customer_id order by customer_id) as min_del
from Delivery ),
data2 as (

select *, (case when ((min_ord)=(min_del)) then 1 else 0 end ) as cn from data1
)
select round(((sum(cn)*1.0/count(cn)*1.0)*100), 2) from data2

[apiitg]

my solution
SELECT
round(sum(IF(order_date = customer_pref_delivery_date, 1, 0))*100/count(*), 2) AS immediate_percentage
FROM DELIVERY d
WHERE order_date = (
SELECT MIN(order_date)
FROM DELIVERY
WHERE customer_id = d.customer_id );