I interview people quite often for data engineer positions where good knowledge of SQL is also required and I'll usually have the applicant give me a presentation of some system or data pipeline that they have worked on earlier (any possible business or client secrets can of course be obfuscated or omitted). I'll ask questions about that system or process "like I'm 12 years old" and then I'll make up on the fly some kind of problem statement that I would like to have this and this kind of results from the data - how would you do it? Very often I include problems that would be easily solvable with window functions and more often than not those who know them get hired :)

IMHO it is really helpful to know these.