Analyze and present data
Free Google Cloud Associate Data Practitioner practice — 6 questions on Analyze and present data, with explanations. No sign-up. Full 12-question mixed test →
A query needs to identify the second-highest distinct sales amount within each region, treating duplicate sales values as a single rank (so if two reps tie for highest, the next distinct value is rank 2, not rank 3). Which window function should drive the ranking logic?
DENSE_RANK() assigns consecutive ranks with no gaps for tied values, so rank 2 always corresponds to the second-highest distinct sales value regardless of how many rows tie for first.
A BigQuery table named orders is partitioned by the DATE column order_date (daily partitions) and clustered by customer_id. A dashboard query uses WHERE DATE(order_date) = '2026-03-01' and scans far more of the 5 TB table than expected. What change will reduce bytes scanned?
Wrapping the partition column in a function like DATE() prevents BigQuery's query planner from recognizing it as a partition filter, forcing a full scan. Filtering directly on the unmodified partition column restores partition pruning so only the relevant day's partition is scanned.
Finance, Sales, and Marketing teams each build their own reports and frequently calculate 'net revenue' with slightly different logic, causing conflicting numbers in executive meetings. Leadership wants one governed definition of key metrics like net revenue that every team's dashboards must use consistently. Which approach BEST meets this requirement?
LookML measures defined once in a shared Explore act as a single governed source of truth for business logic. Any dashboard built on that Explore automatically inherits the same net revenue calculation, eliminating metric drift across teams.
A retail company wants to group customers into unlabeled segments based on purchasing behavior (recency, frequency, monetary value), with no predefined categories, for future targeted marketing campaigns. Which BigQuery ML model type is MOST appropriate?
KMEANS is BigQuery ML's unsupervised clustering algorithm, designed to group unlabeled data into segments based on feature similarity — exactly what's needed for behavior-based customer segmentation without predefined categories.
A Looker Studio dashboard runs a complex join-and-aggregate query against a 2 TB BigQuery table every time a viewer opens it, resulting in high query costs and slow load times. The underlying source data only changes once per day via a nightly batch load. What is the MOST cost-effective way to improve dashboard performance?
A materialized view precomputes and incrementally refreshes the aggregation results. Since the base data changes only once daily, the dashboard can query the small, cached materialized view instead of re-running the full join/aggregation on 2 TB every time, drastically cutting both cost and latency.
An analyst needs a running (cumulative) total of daily revenue per store, ordered by date, across a table with millions of rows per store. The current implementation uses a self-join (joining the table to itself on store_id where date <= current date) and is extremely slow and expensive. Which rewrite BEST improves performance?
An analytic window function with PARTITION BY, ORDER BY, and an explicit frame clause computes the cumulative sum in a single efficient pass over sorted partitioned data, avoiding the row-by-row comparison cost of a self-join.
Ready for the real thing?
The full course has two full-length practice tests, video lessons for every exam domain, hands-on labs and detailed answer explanations.
Start my full course on Udemy →