What is the best/most efficient way to find the maximum date of a column?
After finding that value, Iw ill subtract another date.
Hey @Michael726 you can use the aggregate board where you can select “Max” for the function input
1 Like
In the interest of completeness, it’s worth noting that in order to then subtract the value of another date column from the max date, you’ll need to get the max date as a column onto the result, which requires a subsequent cross join.
You can alternatively use a window function, which will remove the need for the subsequent cross join, but the query plan for the window function is much less efficient (as suggested by the warning in the UI).
Window function configuration:
Query plan for aggregation part of the “Aggregate” transform implementation (you can see how the data is partially aggregated in each partition before shuffling the intermediate results into one partition and calculating the final aggregation result - this is very efficient):
Query plan for the aggregation part of the window function implementation (you can see how all of the data is shuffled into one partition, followed by the computation of the aggregation - this is problematic if the data is large):
3 Likes