Databricks Certified Data Engineer - Associate
Get started today
Ultimate access to all questions.
Comments
Loading comments...
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 }
]
[
{ "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