Uber's SQL Question & Answer | Crack ANY Interview with this 3 Step Approach

💻 For real-time updates on events, connections & resources, join our community on WhatsApp: jvn.io/wTBMmV0
Problem: platform.stratascratch.com/co...
Solution: www.stratascratch.com/blog/ty...
In this video, we are going to see how to solve a SQL interview question "Distance Per Dollar" asked at Uber.
Topics Covered:
00:00 - Introduction
00:18 - Distance Per Dollar
01:11 - Understanding Inputs & Outputs
02:13 - Articulate the Solution Verbally
03:38 - Construct the Query Step by Step
05:03 - Average Distance Per Dollar Difference
#jovian #programming #sql #uber #interviewquestions

[@ManojKumar-ez2lb]

Hmmm i think you read it correctly but the implementation of the query was wrong.
Check this out.
SELECT yearmonth, ROUND(ABS(AVG(dpd-madp)),2) AS aadv
FROM
(SELECT TO_CHAR(request_date, 'mm-yyyy') AS yearmonth,
distance_to_travel/monetary_cost AS dpd, SUM(distance_to_travel)
OVER(PARTITION BY TO_CHAR(request_date, 'mm-yyyy')) / SUM(monetary_cost)
OVER(PARTITION BY TO_CHAR(request_date, 'mm-yyyy')) AS madp
FROM uber_request_logs
) T1
GROUP BY yearmonth

[@ilyboc]

the first AVG averages over the group and the second one over the partition but why don't they return the same result. What is AVG(...) OVER (PARTITION BY DATE_FORMAT(...)) ?

[@Siddharth_Matada]

you didnt explain the third paragraph in the problem clearly...wrt dataset like success and failure requests, also what if requests dates are not unique as they specifically mentioned in problem like 'assume all dates are unique'....

[@jovianhq]

As the output column wanted specifically only YYYY-MM & the average distance difference - we've moved right into it. WRT request_status, they've asked us to consider both success & failure and if you run the request_status column, you'll notice that they're the only entries so nothing to filter. WRT assuming dates are unique, they've already asked us to assume they're unique so there's no need to check but if you want to check, you can do it by COUNT(request_date) & COUNT(DISTINCT request_date), you will get the same count.
Hope this gives some clarity :)