Published November 14, 2026 · By Henry Palma

Databricks SQL Editor: GROUP BY, Joins, Common Table Expressions and Windows (Part 2 of 3)

Part 2 of a Databricks SQL Editor series: GROUP BY and HAVING, inner vs left joins, common table expressions, and window functions for ranks and running totals.

GROUP BY collapses rows into groups, HAVING filters those groups, the join type decides which rows survive, and window functions rank rows without collapsing them. This post follows Part 2 of the video series on the Databricks SQL Editor in Free Edition, using the same eight fictional orders and the same serverless SQL warehouse as Part 1.

Watch on YouTube

The SQL file and cheat sheet are in the Part 2 folder on GitHub.

Start from the baseline

Every statement in Part 2 starts with the same orders common table expression (CTE) from Part 1, plus a valid CTE that keeps rows with both a quantity and a unit price. The first statement returns the baseline: 6 valid orders and 142.50 in revenue. If that number is ever different, fix it before you aggregate.

GROUP BY changes the grain

SELECT region, category, COUNT(*) AS order_count, SUM(order_total) AS revenue
FROM valid
GROUP BY region, category
ORDER BY region, category;

Instead of one row per order, you get one row per region and category. Every column in the SELECT list must be in the GROUP BY or inside an aggregate function.

| region | category | order_count | revenue | |---|---|---|---| | EAST | Books | 1 | 12.00 | | NORTH | Books | 1 | 20.00 | | NORTH | Toys | 2 | 60.00 | | SOUTH | Toys | 1 | 25.00 | | WEST | Books | 1 | 25.50 |

The counts add up to 6 and the revenue to 142.50, so nothing was lost or double counted.

WHERE filters rows, HAVING filters groups

WHERE runs before grouping, so it cannot use an aggregate like SUM. HAVING runs after grouping, so it can.

SELECT region, category, COUNT(*) AS order_count, SUM(order_total) AS revenue
FROM valid
GROUP BY region, category
HAVING SUM(order_total) >= 25
ORDER BY revenue DESC, region, category;

Three groups pass: NORTH Toys (60.00), WEST Books (25.50) and SOUTH Toys (exactly 25.00, kept because the comparison is >=). EAST Books and NORTH Books are removed as groups, even though their individual rows were valid.

INNER JOIN vs LEFT JOIN

A customers CTE maps each customer name to a loyalty tier: gold, silver or bronze.

SELECT v.order_id, v.customer, c.tier, v.order_total
FROM valid v
INNER JOIN customers c ON v.customer = c.customer
ORDER BY v.order_id;

The inner join returns 5 rows, not 6. Order 105 disappears because its customer is null, and null never equals anything, not even another null. This is one of the most common ways rows silently vanish from a report.

Change one word to LEFT JOIN and all 6 valid orders come back. Order 105 returns with a null customer and a null tier.

| Join | Rows | Revenue | |---|---|---| | INNER JOIN | 5 | 130.50 | | LEFT JOIN | 6 | 142.50 |

Same data, same tiers, a different join type, and a 12.00 difference in revenue. Pick the join on purpose, and count your rows before and after.

CTEs as named steps

WITH orders AS (...),
valid AS (
  SELECT region, CAST(quantity * unit_price AS DECIMAL(12,2)) AS order_total
  FROM orders
  WHERE quantity IS NOT NULL AND unit_price IS NOT NULL
),
by_region AS (
  SELECT region, COUNT(*) AS order_count, SUM(order_total) AS revenue
  FROM valid
  GROUP BY region
)
SELECT region, order_count, revenue
FROM by_region
ORDER BY region;

Each block in the WITH clause is a named step you can read top to bottom like a pipeline and test on its own. The result is EAST 1 order and 12.00, NORTH 3 orders and 80.00, SOUTH 1 and 25.00, WEST 1 and 25.50. It still reconciles to 6 orders and 142.50.

Window functions keep every row

ROW_NUMBER() OVER (
  PARTITION BY category
  ORDER BY order_total DESC, order_id DESC
) AS category_rank

Unlike GROUP BY, a window function adds a calculated column and keeps every row. In Books, 103 ranks first (25.50), then 101, then 105. In Toys, 108 and 104 both total 30.00, so the order_id tie-breaker puts 108 first, then 104, then 102.

A running total uses SUM over a window ordered by date and order ID, with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With no partition, it runs across all valid orders: 20.00, 45.00, 70.50, 100.50, 112.50 and finally 142.50. The last row matches the baseline, a built-in check that every valid order was counted exactly once.

Practice challenge

Left join the valid orders to customers and group by tier.

Without a HAVING clause, which tier groups appear, and how many orders does each have?
Why do COUNT(*) and COUNT(tier) differ in one group?
What is left after HAVING COUNT(*) >= 2?

Answers: gold has 3 orders, silver 1, bronze 1, and a fourth group with a null tier holds order 105. GROUP BY puts all null tiers into one group. In that group, COUNT(*) is 1 but COUNT(tier) is 0, because counting a column skips nulls. After the HAVING, only gold remains, with 3 orders.

Exam traps from Part 2

WHERE filters rows and HAVING filters groups.
An inner join drops rows without a match, including null keys. A left join keeps every left row.
GROUP BY collapses rows. Window functions keep them.
Always give a ranking a tie-breaker.

Next, Part 3 saves a summary table, builds a chart in the SQL Editor, and finds the work again with saved queries and query history.