Workarounds & Engine Limits

Concepts covered: Execution Order

Window functions are evaluated in the SELECT step, after FROM, WHERE, GROUP BY and HAVING and before ORDER BY and LIMIT. Most of the errors they throw follow from that position, and so do the workarounds: anything the engine refuses in one step can be computed in a CTE and consumed in the next.

4 rules and their workarounds

Aggregates inside windows are allowed

One nesting the engine accepts is an aggregate inside a window after GROUP BY. SUM(amount) is the group's total, and SUM(SUM(amount)) OVER () is the total across all groups, computed over the grouped rows rather than the raw table. That gives each group's share of the grand total in one query, and the window's single partition holds only as many rows as there are groups.

SELECT
  customer_id,
  SUM(amount) AS cust_total,
  SUM(SUM(amount)) OVER () AS grand_total,
  ROUND(CAST(SUM(amount) AS DOUBLE) / SUM(SUM(amount)) OVER (), 3) AS share
FROM orders
GROUP BY customer_id
ORDER BY customer_id

Read the inner SUM as done by GROUP BY and the outer SUM as done by the window over the 2 grouped rows. The empty OVER () that was a warning sign earlier, under skewed partitions, is harmless here: it covers 2 rows rather than 5, and on production data one row per customer rather than one per order.

Layering windows with CTEs

Ranking customers by their windowed total needs 2 windows where the second orders by the first's output. The engine rejects DENSE_RANK() OVER (ORDER BY SUM(amount) OVER (PARTITION BY customer_id)), so the first window goes into a CTE and the second reads its column like any other.

WITH totals AS (
  SELECT
    order_id,
    customer_id,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS cust_total
  FROM orders
)
SELECT
  order_id,
  customer_id,
  amount,
  cust_total,
  DENSE_RANK() OVER (ORDER BY cust_total DESC) AS cust_rank
FROM totals
ORDER BY cust_rank, order_id

Each layer costs a sort of its own, one on customer_id and one on cust_total, which is the price of the second window and the reason to ask whether a GROUP BY would serve: ranking customers rather than orders needs only one row per customer, and DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) after GROUP BY does it in a single query, because an aggregate inside a window is the nesting the engine allows.

Rank customers by their total spend and keep every order of the top-ranked customer. Put the tiles in order so each window reads a column that already exists at its step and the filter comes after the rank. One tile does not belong in the query.

Distinct counts over a partition

COUNT(DISTINCT device) OVER (PARTITION BY user_id) throws an error, yet the question, how many different devices has this user logged in from, next to every login row, comes up constantly. DENSE_RANK answers it: ranked ascending by device, the last distinct device gets a rank equal to the distinct count; ranked descending, the first does. For any row, ascending rank plus descending rank minus 1 equals the number of distinct devices in the partition.

SELECT
  user_id,
  device,
  login_date,
  DENSE_RANK() OVER (PARTITION BY user_id ORDER BY device)
    + DENSE_RANK() OVER (PARTITION BY user_id ORDER BY device DESC) - 1 AS distinct_devices
FROM logins
ORDER BY user_id, login_date

For u1's laptop row the ascending rank is 1 and the descending rank is 2, so 1 + 2 - 1 = 2; for either phone row it is 2 + 1 - 1 = 2. The 2 windows share a partition key and differ in order, so the trick costs 2 sorts of the table. When that is too much, or when the partition has thousands of rows per key, a GROUP BY user_id with COUNT(DISTINCT device) produces one small row per user that joins back to the login rows.

DENSE_RANK gives NULL a rank like any other value, so a NULL device adds 1 to the count. Filter NULLs out or COALESCE them before the window when they should not count.

Which of these queries runs without an error?

• SELECT d, LAG(SUM(x) OVER (ORDER BY d)) OVER (ORDER BY d) FROM t
• SELECT c, SUM(SUM(x)) OVER () FROM t GROUP BY c
• SELECT c, x FROM t WHERE ROW_NUMBER() OVER (ORDER BY x) = 1
• SELECT c, COUNT(DISTINCT x) OVER (PARTITION BY c) FROM t

About this lesson

This section is part of the Window Functions: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.