Klaviyo OA 面试真题解析：Expected Outcomes For Managers Only（MBTA Webhook 数据处理）

Before you begin

This challenge is about processing real-time data from a webhook hosted by the Massachusetts Bay Transportation Authority (MBTA). For context, the MBTA is responsible for boat, train, bus, and commuter rail public transportation in the Greater Boston Area.

You will be improving this web application in collaboration with your interviewer(s). Feel free to ask them any questions around the requirements of the challenge, syntax, concepts, or library-related questions. Note that we are not going to run this code; we just want to work together and understand your thinking.

The scenario

This web application receives webhooks with status updates about all MBTA vehicles as they move through their routes. These updates are recorded in the database and will eventually be used to analyze where MBTA Commuter Rail trains are speeding, however nothing but your code is using the data yet. This application was made quickly, without code review. No one has confirmed the quality of this code.

Expected outcomes

You are now the owner of this application. We want you to make any and all changes necessary to improve the existing code and verify that the product requirements are met!

Our main priority is to address the product requirements which will be described later.

Additionally, we want you to update the existing code so that it is readable/maintainable for future developers, and can scale without performance concerns.

For Managers Only

We want our managers to be technical. Please drive this interview as if you were the lead engineer of a team working on this challenge.

Product requirements

• Save a log of speeding incidents to the database. Only log incidents for trains that meet both of the following criteria:
• The train must be a commuter rail train.
• The train must be going over 10 miles per hour.
• Know how many times a specific train has been speeding and where.
• You will not need to implement this function, but your data model must be able to satisfy this requirement.

What the current code does

• The web application has an endpoint in the server.py file which receives webhooks from the MBTA. An example webhook payload can be found in the samples/webhook-payload-sample.json file.
• That payload is then passed to a function defined in the processor.py file which contains the bulk of the business logic for this project.
• The code then calls the MBTA API to check if the train is a commuter rail train. An example response from the MBTA API can be found in the samples/mbta-api-route-raw-response.json file.
• The application uses two tables:
• train_lookup to count the number of times a train has been seen speeding. The schema for this table is defined in the samples/train-lookup-table-schema.sql file.
• train_log to log the speed and location a train has been seen speeding (above 10 MPH). The schema for this table is defined in the samples/train-log-table-schema.sql file.

这是一个围绕 MBTA 实时 webhook 的后端题，核心任务是判断收到的车辆状态更新是否属于通勤铁路列车，并且速度是否超过 10 MPH；只有同时满足这两个条件时，才应该把超速事件写入数据库。题目还要求数据模型能够支持后续查询“某一列车超速了多少次、分别发生在哪里”，因此设计时要兼顾事件记录表和按列车汇总的查询能力。整体上，解题重点通常在于正确解析 webhook、调用外部 API 校验列车类型、过滤业务条件，并让数据库写入逻辑具备可维护性和可扩展性。