Swiggy SQL Interview Question | HackerRank Assessment

[@vijaygupta7059]

Select del_partner , count( case when predicted_time < datediff(MINUTE,order_time,deliver_time) then 1 else null end) as Order_count from order_details group by del_partner

[@pravinprince3221]

Thank you for the wonderful video mam

[@kritiknagar6089]

Very supportive Content

[@Team_Kanya_raasi]

with cte as( select *, case when DATEDIFF(MINUTE,order_time,deliver_time) > predicted_time then 'yes' when DATEDIFF(MINUTE,order_time,deliver_time) < predicted_time then 'no' else 'on time_delivery' end flag from order_details ) select del_partner,count(cte.flag) delay_order_count from cte where cte.flag = 'yes' group by del_partner

[@shedizon5978]

In the video the column order_date was not order_date but date, Thank GOD that at this age of mine, at 53 made it this far querying just for watching videos on KZbin! Thank You for your videos! and GOD Bless! SELECT del_partner, COUNT(*) AS order_count FROM order_details WHERE TIMESTAMPDIFF (MINUTE,CONCAT( DATE(order_date), ' ', order_time), CONCAT( DATE(order_date), ' ', deliver_time)) > predicted_time GROUP BY del_partner;

[@radhikadaksh3251]

Good explanation

[@sv1857]

Hey nishita I like your teaching pattern please give more questions for SQL joins and cte.

[@shahzan525]

Please post the video how count and min function working with partition/window functy

[@hairavyadav6579]

I think without concat we also get excepted output because we need only delay order

[@rajanchaurasiya1734]

with order_stat as( select *, timestampdiff(minute,order_time,deliver_time) as "time_diff", case when timestampdiff(minute,order_time,deliver_time) = predicted_time then "Not delayed" else "Delayed" end as "order_status" from order_details ) select del_partner, count(order_status) as "order_count" from order_stat where order_status = "Delayed" group by del_partner;

[@ARUNVG-si5kt]

with cte as( select *,DATEDIFF(MINUTE,order_time,deliver_time) as actual_time from order_details) select del_partner,count(orderid) as delayed_orders from cte where actual_time>predicted_time group by del_partner

[@vikassolanki297]

With cte as( select *, datediff(minute,order_time,deliver_time) as time_diff from order_detailss) select del_partner, count(*) from cte where time_diff > predicted_time group by del_partner

[@DataToInfo]

In dataset, the column name is 'order_date' not 'date'. Anyway, good example.

[@Savenature635]

select del_partner,sum(case when predicted_time < timestampdiff(minute,order_time,deliver_time) then 1 when predicted_time>= timestampdiff(minute,order_time,deliver_time)then 0 end) as delayed_orders from swiggy_order_details group by 1;

[@Saloni_Deep]

Thanks

[@m-43-prateekkumar27]

Useful..

[@arjundev4908]

with cte as(SELECT *, date_add(order_time,interval predicted_time minute) AS Actual_time from order_details),v1 as( select *, case when deliver_time

[@rohit_vora]

with cte as (select del_partner, round(extract(epoch from (deliver_time::time - order_time::time))/60) as totl_time, predicted_time from order_details) select del_partner, count(1) cnt from cte where totl_time > predicted_time group by 1

[@Rahul-l8s7f]

Hey, nishtha😀, amazing content. question: what to do when i am getting error like "not recognized built-in function name". thanks ♥

[@dataenggstudies]

select del_partner, count(*) from order_details where timestampdiff(minute, order_time, deliver_time) > predicted_time group by del_partner ;

[@iswaryanandini2171]

Where do you practice this with a live data set

[@echodelta7680]

I often download the CSV file from KZbin descriptions of different channels, import it into Workbench and practice different types of queries. Also, you can directly access huge datasets on Google BigQuery on cloud and run SQL there itself. :) When I was teaching myself Power BI few months ago, I would export the result set generated in Workbench as CSV to Power BI for creating visualizations and dashboards. It used be good hands-on for data analytics pipeline. :)

[@anshusharaf2019]

with cte as ( SELECT *, (strftime('%s', deliver_time) - strftime('%s', order_time)) / 60 AS difference_in_minutes FROM order_details ) select del_partner, count('*') as cnt from cte where predicted_time < difference_in_minutes GROUP by del_partner in SQL Online IDE you can replace (strftime('%s', deliver_time) - strftime('%s', order_time)) / 60 AS difference_in_minutes with "TIMESTAMPDIFF(SECOND, departure, arrival) AS difference"

[@vishalmane3139]

Kya h ye

[@SanjeevSingh-ep9hu]

Wow

[@ganeshgani7988]

Please make video on Date analyst opening

[@vishalmane3139]

0

[@SnapMathShorts]

My Approach : select del_partner, count(case when predicted_time < cast (EXTRACT(EPOCH FROM (deliver_time - order_time)) / 60 as int) then 1 end ) as order_count from order_details group by 1;

[@echodelta7680]

What is the order of processing in following line? DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) Does the partitioning of departments happen first, followed by ordering of salaries within those partitions and finally ranking of rows? Or does it happen in some other order? Kindly enlighten.

[@Iamthebestthing]

Great. Pin to pin detail video

[@yashmehta6920]

It's my solution which is lengthy compared to video explanation just because of i don't know about TIMESTAMPDIFF function WITH cte3 AS ( SELECT *, HOUR(TIME(deliver_time - order_time)) * 60 + MINUTE(TIME(deliver_time - order_time)) AS Actual_time FROM order_details ), cte4 AS ( SELECT *, CASE WHEN Actual_time > predicted_time THEN 'Delayed' ELSE 'On Time' END AS delivery_cat FROM cte3 ) SELECT del_partner, COUNT(delivery_cat) FROM cte4 WHERE delivery_cat = 'Delayed' GROUP BY del_partner; Please give feedback if you like..

[@jatinjangir2293]

HI, Please find the below solution with cte as ( select a.*,del_partner as dp,EXTRACT(EPOCH FROM (deliver_time - order_time)) / 60 AS act_time_taken from order_details a) select dp as del_partner,count(case when a.act_time_taken > a.predicted_time then 'Delayed' end) order_count from cte a group by dp order by 1;

[@Mind_hit]

select del_partner,count(status) as order_count from( select *,case when round(time_to_sec(deliver_time-order_time)/60) predicted_time then 'delayed' else 'on time' end as status from order_details)as x where x.status='delayed' group by x.del_partner ;

[@mohanbharadwaj7684]

solution in SQL server management: with cte as ( select city, order_time, del_partner,deliver_time, predicted_time, datediff(minute, order_time, deliver_time ) as time_diff from orders ) select del_partner, count(*) as cnt from cte where time_diff > predicted_time group by del_partner

[@MubarakAli-qs9qq]

Optimised solution best

[@rushalishetty8600]

WITH CTE(SELECT *,TIMESTAMPDIFF(MINUTE,order_time,deliver_time) as del_min FROM order_details) SELECT del_partner,COUNT(*) FROM CTE WHERE predicted_time

[@Rogeshwaran]

I'm using this method because it is easy to understand.

[@sagartayde1185]

select del_partner, count(*) as order_count from order_details where datediff(minute,order_time, deliver_time) > predicted_time group by del_partner

[@saiteja-gb8ho]

Here is solution in SQL Server: SELECT del_partner, SUM( CASE WHEN DATEDIFF(minute, order_time,deliver_time) >predicted_time then 1 else 0 END ) CNT FROM order_details group by del_partner

[@HARSHRAJ-gp6ve]

WITH cte AS ( SELECT del_partner, order_time, deliver_time, predicted_time FROM order_details ),cte1 as( SELECT cte.*, TIMESTAMPDIFF(MINUTE, order_time, deliver_time) AS x2 FROM cte ) select del_partner,COUNT(*) as order_count FROM cte1 where x2>predicted_time GROUP BY del_partner;