Ultimate access to all questions.
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 }
]
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