Master the Meta Data Engineer Interview: Find 2nd highest salary in 2 mins with window functions!

Follow me on LinkedIn or Twitter for more consistent content:

[cckrdrc]

I’ve just found your channel and I wanted to say that it’s amazing. For a long while I’ve been looking for YT channel that talks about data engineering.

[chrism]

Some SQL flavors support the QUALIFY statement, which is similar to WHERE or HAVING, but for window functions. You could use it here and get your results in just 2 steps instead of 3.
I use it all the time for deduping purposes, with something like
QUALIFY row_number() over (partition by <x> order by <y> desc) = 1

[TKNinja]

Cool, didn't know this function existed. I was doing the same thing using row_number and order_by separately in Postgres.

[jonniedarko]

This is great! Not only are you teaching us real skills but you actually give us a real "why". Great context and example. As someone who struggles with this side of things I really found this valuable. And now I am subscribed 😉

[meetpatel-lqxb]

It will be a good counter question to ask if there could be multiple authors with same number of user messages in which case using dense_rank() might be more useful

[monogtraetregmen]

I just use qualify to skip one of the cte. Been using analytical/window functions for a year now and honestly it was like I opened my eyes for the first time

[apibarra]

Great video! You got to love when cloud platforms like Snowflake or Databricks have the qualify function so you can just add your window function at the bottom of your query without having to add the extra column.

[sj]

Thanks for sharing this in your channel Zach, in Oracle PLSQL i like using rows between * unbounded * following / current row, ex: SELECT DISTINCT value
FROM (
SELECT value,
RANK() OVER (ORDER BY value DESC) AS rnk,
MAX(value) OVER (ORDER BY value ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS next_highest
FROM counts) AS subquery
WHERE rnk = 1;

[shubhamsharma-neke]

Can you do a video on how much python should we know for Meta/Big companies data engineer interview? I don’t know DSA and could never learn them. If they only ask SQL in technical round, I can definitely clear that but not sure about Python.

[sidharthadaggubati]

You could have partitioned by the column and ranked it in one CTE right?

[zenyatta]

Hi zach i dont understand anything but commenting because you seem like a chill dude!

[romankovalful]

In Postgres the output of the RANK isn't guaranteed to be sequential. For example, if there were 2 authors tied for most messages, their rank would be 1 and then the rank of the authors following would start at 3. Doesn't this make this solution wrong? In my example doing a WHERE rank = 2 wouldn't return any rows, is this correct?

[qwerty]

Hey Zach,

Amazing content you have. Could you please make video on big data stack with latest technologies?

[PennyHerbst]

I was only aware of the clean() window function

[neelkanthbk]

Thank you Zach for making it easy to understand :)

[alekbeloff]

mate, I have a doubt in your solution. Making it rank, you will assign THE SAME rank to all the elements that have MAX value. So you might have counts like 8, 8, 7, so your rank will show 1, 1, 2. And then you make it "where rank = 2". Which will return you the third element. So correct would be to ask an additional question: do you need to return all the elements with the second highest count? Or the second element in a table sorted by count assuming there is only 1 return element. Cause for the latter row_number() should be the answer

[bacfjib]

Can we do the same, using orderby(count)
Offset 1,
Limit 1
( I think this is not correct in the case that you're looking for the second value because multiple authors can share the first value)

[Tdzzz]

Love these, more
Of these
Type
Of shorts please

[chrishabgood]

Can you put the rank inside the main query?

[djrednitro]

that's super easy, what kind of job at meta is would that be? Entry level or internship?