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

In this SQL mock interview, we sit down with a Data Analyst who analyzes user post data for a growth hacky startup. The startup wants to know about how users are talking about them and how well their message is spreading.

Chapters (Powered by ChapterMe) -
00:00 - Intro
01:01 - PostgreSQL: The User Data table
03:09 - Post Success Rate
08:44 - How to Segment the 'Super Users'
10:10 - Post-Success Rate in the Aggregate Table
12:05 - Coding
15:26 - Case and Condition on Aggregated Metrics
24:50 - Facebook Ad Metrics Seamatching
31:39 - Discrepancy Analysis
39:09 - Post-Sequencing and the Failed Post
45:38 - Interview Analysis
49:26 - Avoid Hard Coding Dates

Want more SQL content?

ABOUT US:
Did you enjoy this interview question and answer? Want to land your dream career? Exponent is an online community, course, and coaching platform to help you ace your upcoming interview. Exponent has helped people land their dream careers at companies like Google, Microsoft, Amazon, and high-growth startups. Exponent is currently licensed by Stanford, Yale, UW, and others.

Our courses include interview lessons, questions, and complete answers with video walkthroughs. Access hours of real interview videos, where we analyze what went right or wrong, and our 1000+ community of expert coaches and industry professionals, to help you get your dream job and more!

#sql #sqlinterview #datascience #businessanalyst

[jongryulkim]

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

[ine.]

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

[mathewjoseph]

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.

[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.

[Eddy-phdt]

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

[sagar]

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??

[harsohrabsingh]

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.

[meeta]

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?

[narenkarthikeya]

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

[programmingwithnayan]

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
??

[TonyPinkevich]

What tool do you use during the interview?

[kaboyodaniel]

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

[pzeid]

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

[Aditya-wzmy]

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)

[divyamnigam]

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.

[pwnagebros]

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.

[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?

[NorthEnd]

Just curious are these leetcode problems?

[timianalytics]

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

[witchaponkitthaworn]

HI very educational vidoe

i just curious why instead of just