Databricks Certified Data Engineer - Associate
Get started today
Ultimate access to all questions.
Comments
Loading comments...
Ultimate access to all questions.
A data engineer is tasked with processing data from an e-commerce application. They have two tables: orders and customers. The schema and sample data are as follows:
orders: order_id bigint, order_customer_id bigint, items array<int>
customers: customer_id bigint, customer_name string, customer_email string
The goal is to retrieve customer details along with the number of items in each of their orders. Which of the following code blocks achieves this?
A
SELECT c.customer_id, c.customer_name, c.customer_email, o.order_id, COUNT(o.item_id) as total_items FROM customers c INNER JOIN (SELECT order_id, order_customer_id, PIVOT(items) as item_id FROM orders) o ON c.customer_id = o.order_customer_id GROUP BY c.customer_id, c.customer_name, c.customer_email, o.order_id;_
B
SELECT c.customer_id, c.customer_name, c.customer_email, o.order_id, COUNT(o.item_id) as total_items FROM customers c INNER JOIN (SELECT order_id, order_customer_id, FLATTEN(items) as item_id FROM orders) o ON c.customer_id = o.order_customer_id GROUP BY c.customer_id, c.customer_name, c.customer_email, o.order_id;_
C
SELECT c.customer_id, c.customer_name, c.customer_email, o.order_id, COUNT(o.item_id) as total_items FROM customers c INNER JOIN (SELECT order_id, order_customer_id, EXPLODE(items) as item_id FROM orders) o ON c.customer_id = o.order_customer_id GROUP BY c.customer_id, c.customer_name, c.customer_email, o.order_id;_
D
SELECT c.customer_id, c.customer_name, c.customer_email, o.order_id, COUNT(o.item_id) as total_items FROM customers c INNER JOIN (SELECT order_id, order_customer_id, COLLECT_SET(items) as item_id FROM orders) o ON c.customer_id = o.order_customer_id GROUP BY c.customer_id, c.customer_name, c.customer_email, o.order_id;
E
SELECT c.customer_id, c.customer_name, c.customer_email, o.order_id, COUNT(o.item_id) as total_items FROM customers c INNER JOIN (SELECT order_id, order_customer_id, ARRAY_DISTINCT(items) as item_id FROM orders) o ON c.customer_id = o.order_customer_id GROUP BY c.customer_id, c.customer_name, c.customer_email, o.order_id;