How to Use Measures With One-to-Many Joins

The text discusses the problem of implementing measures with one-to-many joins in dimensional modeling. It presents an example where two measures are used - sum(orders.total_cost) and sum(order_items.quantity). The author explains that a naive SQL query would produce incorrect results due to the one-to-many join from orders to order_items, resulting in duplicate aggregated values. To solve this problem, Looker uses symmetric aggregates, while an alternative approach involves using subqueries for multiple levels of aggregation. This method avoids the use of distinct aggregates and can generate efficient plans in modern data warehouses.