You have a date-partitioned sales table with millions of rows. Many users run frequent aggregation queries (AVG, MAX, SUM) over only the past year; queries must reflect latest data and you want lower cost and latency. What should you do?

A materialized view is the best solution because it pre-computes and stores the aggregated results, significantly reducing query latency and cost for frequent aggregation queries. BigQuery automatically maintains materialized views, ensuring they reflect the latest data from the base table. Including a filter clause for the last year optimizes the materialized view to only store relevant data, further improving performance and cost efficiency for the specified use case. Creating a materialized view with partition expiration on the base table is incorrect because partition expiration on the base table would delete historical data, which might be needed for other queries. Creating a regular view is incorrect because views do not pre-compute data; they simply store the query definition, offering no performance or cost benefits for aggregation. Creating a new table with a scheduled query is less efficient and more complex to manage than a materialized view, as it requires manual scheduling and maintenance to keep the aggregated data up-to-date.