Noos
PT EN
RAN-DBDEA-00147
Ranvier · DBDEA

Exam DBDEA Topics: transformation Difficulty: medium

The table catalog.sales.order_items stores one row per line item, with columns order_id and item. An export job must return exactly one row per order, carrying all of its items in a single ARRAY<STRING> column named items — a native array, not a concatenated string. Which query shape produces this result?

Options (Reading Mode) Click to filter discussion →
  1. A. Explode the item column first, then concatenate the values into one delimited string
  2. B. SELECT order_id, collect_list(item) AS items FROM catalog.sales.order_items GROUP BY order_id
  3. C. Pivot the rows into one explicit column per item position, such as item_1 and item_2
  4. D. It is impossible in pure SQL, because aggregate functions cannot return complex types like arrays
Reveal answer

Correct answer: SELECT order_id, collect_list(item) AS items FROM catalog.sales.order_items GROUP BY order_id

Reference: https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-functions

Train this exam on Ranvier →

02 — The Myelin Layer · Canonical Discussion

Where the exam tries to trick you — and how those who passed solved it.

0 Passed

contributed to this question

Answer Distractor Matrix Click an option to filter or open its dissection.
✦ Consolidated Synthesis (Wiki) rev. #1 (baseline) · CC BY-SA 4.0
Start synthesis for this question →

No community synthesis consolidated for this question yet. Below is the verifiable official starting point — if you passed the exam, open the option dissection.

Official Answer (B): SELECT order_id, collect_list(item) AS items FROM catalog.sales.order_items GROUP BY order_id

Ranvier · DBDEA

Contribute to this Question

Your contribution builds your Orbital Sheath

Peer contributions

No public contribution yet. Be the first who passed to explain why the distractors trick candidates — your contribution builds your Orbital Sheath.