Choosing between LOD fields, window functions, CTEs, and Excel

Some aggregations don’t fit a simple GROUP BY. For example, you may want to show each customer’s lifetime spend on a row-level breakout by product category, or compute each region’s percentage of a grand total. Omni gives you four ways to handle these: an LOD field, a SQL window function, a CTE or derived table, or a spreadsheet. In this guide, you’ll learn about each of these methods, how they compare to each other, and when to choose each one.

Before you start

If you’re new to LOD fields, read Level of detail fields first — this guide assumes basic familiarity with the field type.

Available methods

The right choice usually comes down to whether the calculation needs to be reusable, where you’re comfortable writing logic, and how much data is involved. Use the table below as a starting point; the side-by-side comparison goes deeper on the trade-offs.

Method	Use this when…
Omni LOD field	The same calculation will appear in multiple workbooks, when it needs to respond to filters dynamically, or when you’d rather not write SQL. Cohorts, lifetime customer metrics, and percent-of-total calculations are good fits for this approach.
SQL window function	The calculation only matters inside one query and you’re comfortable in SQL. Use for one-off analyses that don’t need to be reusable.
CTE or derived table	You’re working with a large dataset and benefit from materializing the intermediate result in your warehouse, or when the calculation involves multi-step transformations that don’t fit cleanly into a single query.
Excel	Only when the data already lives in a spreadsheet or for a quick ad-hoc check. Excel isn’t scalable and the logic isn’t reusable outside the file.

Comparing methods

This table compares the four methods across the traits that usually drive the choice. Use it when you want to compare a specific dimension across all four options.

Trait	Omni LOD field	SQL window function	CTE / derived table	Excel
Primary use	Aggregating at a specific level without changing query granularity.	Aggregating relative to the current row in a single query pass.	Creating temporary tables to join back to main data.	Aggregating across a range or within a pivot table.
Where it lives	UI or semantic layer; reusable across workbooks.	Inside a single SQL query.	In SQL; can be promoted to a shared model view.	A specific spreadsheet file.
Flexibility	Dynamic; responds to other filters while maintaining its fixed level.	Fixed to the query’s current result-set granularity.	Static; requires rewriting SQL to change the grouped level.	Manual; requires updating ranges or refreshing pivot sources.
Ease of use	UI-driven; no code required.	Requires knowledge of window function parameters such as `OVER`, `PARTITION BY`, and `ORDER BY`.	Requires SQL and manual join logic.	Familiar for one-off math; hard to scale.
Performance	Optimized by Omni as part of query generation.	Database-dependent.	Best for massive datasets when pre-materialized.	Limited by local CPU and memory.
Reusability	Define once, use everywhere.	Trapped inside a single query.	Reusable if built into a shared model view.	Trapped inside a specific file.

For the best performance on massive datasets, consider using a derived table which can be materialized in your warehouse.

Common scenarios

The three scenarios below cover the patterns that come up most often: bucketing customers by lifetime spend, building acquisition cohorts, and computing percent of total. Each is solved with all four methods side by side, so you can take a pattern you already know in one tool and find its equivalent in another.

Customer lifetime spend

In this scenario, you want “total spend per customer” as a dimension you can use to bucket customers ($0–100, $100–500, and so on), even when viewing orders by product category.

Omni LOD
SQL window function
CTE
Excel

The fields in the workbook’s LOD editor would look like the following:

Field to aggregate - Sale price
Aggregation - Sum
Level of Detail Grouping - Fixed
Fixed dimension(s) - User ID

SUM(sale_price) OVER (PARTITION BY user_id)

WITH user_spend AS (
  SELECT user_id,
         SUM(sale_price) AS total
  FROM orders
  GROUP BY 1
)
SELECT o.*, us.total
FROM orders o
JOIN user_spend us ON o.user_id = us.user_id

In Excel, you’d have a formula where A is User ID and C is Sale Price:

=SUMIFS(C:C, A:A, A2)

First-touch cohort date

In this scenario, you want each user’s first order date so you can analyze performance by acquisition cohort.

Omni LOD
SQL window function
CTE
Excel

The fields in the workbook’s LOD editor would look like the following:

Field to aggregate - Order date
Aggregation - Min
Level of Detail Grouping - Fixed
Fixed dimension(s) - User ID

For a full walkthrough using this approach, see Build cohort analysis using level of detail.

FIRST_VALUE(order_date) OVER (PARTITION BY user_id ORDER BY order_date ASC)

WITH first_purchase AS (
  SELECT user_id,
         MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY 1
)
SELECT o.*, fp.first_order_date
FROM orders o
JOIN first_purchase fp ON o.user_id = fp.user_id

In Excel, you’d have an array formula finding the minimum date (B) per user (A). In Excel versions without dynamic arrays, enter with Ctrl+Shift+Enter.

{=MIN(IF(A:A=A2, B:B))}

Percent of total

In this scenario, you’re viewing sales by region and want to know what percentage of the company’s total sales each region represents.

Omni LOD
SQL window function
CTE
Excel

The fields in the workbook’s LOD editor would look like the following:

Field to aggregate - Sales
Aggregation - Sum
Level of Detail Grouping - Exclude
Fixed dimension(s) - Region

This returns the grand total on every row, so you can divide each row’s sales by it to get percent of total.

SUM(sales) OVER ()

SELECT region, 
       sales, 
       (
        SELECT SUM(sales)
          FROM orders
       ) AS grand_total
  FROM orders

In Excel, build it as a pivot:

Add Sales to Values twice.
Set the second to Show Values As > % of Column Total.

Next steps

Create your first LOD field in a workbook
Build a cohort analysis using an LOD field
Promote logic to a derived table or query view
Read Omni’s blog post on level of detail fields