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

Don't leave your SQL interviews to chance. Sign up for Exponent's SQL interview course today: bit.ly/3Tfhs4f
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?
- Find employees who earn more than their managers: • SQL Mock Interview: Fi...
- Analyze Marketing Channels: • Data Science SQL Mock ...
- SQL vs. NoSQL Explained (in 4 Minutes): • SQL vs. NoSQL Explaine...
- Complex Datasets with SQL: • Complex Datasets with ...
👉 Subscribe to our channel: bit.ly/exponentyt
🕊️ Follow us on Twitter: bit.ly/exptweet
💙 Like us on Facebook for special discounts: bit.ly/exponentfb
📷 Check us out on Instagram: bit.ly/exponentig
📹 Watch us on TikTok: bit.ly/exponenttikttok
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

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

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

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

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

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

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

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

[@narenkarthikeya9]

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

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

[@pzeid48]

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

[@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)

[@user-ce7hh3bc3j]

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?

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

[@timianalytics7150]

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

[@osmanbajraktarevic833]

essentially

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

[@NorthEnd11]

Just curious are these leetcode problems?

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

[@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!

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