Databricks-DE-Assoc Data Transformation and Modeling Practice Question
A data engineer has a Delta table named `sales` with columns `sale_id`, `customer_id`, `amount`, and `sale_date`. They need to create a new table that contains only the `customer_id` and the total `amount` per customer for all sales in 2023. Which SQL statement correctly creates this aggregated table?
⚠ Common exam trap
Candidates often confuse the order of filtering and aggregation, such as using HAVING instead of WHERE for row-level filtering, or forgetting that GROUP BY must include all non-aggregated columns.
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
✓
CREATE TABLE customer_totals AS SELECT customer_id, SUM(amount) AS total_amount FROM sales WHERE YEAR(sale_date) = 2023 GROUP BY customer_id;
The correct approach uses a CREATE TABLE AS SELECT statement with a WHERE clause filtering for 2023 sales before aggregation, followed by GROUP BY customer_id and SUM(amount). This materializes the desired summary table. The other options either misuse HAVING, omit GROUP BY, or add an extra grouping column that changes the granularity of the result.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
CREATE TABLE customer_totals AS SELECT customer_id, SUM(amount) AS total_amount FROM sales WHERE YEAR(sale_date) = 2023 GROUP BY customer_id;
Why this is correct
This statement uses CREATE TABLE AS SELECT (CTAS) to create a new table from the query. It filters rows for 2023 using WHERE YEAR(sale_date) = 2023, groups by customer_id, and sums the amount. The result is a new Delta table with the required columns, meeting the scenario's goal. It is the correct and efficient way to materialize the aggregated data.
- ✗
CREATE TABLE customer_totals AS SELECT customer_id, SUM(amount) AS total_amount FROM sales WHERE YEAR(sale_date) = 2023 GROUP BY customer_id, sale_date;
Why it's wrong here
This statement groups by both customer_id and sale_date, which means it will produce one row per customer per sale date, not a single total per customer. The result would have multiple rows for each customer, each summing only the amounts for that specific date. This does not meet the requirement of total amount per customer across all 2023 sales.
- ✗
CREATE TABLE customer_totals AS SELECT customer_id, SUM(amount) AS total_amount FROM sales WHERE YEAR(sale_date) = 2023;
Why it's wrong here
This statement omits the GROUP BY clause, which is required when using an aggregate function like SUM alongside a non-aggregated column (customer_id). Without GROUP BY, the query would either error or return a single row with a non-deterministic customer_id, depending on SQL mode. It does not produce per-customer totals as needed.
- ✗
CREATE TABLE customer_totals AS SELECT customer_id, SUM(amount) AS total_amount FROM sales GROUP BY customer_id HAVING YEAR(sale_date) = 2023;
Why it's wrong here
The HAVING clause is used to filter groups after aggregation, but it cannot reference a column not in the GROUP BY or an aggregate. Here, sale_date is not grouped, so this query would fail. Even if it ran, it would filter groups based on an arbitrary sale_date within the group, not filter rows before aggregation. This does not correctly restrict to 2023 sales.
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.