Analyze a User's Posts - Data Analyst SQL Mock Interview

[@tryexponent]

Don't leave your SQL interviews to chance. Sign up for Exponent's SQL interview course today: bit.ly/3Tfhs4f

[@99ine.]

bro the way the guy walks through to every details of how does he intend to write every sql statements is mind blowing. great job man I'm tryna be like u mah boi xD

[@shikharsav]

Great interview, very helpful that the candidate was walking through their thought process. I liked how they broke down the last question into multiple tables. Just want to highlight a few things that some other comments have touched upon (for other people watching this interview). 1. My understanding was that the objective of the first question was to group by user_type and not user. The interviewer never clarified that for the candidate though. 2. In the third question, there is a bug in the join condition. next_post_id refers to the post sequence number for the user so comparing it to the post_id won't work. It can be fixed by joining with the post_seq table and using join condition as pp.user_id = post_seq.user_id AND pp.next_post_id = post_seq.post_seq_id. However, using an approach with LAG might be much better as some other comments point out. Thanks for the video.

[@jongryulkim6353]

This is the best SQL interview I've ever seen.

[@Eddy-ph5dt]

The answer to the first question seems wrong to me...the question was to group by "user type" but the answer grouped by "user id"....correct me if im wrong

[@mathewjoseph7552]

Really helpful video. Appreciate the way he thought of asking those questions to clarify and the approach he took where way simpler that what came to my mind. Really opened up a lot of new simpler approaches.

[@harsohrabsingh8020]

Amazing video, the approach, clarifying questions asked, the overall structure and discussing it with the interviewer before actually coding it! It would be great if you guys could provide the database table/question link, so we could practice them as well. Great video otherwise.

[@sagar857]

Really enjoying watching your videos, learning a lot , Thanks for doing such a great work for community. God bless you. A request - Could you please make a video on SQL server profiler and how to debug Stored Procedure??

[@narenkarthikeya9]

what do we call such kind of questions asked in a SQL interview? case study questions ? or product metric questions?

[@programmingwithnayan8216]

For the next_post_sc_rate in the last question can't we use lead() window function to get its next post is_success value and then can count the rows by filtering the current_is_success = 0 and next_is_success = 1 as count_next_success_post; so the final calculations will be, count_next_success_post/ count(total_post_id) by user_id ??

[@kaboyodaniel3630]

Kindly share the data set used in this video. I think it would also increase interactivity. Otherwise, great and thought provoking video. Thank you.

[@TonyPinkevich]

What tool do you use during the interview?

[@pwnagebros100]

Lol he answered the first question completely wrong. All she asked was the total amount of succesful posts per user type in the last month. So idk why he was doing the rate and stuff and also didnt do by user_type. All he had to do was count the number of succesful posts and it wouldve been done he did way too much and then didnt group by what was asked.

[@Aditya-wz8my]

For the last question why is the join on “next_post_id(row_number) ” which is index generated by us to the post_id. Shouldn’t we fetch the post_id of the next_post_id (row_number)

[@DduduDdudu_Du]

Question about the Post-Sequencing and the Failed Post case. We used the row number with partition by user_id as the post sequencing id. pos_seq_id +1 which is the next row number. It would be for another user_id. Do we want to analyze the post sequencing failure under the same user?

[@pzeid48]

Is it possible for you guys to upload the data somewhere to try it by oneself?

[@witchaponkitthaworn5998]

HI very educational vidoe i just curious why AVG(post_success*1/post_attempt) instead of just AVG(post_success/post_attempt)

[@tryexponent]

Hey! Multiplying by 1.0 (a floating-point number) converts post_success into a floating point and thus ensures correct handling of division! Integer division: 3 / 4 = 0 Floating point division: 3 * 1.0 / 4 = 0.75

[@osmanbajraktarevic833]

essentially

[@NorthEnd11]

Just curious are these leetcode problems?

[@Rantalytics]

This guy is legit

[@meeta890]

Thanks for sharing the video! This is a super helpful resource as guidance for a real interview. I have a question in the last sql solution. I think the row_number function should only have order by without partition by user_id. If we do partition we won't be able to correctly join to original post table as the post_id and sequence ids won't be in sync. Is my understanding correct?

[@danistrate96]

Would this interview be for an entry level job? This seems pretty advanced :D

[@tryexponent]

Hey danistrate96! While this mock interview may be on the more advanced side, it is possible that some companies will pose interview questions of such difficulty to entry level candidates.

[@aaryamansrivastava8914]

This is for people with some experience or for freshers? I mean the level of questions

[@tryexponent]

Hey aaryamansrivastava8914! These questions are quite advanced, so they’re more likely intended for experienced candidates. However, it’s possible that freshers could be asked these as well.

[@kaiyan9589]

why need to convert integer to float?

[@divyamnigam2070]

holy shot this MF wrote a subquery inside a subquery I did think it would be possible. Well back to grind I still need a lot more info on sql.

[@timianalytics7150]

It'll just made a lot of sense if the dataset is available for practice. Video is still helpful nonetheless

[@darshakkumarranpariya867]

good interview overall, however, seems the first question is not actually properly understood by the candidate. You asked to find out the total number of successful posts posted by user_type for the last month, whereas the candidate addressed a question to find out the total number of successful posts posted by user_id for the last month.

[@serioussam2071]

I am definitely failing this interview 😢

[@TAHJBecomeYourBestSelf]

😂 maybe me too 😢

[@TAHJBecomeYourBestSelf]

Showed me that I need more practice

[@tryexponent]

Hey SeriousSam! Don't be too hard on yourself, it's natural to feel nervous before an interview. One way to boost your confidence and brush up on your SQL skills is by trying out our SQL interview courses at www.tryexponent.com/courses/sql-interviews. You've got this!