Design a Daily Slowest-Query Report for BigQuery with Fixed Day Windows

Design a system that finds the slowest query of every day from BigQuery, Google's cloud data warehouse: as queries finish, it determines which one took the longest to run on each day and makes that result available. Treat each day as a fixed window that starts and ends at midnight, not as a sliding 24-hour window. The report states only this goal and the fixed-window rule, so the scale, the exact meaning of "slowest" and who reads the results are open: settle them with the interviewer before designing. ```hint What one day's answer needs Think about how much state you must keep for a day to know its slowest query, and how that would change if the window slid instead of being fixed. ``` ```hint When is a day really over Consider a long query that starts before midnight and finishes after it, and an execution record that arrives late: when can you declare a day's answer final? ``` ### Clarifying Questions - Is the slowest query wanted for all of BigQuery, or per project, customer or region? Only the single slowest, or the top few? - What does "slowest" measure: wall-clock time from submission to completion, execution time only, or resources consumed? Do failed or cancelled queries count? - Does a query belong to the day it started or the day it finished, and in which time zone does a day begin? - How many queries finish per day, and how soon after a day ends must its answer be available? Is a live "slowest so far today" needed? - Where do the query execution records come from, and can they arrive late or more than once? ### What a Strong Answer Covers - Requirements and a scale estimate agreed first, including the grouping key and the definition of duration - A clear rule for which day a query belongs to, its time zone, and how queries that span midnight are handled - A capture path for query completion records that never slows down the queries being measured - Daily tumbling-window aggregation with constant state per group, partial results per shard and a final merge - Watermarks, late and duplicate records, and the point at which a day's result becomes final - Storage and an API for daily results, a batch alternative or reconciliation over the raw records, and monitoring ### Follow-up Questions - The requirement changes to the slowest query in the last 24 hours, updated continuously. What changes in your state and algorithm? - Return the 10 slowest queries per project per day instead of one. How do the per-group state and the merge change? - A query runs for two days. How does your design attribute it, and when is each day's result final? - How would you also report the slowest query per day for each query pattern, such as queries that share the same normalized text?