Waymo·Data Analyst·Technical Phone Screen·Intermediate

Intermediate

Jun 2026

Summary

Waymo data analyst interview with a SQL question that looked clean on the surface but had a few gotchas buried in the requirements. Single technical question, no behavioral stuff from what I could tell.

Questions Asked (1)

You have an autonomous vehicle fleet database with models, vehicles, trips, and maintenance records. Write a production-ready SQL query that returns the top 5 most efficient vehicle models by miles per kWh, but only for models whose total maintenance cost is under 5000. Models with no maintenance records count as zero cost and must be included. Models with zero or null total energy consumption should be excluded rather than treated as infinitely efficient.

Data ModelingProduct Analytics & MetricsTechnical Trade-offs

Author's notes

The fanout problem is what got me initially.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and metric definitions, then build the query in stages: aggregate energy and maintenance per model, apply filters, and rank by efficiency. Use LEFT JOINs to include models without maintenance records and handle NULLs explicitly to avoid incorrect exclusions.

Pro tip: Always validate edge cases like models with zero energy consumption or no maintenance records; explicitly state your assumptions about data completeness and time windows to demonstrate production readiness.

1. Clarify schema and metric definitions

Confirm table relationships, column names, and how miles per kWh is calculated (e.g., total miles driven divided by total energy consumed).

2. Aggregate energy and maintenance per model

Use CTEs to sum energy consumption and maintenance costs for each model, ensuring LEFT JOINs so models without maintenance are included with zero cost.

3. Apply filters and handle edge cases

Exclude models with zero or NULL total energy, and filter out models with total maintenance cost >= 5000.

4. Rank and select top 5

Order the remaining models by miles per kWh descending and limit to 5, using window functions or ORDER BY with LIMIT.

Key Points to Mention

Use LEFT JOIN for maintenance records to include models with no maintenance (cost = 0).
Explicitly filter out models with total energy consumption <= 0 or NULL to avoid division by zero.
Calculate miles per kWh as total miles divided by total energy, ensuring correct aggregation.
Consider using COALESCE or IFNULL to handle NULL maintenance costs as zero.
Mention indexing or partitioning strategies for production performance on large datasets.
Validate results with edge cases like models with no trips or no energy data.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.