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
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(...)) ?
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'....
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 :)
