CERTIFIED-DATA-ANALYST-ASSOCIATE Question #97: Real Exam Question with Answer & Explanation

Question

A data analyst is working with the following table my_table: customer_name dollars_spent Hex Sprockets [125.34, 100.15, 9003.99] Dented Fenders [16.99, 200.85, 33.49, 88.17] The analyst now wants to divide each value in the dollars_spent array by 100 to get the spend in terms of hundreds of dollars using the following code block: SELECT customer_name, _______ FROM my_table Which line of code can be used to fill in the blank so that the above code block successfully completes the task?

Options

• ATRANSFORM(hundreds_spent, dollars_spent / 100)
• BTRANSFORM(dollars_spent, value / 100) AS hundreds_spent
• DTRANSFORM(dollars_spent, dollars_spent / 100) AS hundreds_spent

Explanation

Option C is not included in the choices you've provided - only A, B, and D are listed, yet the stated correct answer is C. This looks like a copy/paste error in the question. That said, here's what can be explained from the options given:

Why the distractors are wrong:

• Option A - TRANSFORM(hundreds_spent, dollars_spent / 100) - is wrong because the first argument must be the existing array column (dollars_spent), not hundreds_spent, which doesn't exist yet.
• Option D - TRANSFORM(dollars_spent, dollars_spent / 100) AS hundreds_spent - is wrong because inside TRANSFORM, you cannot reference the original array column name (dollars_spent) as the element variable; you need a separate lambda/element alias to refer to each individual value.
• Option B - TRANSFORM(dollars_spent, value / 100) AS hundreds_spent - may be wrong depending on the SQL dialect; value is typically not a recognized implicit element variable unless the platform defines it that way.

The likely correct syntax (Option C) would be something like TRANSFORM(dollars_spent, x -> x / 100) AS hundreds_spent, using a proper lambda expression.

Memory tip: Think of TRANSFORM as a "for each" loop - the first argument is what array to loop over, and the second is what to do to each element using a placeholder variable of your choosing (e.g., x -> x / 100).

You may want to double-check the original source of this question to retrieve the missing Option C.

No community discussion yet for this question.