Databricks-DE-Assoc Data Transformation and Modeling Practice Question
A data engineer maintains a Delta table named inventory.products with columns product_id, category, price, and updated_at. The engineer needs to create a new table that contains one row per category with the average price and the most recently updated product_id in that category. The query must be efficient and use only standard Databricks SQL. Which statement should the engineer run?
⚠ Common exam trap
The trap here is believing that FIRST or LAST combined with an ORDER BY on the outer query can reliably select the row with the maximum updated_at inside each group.
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
✓
SELECT category, AVG(price) AS avg_price, MAX_BY(product_id, updated_at) AS latest_product FROM inventory.products GROUP BY category
MAX_BY is designed exactly for the pattern of retrieving a value associated with the maximum of another column within a group. Grouping by category and applying AVG for the price and MAX_BY for the product_id returns the average price and latest product per category in one aggregation. The alternatives rely on non-deterministic ordering or array indexing, which do not guarantee the row with the greatest updated_at.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
SELECT category, AVG(price) AS avg_price, COLLECT_LIST(product_id)[0] AS latest_product FROM inventory.products GROUP BY category
Why it's wrong here
COLLECT_LIST gathers all product_id values into an array, but the array order is not guaranteed to follow updated_at. Taking index 0 yields an arbitrary element, not the product with the most recent update. It also materializes unnecessary arrays, increasing memory use compared with a targeted aggregate such as MAX_BY.
- ✗
SELECT category, AVG(price) AS avg_price, LAST(product_id) AS latest_product FROM inventory.products GROUP BY category ORDER BY updated_at
Why it's wrong here
LAST is not a deterministic aggregate in this context; it depends on row order, which is not guaranteed after a GROUP BY. Ordering the final result by updated_at does not control which product_id is retained inside each category group. The query may return an arbitrary product_id rather than the one with the maximum updated_at, making the result unreliable.
- ✗
SELECT category, AVG(price) AS avg_price, FIRST(product_id) AS latest_product FROM inventory.products GROUP BY category ORDER BY updated_at DESC
Why it's wrong here
FIRST returns the first value encountered in the group, and the outer ORDER BY updated_at DESC only sorts the final output rows, not the rows inside each aggregation group. There is no guarantee that the first product_id seen per category corresponds to the maximum updated_at. This produces a plausible but incorrect latest product for each category.
- ✓
SELECT category, AVG(price) AS avg_price, MAX_BY(product_id, updated_at) AS latest_product FROM inventory.products GROUP BY category
Why this is correct
MAX_BY is a Databricks SQL aggregate function that returns the value of the first argument associated with the maximum value of the second argument. Grouping by category and using MAX_BY(product_id, updated_at) yields the product_id with the latest updated_at per category, while AVG(price) gives the average price. This is a single-pass aggregation with no self-join.
About these practice questions
Courseiva writes every Databricks-DE-Assoc question from scratch — 276 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or 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 Databricks exam blueprint
This Databricks-DE-Assoc practice question is part of Courseiva's free Databricks 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 Databricks-DE-Assoc exam.