PDE Preparing and Using Data for Analysis Practice Question
Your team stores IoT sensor readings in a BigQuery table `project.sensors.readings` with columns `sensor_id` (STRING), `reading_time` (TIMESTAMP), and `temperature` (FLOAT64). You need to create a new table that adds a column `avg_temp_7d` containing, for each row, the average temperature of that sensor over the preceding 7 days (including the current row). Which SQL feature should you use to compute this efficiently?
⚠ Common exam trap
The trap here is assuming that a ROWS-based window frame (e.g., 7 PRECEDING) works for time-based rolling averages; it would instead include the previous 7 rows regardless of time gaps.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
A window function with a RANGE-based frame between 7 days preceding and CURRENT ROW
A window function with a RANGE frame based on the timestamp column correctly computes a rolling 7-day average per sensor. It is efficient because BigQuery processes the window in a single pass, and the RANGE clause dynamically adjusts the window based on the actual time values, not row counts. This meets the requirement of including the current row and the preceding 7 days.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
A GROUP BY on sensor_id and a date truncation to week, then joining back to the original table
Why it's wrong here
Grouping by week and joining back would produce weekly averages, not a rolling 7-day average that changes for each row. It also requires an extra join and does not handle overlapping windows correctly. This approach is incorrect for row-level rolling calculations.
- ✗
A user-defined function (UDF) that loops over the previous 7 days and accumulates temperatures
Why it's wrong here
A UDF that loops day by day would be inefficient and cannot easily access other rows in the table. BigQuery UDFs are not designed for row-by-row aggregation across a window; they operate on scalar inputs. This would be slow and complex compared to a native window function.
- ✗
A correlated subquery that selects the average temperature where reading_time is between the current row's time minus 7 days and the current row's time
Why it's wrong here
A correlated subquery can compute the same result, but it runs once per row and does not scale well on large tables. BigQuery may not optimize it as effectively as a window function, leading to slower performance and higher slot usage. The question asks for an efficient method, making this a less suitable choice.
- ✓
A window function with a RANGE-based frame between 7 days preceding and CURRENT ROW
Why this is correct
RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW defines a dynamic window based on the timestamp value, so each row's average includes all readings from the same sensor within the preceding 7 days. This is the correct approach for time-based rolling aggregates in BigQuery and avoids self-joins or manual date arithmetic.
About these practice questions
This PDE question is part of Courseiva's 747-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Google Cloud exam blueprint
This PDE practice question is part of Courseiva's free Google Cloud certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PDE exam.