Posted by Colin, Dec. 10, 2023, 1:19 p.m.

127 vs 129...why would we use a LEFT JOIN for 127 but and INNER JOIN for 129?

127. Average rating after 10th trip and 129. Top 3 and bottom 3 courier both use similar business logic.

Except one 127 requires a left join to achieve your results, while 129 requires an inner join.

If I'm not mistaken:
127's left join is basically saying "include all the trips that didn't get a rating and treat them as 0s in the average" (cuz of the 'ELSE 0' logic, which converts NULLS to 0)
129's inner join is saying "ignore all the trips that didn't get a rating in the average" (cuz if there is no rating, then the trip doesn't appear in the trip_rating table)

Can you explain the rationale there? Why would we treat these differently? Seems like we either want to treat the NULLs as 0s or not include NULLS at all, but shouldn't be different for each q.




SQLPad user: Leon Wei

- Leon (934)

Dec. 10, 2023, 6:34 p.m.