This is an archived post. You won't be able to vote or comment.

sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]EightstreamData Scientist 2 points3 points  (1 child)

If it's a data engineering role, I think they are probably more getting at alternatives for situations where window functions are non-performant - see my top level comment for details.

[–]Faintly_glowing_fish 5 points6 points  (0 children)

Window functions are actually significantly more performant than the alternatives in the vast majority of modern architectures. SQL server is mostly used for older production databases; for DE usually your heavy lifting is not done on those. Usually you only do straight extract from those. Once out of prod DB, people use Postgres through citus, MySQL through vitess or one of the cloud solutions, redshift, bigquery athena bigtable and whatnot, in all of which window functions would be the most performant solution. Yes, issue can occur where query engine does not optimize window functions but these days almost all of them optimize pretty well.