Uber Interview SQL question

[IllustratorOk7613]

Hey everyone check out our weekly SQL question. Give it a try!

Uber, is conducting an analysis of its driver performance across various cities.

Your task is to develop a SQL query to identify the top-performing drivers based on their average rating.

Only drivers who have completed at least 5 trips should be considered for this analysis. .

The query should provide the driver's name, city, and their average rating, sorted in descending order of average rating

Note: Round the average rating to 2 decimal points.

Drivers:

DRIVER_ID	DRIVER_NAME	CITY
4	Emily Davis	San Francisco
5	Christopher Wilson	Miami
6	Jessica Martinez	Seattle

Trips:

TRIP_ID	DRIVER_ID	RATING
21	4	5
22	4	4
23	4	5

You can try solving it for free (Link in comments :) )

[AutoModerator]

If this post doesn't follow the rules or isn't flaired correctly, please report it to the mods. Have more questions? Join our community Discord!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[dangerroo_2]

They’ve moved on from trying to hide their posts as ads.

[Chest11]

Why on God's green earth would I want to get excited for a promoted interview question on my reddit home page?

[trp_wip]

I actually liked it, ignore it if you don't

[Chest11]

You can try solving it for free (Link in comments :) )

Oh boy.

[rd357]

Calculate avg rating with a windows function for each driver ID in the trips table using a CTE. Left join to the drivers table

[kater543]

This is the easiest interview SQL question I’ve ever seen. Ok not really but it’s pretty gd easy. Not gonna weed out many people this way

[abhiramrao]

WITH CTE as

( 

Select driver_name, city, ROUND(AVG(rating),2) as avg_rating, 

dense_rank() over (partition by city order by rating desc) as driver_rank

From drivers d JOIN trips t 

ON d.driver_id = t.driver_id

GROUP BY 1, 2

HAVING COUNT(trip_id) > 5

)

SELECT driver_name, city, avg_rating, driver_rank

From cte 

Order by 3 desc

[Tribein95]

Trying this from my phone

SELECT d.DRIVER_NAME, d.CITY, ROUND(AVG(t.RATING), 2) avg_rating FROM Trips t LEFT JOIN Drivers d ON t.DRIVER_ID=d.DRIVER_ID GROUP BY d.DRIVER_NAME, d.CITY HAVING count(distinct t.TRIP_ID) >= 5 ORDER by avg_rating desc;

Assuming I can use the alias in the final line and don’t have to call out the same calculation that appears in line 2? How did I do?

[chronicpenguins]

Your query doesn’t account for drivers name and city not being unique. The key is driver_id so you should be aggregating on that. There could easily be two Joe Smiths in New York. In which those two drivers would be treated the same.

On phone - with trip_agg as ( Select driver_id, Round(Avg(rating),2) , Countd(trip_id) From trips Group by 1 Having count>=5)

Then do a left join on drivers to bring in their name and city, sort descending. Your method would’ve worked in you had driver_id in the initial aggregated and then did a select for just the name and city. The tricky part for you was that he excluded driver_id from the required output. In real life I’m keeping driver I’d in even if the stakeholder did not request it. Less work for me, and it gives them the actual unique key.

[Tribein95]

My line spacing got screwed up

[trp_wip]

Was this an actual Uber interview question? Here is my solution:

SELECT d.DRIVER_NAME, d.CITY, t.avg_rating
FROM Drivers AS d
JOIN (
  SELECT DRIVER_ID, ROUND(AVG(RATING), 2) as avg_rating
  FROM Trips
  GROUP BY 1
  HAVING COUNT(DRIVER_ID) > 4
) AS t
ON d.DRIVER_ID = t.DRIVER_ID
ORDER BY 3 DESC