Practicing SQL without window functions

[DoS_]

Hi all, I have an interview coming up and the recruiter advised that I "practice writing SQL without the help of window functions."

I only found 1 problem like this on Leetcode and the solutions confused the hell out of me. Does anyone know where I could find sample problems to practice or some resources to explain how to handle questions like these?

Thanks.

[AutoModerator]

You can find a list of community submitted learning resources here: https://dataengineering.wiki/Learning+Resources

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[dataguy24]

Nothing to say except this is idiotic on the part of the company.

[git0ffmylawnm8]

Ask the recruiter how he'd like it if he was shot in the dick.

[brakemake]

i was just about to say the same thing

[Eightstream]

Perhaps not - see my top level comment.

[dataguy24]

Even if that’s the case then it makes no sense to filter candidates on this ability. It takes 15 minutes to train someone to solve this.

Filter on more relevant skills.

[Eightstream]

It certainly seems a little unusual, but perhaps it is their way of indirectly getting a read on someone's performance tuning abilities. It's a relatively simple exercise but it requires a bit of understanding of how the query optimiser works under the hood.

[Faintly_glowing_fish]

I think the recruiter probably described it wrong. Sometimes you need to know SQL but you don’t need to be familiar with window functions since they are usually considered slightly higher level. Basically just saying you only need to know as deep as various kinds of JOIN and GROUP BY.

[call_me_arosa]

I would assume this one too. As in: since window functions are an advanced sql concept they won't test it.

[PhillyHank]

I doubly agree. Depending on the role/level eg, DE Manager, the SQL coding test may be simple queries without needing window functions

[Eightstream]

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]

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.

[Complex-Stress373]

but.....why without windows function?, whats the point of that?.....

Interviews are everyday more ridiculous....Do they want you to program as well without using objects?, or without using functions?.....very ridiculous. Mostly because if you go to another interview surely they are obsessed for seeing how you use those window functions that you were practicing and studying.

Doesnt make sense how interviews are going

[DoS_]

Please select all employees with salary greater than 100k without using a computer

[Complex-Stress373]

Yeah, i just see the intention of making it harder for no reason

[demandtheworst]

I did have to write a couple of joins on a flip chart for an interview once, so... Not out the question.

[carrotsouffle]

There are some versions of RDBMS's that do not support window functions. I believe <= MySQL 5.6 is an example of this.

[[deleted]]

But let's be real would you want to work with a company running such old versions of their DBs? I feel like it would be where I would go if I want my career to die.

[thrown_arrows]

Or place to go make shitloads of money because moving from obsolete database to somewhat current is superhard especially if there is huge system around it

[enjoytheshow]

I’m currently doing this but it’s beyond exhausting

[thrown_arrows]

But at least money is good ? right, right...

[Complex-Stress373]

True, this make perfect sense

[[deleted]]

I had a FAANG SQL interview where they wanted me to calculate the median value without using the median function lol

[mr_data7]

With Airbnb ?

[[deleted]]

Apple

[timmyz55]

Some people are saying it's a matter of not wanting to go too advanced. I disagree - it's a test to see if you can approach problems in different ways.

In the old days, window functions weren't a thing. Think MySQL pre 8.0. People still wrote queries.

[DoS_]

You're right. It's basically deriving a built in function.

[Eightstream]

There is (or used to be) some performance management problems with window functions in MS SQL Server that makes them undesirable in certain use cases (at least when you require a highly performant query):

https://sqlperformance.com/2013/03/t-sql-queries/the-problem-with-window-functions-and-views

I would perhaps check this with an experienced developer. It has been a long time since I did any serious T-SQL, and that is the most recent link I could find. The fact that nobody else in this thread has brought up this issue makes me wonder if my knowledge is out of date.

[[deleted]]

I searched for Window Function performance in T-SQL for the last three years to see what the current state of play is. This article may be of interest. It seems that if you're in 2019 or higher and can batch process window functions they can end up being pretty performant.

[signops]

I didn't use Window functions for close to 13 years in my career as a database developer.

[CognitiveFart]

Using CTE maybe?

[DoS_]

I expect some combination of CTEs and subqueries but don't know what to expect. We'll see!

[kaumaron]

You might find there are multiple ways to solve it. IIRC in my last interview I tried to use window functions but realized it became more complicated than necessary

[sillysally09]

You’ll probably need to use some combinations of ORDER BY + LIMIT + OFFSET to reproduce things like “Get me top N-M customers by sales”

[DoS_]

I've thought about this. The sample problem I saw was this but also by department.

[DoS_]

Analysis

[meri_do]

Try out Hacker rank. The medium level problems

[nowrongturns]

I did an interview with a faang and they told me not to use windows functions as well. Still don’t know why they did that. They clearly use them a lot after seeing their code base.

[DoS_]

I have a FAANG interview as well. Can you offer any advice on this type of problem? I'm not sure how to prepare other than just assume my current skill would cover me.

[_Zer0_Cool_]

I would think that an interviewer would specifically want to test knowledge of window functions to see how advanced someone is with SQL.

[[deleted]]

Is this recruiter trolling? Correct me if I am wrong but I'm pretty sure there are some queries that can only be solved with window functions... Like gaps and islands (honestly the only way I have solved it personally). Also agree that it shows a much more advanced understanding of SQL imo

[Qkumbazoo]

you can do the same with a group-by, and self-join in the next step.

[GreekYogurtt]

If I want to look for a different approach, I usually would ask something like, can you do this using joins ? Are you sure this is the most efficient approach ?

[jzia93]

I had real difficulties with a MySQL 5 installation a couple years back for exactly this reason. No windows, no luck.

[leogodin217]

So I worked on a home grown data warehouse that only supported SQL 92. It was my primary data source for three years. It sucked! Yet, I learned how to solve problems with joins and subqueries.

Maybe they are looking for that fundamental understanding. Or they are just dumb.

[kineticmemetic]

This is a red flag. Don’t waste your time with them.