Data Analyst Associate · Domain 4: Executing queries using Databricks SQL and Databricks SQL Warehouses · 20% of the exam
Task 4.3: Perform aggregate operations such as count, approximate count distinct, mean, and summary statistics; Write queries to combine tables using various join operations (inner, left, right, and so on) with single or multiple keys, as well as set operations like union and union all, including the differences between the joins (inner, left, right, and so on); Perform sorting and filtering operations on a table
count, approx_count_distinct, avg and percentiles with GROUP BY and HAVING, join types and set operators on concrete result sets, and ORDER BY, WHERE, LIMIT and QUALIFY.
Study it
Aggregates, joins and set operators in Databricks SQL
Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.
Question 1 · choose 1
The table payments has five rows (region, customer, status): ('EU', 'c1', 'paid'), ('EU', 'c1', 'paid'), ('EU', 'c2', 'refunded'), ('EU', 'c3', 'paid') and ('US', 'c4', 'paid'). An analyst runs SELECT region, count(*), count(DISTINCT customer), count(DISTINCT customer) FILTER (WHERE status = 'paid') FROM payments GROUP BY region. What does the EU row contain?
A4, 3, 3
B3, 3, 2
C4, 4, 3
D4, 3, 2
Show the answer and why
A4, 3, 3
Incorrect
The FILTER clause passes only the paid rows to the last aggregate, so c2 is not counted there.
B3, 3, 2
Incorrect
count(*) counts every EU row, including both rows for c1.
C4, 4, 3
Incorrect
DISTINCT counts each customer once, so the two c1 rows count as one.
D4, 3, 2
Correct
Four EU rows, three distinct customers, and among the paid rows the distinct customers are c1 and c3.
count(*) returns the number of rows in the group; with DISTINCT, count returns the number of unique values. A FILTER clause attached to an aggregate passes only the rows that match its condition to that aggregate, leaving the other aggregates in the same query unchanged.
orders has three rows (order_id, cust, region): (1, 'A', 'EU'), (2, 'A', 'US'), (3, 'B', 'EU'). targets has four rows (cust, region, target): ('A', 'EU', 100), ('A', 'EU', 120), ('B', 'US', 50), ('A', 'US', 70). How many rows does SELECT * FROM orders o FULL OUTER JOIN targets t ON o.cust = t.cust AND o.region = t.region return?
A3 rows, none of them with NULL columns
B5 rows: one with NULL order columns and one with NULL target columns
C4 rows: one of them, order 3, with NULL target columns because nothing matches it
D7 rows, because each order matches every target of its customer
Show the answer and why
A3 rows, none of them with NULL columns
Incorrect
That counts only matched rows, as an inner join would; a full join also keeps the unmatched rows of both sides.
B5 rows: one with NULL order columns and one with NULL target columns
Correct
Order 1 matches two targets, order 2 one, order 3 none; target ('B', 'US') matches no order. The unmatched rows get NULLs on the other side.
C4 rows: one of them, order 3, with NULL target columns because nothing matches it
Incorrect
Both ('A', 'EU') targets match order 1, giving two rows, and the unmatched target row is kept too.
D7 rows, because each order matches every target of its customer
Incorrect
The join uses both keys, so a row matches only when the customer and the region are equal.
An inner join returns rows with matching values in both relations; a full outer join returns all values from both relations and appends NULL values on the side that has no match. With both cust and region in the ON clause, order 1 joins two targets, order 2 one, and order 3 and target ('B', 'US') stay unmatched: 5 rows.
The table region_sales holds ('EU', 50), ('US', NULL), ('APAC', 70) and ('LATAM', 20) in the columns region and revenue. An analyst runs SELECT region, revenue FROM region_sales ORDER BY revenue LIMIT 2. Which rows are returned, in order?
ALATAM (20), then EU (50)
BAPAC (70), then EU (50)
CUS (NULL), then LATAM (20)
DLATAM (20), then US (NULL)
Show the answer and why
ALATAM (20), then EU (50)
Incorrect
That would need NULLS LAST; with an ascending sort and no null order given, NULLs come first.
BAPAC (70), then EU (50)
Incorrect
Without a direction, ORDER BY sorts ascending, not descending.
CUS (NULL), then LATAM (20)
Correct
The sort is ascending by default, and with no null order specified NULLs sort first in an ascending sort.
DLATAM (20), then US (NULL)
Incorrect
NULLs are not placed after the lowest value; in an ascending sort they come before all non-NULL values.
If no sort direction is given, ORDER BY sorts ascending. If no null order is given, NULLs sort first for ASC and last for DESC. LIMIT then keeps the first two rows of that order. Add NULLS LAST (or filter the NULLs) to get the lowest non-NULL revenues.
An analyst groups orders by order_id and wants to show one customer_name per order, knowing every row of an order has the same name. She uses any_value(customer_name). What does the documentation say about any_value?
AIt returns the most frequent value in the group
BSome value of the group; it is non-deterministic
CIt returns the first value in the order the rows were written
DIt raises an error when the group holds more than one value
Show the answer and why
AIt returns the most frequent value in the group
Incorrect
any_value returns some value of the group, not the most frequent one.
BSome value of the group; it is non-deterministic
Correct
any_value returns some value of expr for a group of rows; runs may return different values.
CIt returns the first value in the order the rows were written
Incorrect
No order is guaranteed; the function is non-deterministic.
DIt raises an error when the group holds more than one value
Incorrect
any_value accepts groups with several values and returns one of them.
any_value returns some value of expr for a group of rows and is non-deterministic: repeated runs may return different values. It suits columns whose value is the same within each group.
orders has the columns cust, region and order_id; targets has cust, region and target. Which columns, in which order, does SELECT * FROM orders JOIN targets USING (cust, region) return?
Acust, region, order_id, cust, region, target
Border_id, target, cust, region
Corder_id, cust, region, target
Dcust, region, then order_id, target
Show the answer and why
Acust, region, order_id, cust, region, target
Incorrect
With USING, the matching columns are not repeated for each table.
Border_id, target, cust, region
Incorrect
The columns used to match come first, not last.
Corder_id, cust, region, target
Incorrect
The matching columns are listed before the other columns of either table.
Dcust, region, then order_id, target
Correct
With USING, SELECT * shows each matching column once first, then the left table's other columns, then the right table's.
When you specify USING or NATURAL, SELECT * shows only one occurrence of each column used to match, first, followed by the columns of the left and then the right join table, excluding the columns joined on.
A query ends with SORT BY order_date and returns rows that are not in order_date order across the whole result. Why?
ASORT BY sorts rows only within each Spark partition
BSORT BY sorts in descending order by default
CSORT BY ignores DATE columns
DSORT BY needs a LIMIT clause to take effect
Show the answer and why
ASORT BY sorts rows only within each Spark partition
Correct
SORT BY returns rows sorted within each partition, so the result can be only partially ordered.
BSORT BY sorts in descending order by default
Incorrect
The issue is not the direction but the scope of the sort.
CSORT BY ignores DATE columns
Incorrect
SORT BY sorts by any expression; the limit is the partition scope.
DSORT BY needs a LIMIT clause to take effect
Incorrect
LIMIT is not required; SORT BY sorts within partitions.
SORT BY returns the result rows sorted within each Spark partition. When the data spans several partitions, the result might be only partially ordered; ORDER BY sorts the whole result.