Aggregations, Joins, Set Operations and Filtering

What it is

These are the core query patterns an analyst writes in Databricks SQL: aggregates that summarize rows, joins that combine tables side by side, set operations that stack results, and sorting and filtering that shape what comes back.

The exam usually shows a short query and asks what it returns. Most questions turn on row counts and NULLs.

How it works

Aggregate functions

SELECT region,
       count(*) AS orders,
       approx_count_distinct(customer_id) AS customers,
       avg(amount) AS avg_amount
FROM main.sales.orders
GROUP BY region;

Use approx_count_distinct when a dashboard over billions of rows needs a fast distinct count and a small error is acceptable. For a quick set of summary statistics in a notebook, the results table also offers a Data Profile view.

Join types

Multiple keys are combined with AND in the ON clause, or listed in USING:

SELECT c.customer_id, c.region, o.amount
FROM main.sales.customers AS c
LEFT JOIN main.sales.orders AS o
  ON c.customer_id = o.customer_id AND c.region = o.region;

A filter on the right table in WHERE runs after the join and removes the NULL rows a left join kept, turning it into an inner join in effect. Put that condition in ON to keep unmatched rows. If the key is not unique on one side, the join multiplies rows, which inflates sums.

Set operations

SELECT customer_id FROM main.sales.store_orders
UNION ALL
SELECT customer_id FROM main.sales.web_orders;

Both queries need the same number of columns with compatible types, matched by position, not by name. UNION ALL is faster because it skips the duplicate check, so use it when duplicates are impossible or wanted.

Sorting and filtering

SELECT region, sum(amount) AS revenue
FROM main.sales.orders
WHERE order_date >= '2026-01-01' AND status IN ('paid', 'shipped')
GROUP BY region
HAVING sum(amount) > 10000
ORDER BY revenue DESC NULLS LAST
LIMIT 10;

How the exam asks about this

• "Count unique visitors over five billion events, a small error is fine, and the dashboard must be fast." Use approx_count_distinct.
• "Show every customer, including those with no orders." Use customers LEFT JOIN orders, with any order filter in the ON clause.
• "Combine two monthly extracts and keep every row, including repeats." Use UNION ALL.