Skip to content

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

Sample questions

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?

  1. A4, 3, 3
  2. B3, 3, 2
  3. C4, 4, 3
  4. 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.

Question 2 · choose 1

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?

  1. A3 rows, none of them with NULL columns
  2. B5 rows: one with NULL order columns and one with NULL target columns
  3. C4 rows: one of them, order 3, with NULL target columns because nothing matches it
  4. 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.

Databricks documentation

Question 3 · choose 1

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?

  1. ALATAM (20), then EU (50)
  2. BAPAC (70), then EU (50)
  3. CUS (NULL), then LATAM (20)
  4. 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.

Question 4 · choose 1

A colleague's query uses mean(amount) and another uses avg(amount). How do the two functions relate in Databricks SQL?

  1. Amean is a synonym for avg
  2. Bmean returns the median and avg the arithmetic mean
  3. Cmean is an approximate version of avg for large tables
  4. Dmean works only as a window function
Show the answer and why
  • Amean is a synonym for avg

    Correct

    The mean aggregate function is a synonym for the avg aggregate function.

  • Bmean returns the median and avg the arithmetic mean

    Incorrect

    mean returns the mean of the group's values, the same as avg.

  • Cmean is an approximate version of avg for large tables

    Incorrect

    mean is not an approximate function; it is another name for avg.

  • Dmean works only as a window function

    Incorrect

    mean is an aggregate function, used like avg.

mean returns the mean calculated from the values of a group, and it is a synonym for the avg aggregate function.

Question 5 · choose 1

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?

  1. AIt returns the most frequent value in the group
  2. BSome value of the group; it is non-deterministic
  3. CIt returns the first value in the order the rows were written
  4. 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.

Question 6 · choose 1

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?

  1. Acust, region, order_id, cust, region, target
  2. Border_id, target, cust, region
  3. Corder_id, cust, region, target
  4. 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.

Databricks documentation

Question 7 · choose 1

A query ends with SORT BY order_date and returns rows that are not in order_date order across the whole result. Why?

  1. ASORT BY sorts rows only within each Spark partition
  2. BSORT BY sorts in descending order by default
  3. CSORT BY ignores DATE columns
  4. 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.

Practice domain 4 →Practice all domains →