Hopper Company Queries III

Hopper Company Queries III

What is this problem about?

The final part of the series, Hopper Company Queries III interview question, asks for a 3-month rolling average of ride distance and ride duration for each month from January to October 2020. Specifically, for month $n$, you calculate the average of (month $n$, $n+1$, and $n+2$). This is a classic "Moving Average" problem applied to a relational database schema.

Why is this asked in interviews?

Uber uses this to test proficiency with Window Functions and complex temporal windows. Calculating a moving average is a standard task in financial and operational analytics. It evaluations whether a candidate can use the OVER clause with specific ROWS or RANGE frames to aggregate data across a shifting boundary of rows.

Algorithmic pattern used

This problem uses Monthly Aggregation followed by a Rolling Window Function.

1. Aggregate total distance and duration for each month of 2020.
2. Use the AVG() OVER() window function.
3. Define the frame as BETWEEN CURRENT ROW AND 2 FOLLOWING.
   • Note: The problem asks for the average of the current month and the next two.
4. Limit the results to the first 10 months (Jan-Oct) because Nov and Dec don't have two subsequent months to complete a 3-month window.

Example explanation

• Jan: 100 miles, Feb: 200 miles, Mar: 300 miles.
• The result for January would be $(100+200+300) / 3 = 200$.
• Then for February, it would be (Feb+Mar+Apr) / 3.

Common mistakes candidates make

• Incorrect Window Frame: Using PRECEDING instead of FOLLOWING, or including only 2 months total.
• Missing months: Failing to join with a month series, causing the "3-month window" to skip months with no rides, which leads to incorrect averages.
• Data Type Issues: Not rounding the final average to two decimal places.

Interview preparation tip

Moving averages are a "Tier 1" SQL concept. Master the ROWS BETWEEN X PRECEDING AND Y FOLLOWING syntax. It is the most efficient way to perform trend analysis in databases without writing expensive self-joins.