Explanation:
Option A is correct because it correctly uses the FILTER higher-order function to apply custom logic at scale. Other options are either using incorrect source columns, have syntax errors, or do not use the FILTER function as required.
Ultimate access to all questions.
As a data engineer, you are tasked with identifying employees who have more than 5 years of experience from a dataset. The data is stored in a table named stores, where employee information is contained in an array column called employees. Your goal is to create a new column, named exp_employees, which will hold an array of employees with over 5 years of experience for each row in the table. To efficiently apply this custom logic at scale, you should use the FILTER higher-order function. Which of the following code blocks successfully completes this task?
A
SELECT store_id, employees, FILTER (employees, i -> i.year_exp > 5) AS exp_employees FROM stores
SELECT store_id, employees, FILTER (employees, i -> i.year_exp > 5) AS exp_employees FROM stores
B
SELECT store_id, employees, FILTER (exp_employees, i -> i.year_exp > 5) AS exp_employees FROM stores
SELECT store_id, employees, FILTER (exp_employees, i -> i.year_exp > 5) AS exp_employees FROM stores
C
SELECT store_id, employees, FILTER (employees, year_exp > 5) AS exp_employees FROM stores
SELECT store_id, employees, FILTER (employees, year_exp > 5) AS exp_employees FROM stores
D
SELECT store_id, employees, CASE WHEN employees.year_exp > 5 THEN employees ELSE NULL END AS exp_employees FROM stores
SELECT store_id, employees, CASE WHEN employees.year_exp > 5 THEN employees ELSE NULL END AS exp_employees FROM stores