Answer-first summary for fast verification
Answer: ```sql SELECT store_id, employees, FILTER (employees, i -> i.year_exp > 5) AS exp_employees FROM stores ```
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.
Author: LeetQuiz Editorial Team
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