partitions of max() and dense_rank() must match., dense_rank() over(order by customer_id) cust_rnk , dense_rank() over(partition by order_month order by customer_id) mth_rnk , dense_rank() over(partition by traffic_channel order by customer_id) tc_rnk , dense_rank() over(partition by order_month, traffic_channel order by customer_id) tc_mth_rnk , max(cust_rnk) over() ytd_total_customers , max(mth_rnk) over(partition by order_month) monthly_customers_all_channels , max(tc_rnk) over(partition by traffic_channel) ytd_customers_by_channel , max(tc_mth_rnk) over(partition by order_month, traffic_channel) customers_by_channel_and_month * multigrain windowed distinct count, additional grains are one dense_rank and one max over() */ Its important to match the partitions in the outer query to the corresponding partition in the subquery. Since you need the highest rank, you have to subquery everything and select the max value from each ranking taken. This is a horrible mess if you try to swap in the following for each partition I want: dense_rank() over(partition by order_month, traffic_channel order by customer_id)

The key insight is that dense_rank(), ordered by the item in question, provides the same rank to identical items, and therefore the highest rank is also the count of unique items. There is strangely very little I could find on all of the web therefore I'm sharing my (tested) solution. Of course, if you have, naturally occurring in your customer_id strings, you'll have to find a safe delimiter.Ī blog post from 2016 calls out this problem and provides a rudimentary workaround, so thank you Mark D. Listaggdistinct(customer_id, ',') over (partition by field2), While Redshift doesn't support DISTINCT aggregates in its window functions, it does have a listaggdistinct function.

Redshift rank function