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?