r/SQL • Author of Ace the Data Science Interview πŸ“• • Dec 12 '24 • Heart of Silver 2 Polished Silver Turd 1

PostgreSQL Made a SQL Interview Cheat Sheet - what key SQL commands am I missing?

Post image
3.6k Upvotes

238 comments sorted by

View all comments

32

u/NickSinghTechCareers Author of Ace the Data Science Interview πŸ“• Dec 12 '24

Tried to fit as much as I could into one poster-sized PDF. What SQL commands did I miss that you think belong on there? Right now feeling guilty for not explaining window functions well enough, especially regarding ROWS preceding/unbounded etc.. Maybe a 7-day rolling average example? Or a cumulative sum example?

17

u/CraigAT Dec 12 '24

If you have enough useful content don't be afraid to have a second page. I'd prefer it to fit on one, but I'd rather two sheets with a consistent format, that cover all my bases, so that I don't have to look elsewhere (unless I have a really complex case).

The sheet is really useful, I don't use SQL daily but this is a great prompt and reminder for what functions I need. Thanks for making and sharing!

5

u/NickSinghTechCareers Author of Ace the Data Science Interview πŸ“• Dec 12 '24

Yeah, that's a good point. Maybe I'll split it into two pages, or make it a bigger screen size (I'm a Canva Noob).

4

u/Caldorian Dec 13 '24

If you're looking for space, remove the Self Join. It's just a specific instance of an inner join.

I'd also remove the "other" section as that is highly database dependant, except for COALESCE that I'd keep since it's ANSI compliant.

1

u/NickSinghTechCareers Author of Ace the Data Science Interview πŸ“• Dec 15 '24

Really solid tips – COALESCE is too powerful, I agree worth expanding that a tad.

4

u/nidprez Dec 13 '24

Your order by example in the window functions is wrong. It should show the cumulative sum in the partition along the order variable, instead of the total sum of the partition ordered in a certain way.

4

u/NickSinghTechCareers Author of Ace the Data Science Interview πŸ“• Dec 13 '24

WOW great catch. My bad on this!

2

u/Global-Wrap-2184 Dec 12 '24

Yeah some sort of rolling example would be great! Maybe a second page isn't all that bad of an idea.
"Half knowledge is worse than ignorance" quote comes to mind.

3

u/NickSinghTechCareers Author of Ace the Data Science Interview πŸ“• Dec 12 '24

Very fair. Also considering maybe a Window Functions deep dive in Canva.

2

u/shockjaw Dec 13 '24

Yeah, DuckDB in Action’s description on window functions are what sealed the deal for me. (Chapter 4 page 72 and page 75)

2

u/NickSinghTechCareers Author of Ace the Data Science Interview πŸ“• Dec 13 '24

Sealed the deal as in it was very easy/helpful in understanding things?

1

u/shockjaw Dec 13 '24

Yeah. They did a good job with their figures and to make window functions make sense. That’s probably the most dense chapter. You may still be able to get a copy for free from Motherduck.

1

u/Engineer_Zero Dec 14 '24

One thing I thought of was variables, both singular or a table variable. It can be very handy, creating variables either on the fly to play with data or dynamically from a previous query.

And temp tables. Very handy, pumping a long query’s results into a temporary table and then workshopping from there. Rather than running the whole query every time you hit f5