Answer-first summary for fast verification
Answer: ```sql select aggregate(flatten(collect_list(performance.sales)), 0, (x, y) -> x + y) as total_sales from sales ```
The correct approach is to use the `aggregate` function combined with `flatten` and `collect_list` to sum up all sales values from the `performance` column. This method correctly accesses the `sales` values within the struct data type using the `.` notation (`performance.sales`). Note: Option C is incorrect because it attempts to use `SLICE` on the `performance` column, which is not applicable here. Additionally, the use of `:` (as in `performance:sales`) is only appropriate for JSON data, not struct data types. Understanding the distinction between JSON and struct data types is crucial for the exam. Alternative solutions include using `reduce` instead of `aggregate` or combining `explode` and `sum` functions for the same result. For example: ```sql select reduce(flatten(collect_list(performance.sales)), 0, (x, y) -> x + y) as total_sales from sales ``` Or: ```sql with cte as ( select explode(flatten(collect_list(performance.sales))) sales from sales ) select sum(sales) from cte ```
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
No comments yet.
When working with a complex struct data type in SQL that contains employee and sales data, how would you calculate the total sales made by all employees? The table is defined with batchId INT, performance ARRAY<STRUCT>, and insertDate TIMESTAMP. Here's a sample of the performance column data:
[
{ "employeeId": 1234, "sales": 10000 },
{ "employeeId": 3232, "sales": 30000 }
]
[
{ "employeeId": 1234, "sales": 10000 },
{ "employeeId": 3232, "sales": 30000 }
]
Given the following SQL to create the table sales:
create or replace table sales as
select 1 as batchId,
from_json('[{ "employeeId":1234,"sales" : 10000 },{ "employeeId":3232,"sales" : 30000 }]',
'ARRAY<STRUCT>') as performance,
current_timestamp() as insertDate
union all
select 2 as batchId,
from_json('[{ "employeeId":1235,"sales" : 10500 },{ "employeeId":3233,"sales" : 32000 }]',
'ARRAY<STRUCT>') as performance,
current_timestamp() as insertDate
create or replace table sales as
select 1 as batchId,
from_json('[{ "employeeId":1234,"sales" : 10000 },{ "employeeId":3232,"sales" : 30000 }]',
'ARRAY<STRUCT>') as performance,
current_timestamp() as insertDate
union all
select 2 as batchId,
from_json('[{ "employeeId":1235,"sales" : 10500 },{ "employeeId":3233,"sales" : 32000 }]',
'ARRAY<STRUCT>') as performance,
current_timestamp() as insertDate
A
WITH CTE as (SELECT EXPLODE(performance) FROM table_name)
SELECT SUM(performance.sales) FROM CTE
WITH CTE as (SELECT EXPLODE(performance) FROM table_name)
SELECT SUM(performance.sales) FROM CTE
B
WITH CTE as (SELECT FLATTEN(performance) FROM table_name)
SELECT SUM(sales) FROM CTE
WITH CTE as (SELECT FLATTEN(performance) FROM table_name)
SELECT SUM(sales) FROM CTE
C
SELECT SUM(SLICE(performance, sales)) FROM employee
SELECT SUM(SLICE(performance, sales)) FROM employee
D
select aggregate(flatten(collect_list(performance.sales)), 0, (x, y) -> x + y) as total_sales from sales
select aggregate(flatten(collect_list(performance.sales)), 0, (x, y) -> x + y) as total_sales from sales