Right way to reflect for window functions

1

Hi I am using a window function to deduplicate a dataset based on a column. I can’t seem to get it to ever use a reflection. The query I am doing is something like this

WITH deduped AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY p.unique_id ORDER BY p.date DESC) AS rk FROM Transcode."Generation 1"."Unique".table_with_unique p) SELECT d.* FROM deduped d WHERE d.rk = 1

How do you recommend I get this query to use a reflection? I have an aggregate measure reflection on unique_id and a partition reflection on date which are not getting used.

Is there another recommended way of deduping by a unique id than what I am trying?

1 Like
2

i have the same issues facing too, instead of using group by, i use the window function, how is the suggestion on the agg.reflection design?

3

@kprifogle Can you give us your agg reflection refresh job profile?