You receive streaming inserts with possible duplicate rows (unique ID + timestamp present). For interactive queries that exclude duplicates, which query pattern should you use?

The ROWNUMBER() window function is the most effective way to handle duplicate rows when you want to keep only one distinct record based on a unique ID and potentially a timestamp for the most recent entry. By partitioning by the unique ID and ordering by the timestamp (or another relevant column) within each partition, ROWNUMBER() assigns a sequential number to each row. Filtering for ROWNUMBER() = 1 then selects only the first (e.g., most recent) occurrence for each unique ID, effectively removing duplicates for interactive queries. The other options are incorrect because: ORDER BY DESC and LIMIT 1 would only return a single row from the entire dataset, not one per unique ID. GROUP BY on unique ID and timestamp would still return all unique combinations of ID and timestamp, not just one per ID. SUM is irrelevant here. LAG is used to access a preceding row within a partition and is not designed for deduplication in this manner.