So I cleared my one hour phone screen SQL and Python interview with all questions done correctly with 20 minutes to spare. I did 6 SQL and 5 python question in 40 minutes.
Then I was given around 2.5 weeks to prepare for onsite round which I utilized fully by practicing all SQL questions and Easy+Medium Python questions on Leetcode. I created my own Facebook user activity data model mimicking the user activity using a python script (see my posts 1, 2 & 3 ).
Well, before I move forward let me highlight that this was not a traditional onsite because it happened during the COVID lockdown. Therefore it was all arranged over bluejeans video call. Coding, SQL and Dimensional Modelling was done on CoderPad, but without execution
If you want to prepare for SQL questions, I found that the SQL Cookbook was very helpful in practicing some difficult scenarios. You can buy this book from following Amazon link
Back to interview details – It started with 10 minute call at 10:20AM PST with the recruiter who described all the modalities of the day.
Interview 1 – Ownership
This was a 30 minutes interview with a Data Engineering Manager at Facebook who asked lots of past project related questions. Something like
- Why Data Engineering
- Tell me a project where you had to cut corners
- a project where you had to persuade someone to look at your viewpoint
- How do you resolve workplace professional conflicts
- a project where you took initiative
Be prepared with lots of example from your past projects to demonstrate ownership skills
Interview 2 – Tech 1
This was a 60 minute interview with a senior Data Engineer. below were the types of questions asked:
- How would you measure the success of a facebook post?
- What metrics would you calculate and what data will you log
- Then a SQL question which was of medium difficulty on the same metric. They gave a table with data in it and asked to write the SQL. I cannot disclose the exact question though.
- Lastly I was asked to write a python function to calculate this metric while the data was being streamed to your function so you have to take care of memory usage as well as speed.
All went well in this interview and the interviewer even said at the end – talk to you soon. So I was happy for this signal.
Break – 60 minutes
Interview 3 – Tech 2
This was also a 60 minutes interview and was mostly on Dimensional Modelling
- Facebook wants to launch a new service, how would you measure the success
- How will you setup the data model (dimensional model) to perform various metric calculations
- Then 6 SQL questions on your data model ranging from Medium to Hard difficulty
I finished all 6 queries and data model in just 45 minutes. I wrote table names and their field names on CoderPad.
Interview 4 – Tech 3
This was again for 60 minutes and had a similar format as the Tech 1 but with greater difficulty level
- Again started with product scenario, where I was asked how would you investigate 10% increase in user numbers but only a 5% increase in engagement.
- What metric would you calculate, how would you visualize
- Then a table was given with data and I was asked to write a SQL. I wrote the basic SQL but the catch was that the table had billions of rows so they expected me to design an efficient solution by using aggregate tables. The interviewer video line was not so good and the interviewer was not able to explain the question correctly as well. So I had trouble answering this question to their expected perfection. You should not let their engineers give you any hint, they tell you that you should listen for hints, but in reality if you take their hints and solve the question they will mark you as No hire with the reason that you are not good at SQL or coding.
- After satisfying the interviewer I had already used 45 minutes, now I was left to do the last Python question in 10 minutes because last 5 mins are kept for you to ask some stupid questions to the interviewer. The python question was about creating select SQL statement for converting data from one table structure to a final table structure. A PYTHON FUNCTION TO WRITE A SQL STATEMENT!!! It was a weird kind of question which I never expected. So I was not able to complete it in time. Although I still believe if I had 10 more minutes I could have solved it.
For a guy who had the phone screen done in record time, dimensional modelling done in 45 minutes, still received a rejection call after two days saying that your SQL and coding are not up to the mark as we expect :). That’s how hard it is to get into Facebook.
I think the last interviewer was not satisfied with my answers and that was the reason for rejection. I felt bad but it is OK, I have another good offer from a company in the same city as I am currently in.
Although the recruiter tells you to listen for hints, I would just advise anybody giving Facebook interview to not let their engineers give any hints, think of the solution yourself. Make it clear to them that you need time to think.
All in all I believe your failures make you more strong, so I take it as a learning lesson in life and push for more perfection in my answers.
I hope you enjoyed this post and I promise the next posts will be more on ML and AI.
Thanks for sharing such a wonderful insight. Please do share your other interview experience its a good learning for other and big motivation as well.
In the initial screening round where you answered 10 ( SQL + Python ) is in coderpad with unit-tests.
For your final Virutal round, do you have write SQL and should it produce the exact output like the initial round or its just they look at the SQL.
I had done on-site round pre-covid ( 18 months back ), back then in on-site round coding was on whiteboard.
Now after the interview, if you had a opportunity to re-take just the last-interview ( L but the catch was that the table had billions of rows so they expected me to design an efficient solution by using aggregate tables , PYTHON SQL functions ) … what would you change in your approach ??
Thanks again for the wonderful blog ..
The onsite round also used coderpad but you don’t need to execute it. You just need to write the correct SQL and python code. For data modelling you can write the table names and column names on coderpad as well.
Glad that you found it useful. Thank you for your comment. All the best!
Hi Raghav, very interesting reads. I sent you an email, would appreciate if you could please respond to it.
Hey, nice interview insight !
I’m preparing my onsite interview as well 🙂
It was for “Data Engineer – Analytics” position ?
Thx
Yes
Hi Will, how did you onsite go? Hope you did great!
Hi Raghav,
Great post.
Thanks for sharing.
For the Data Modeling round, did they expect you to build a complex one with several tables or a straight forward one fact and 4-5 dimensions to answer simple SQL questions?
Thanks.
Thank you, Raghav. This was very helpful while preparing for my recent interview at Facebook.
Hi Raghav,
Thanks for sharing Facebook DE interview experience in great detail. I have my interview in couple weeks.
I would like to learn more on Python side of questions. Can you please elaborate more on Python questions.
1. “python function to calculate this metric while the data was being streamed “- Can you explain in bit more detail on problem.
2. “PYTHON FUNCTION TO WRITE A SQL STATEMENT” – Can you explain in bit more detail .
Also, did they ask any questions on linked lists,trees,heaps etc.
I need some pointers on Python as this is my weak area.
Really appreciate your help.
Thanks
Ali
Did you need to use generator for the ETL streaming. And, how was the ETL batch? Did you need to write DDL,create triggers, audits etc
Hi,
Even I didn’t clear the onsite interview last year but I was very curious on how you answered these questions
1) How would you measure the success of a facebook post?
2) What metrics would you calculate and what data will you log
3) Facebook wants to launch a new service, how would you measure the success
4) How will you setup the data model (dimensional model) to perform various metric
calculations
5) Again started with product scenario, where I was asked how would you investigate 10% increase in user numbers but only a 5% increase in engagement.
6) What metric would you calculate, how would you visualize
Hi Raghav,
Can you please share your approach on how you tackled all these Tech interview.
Tech 1
How would you measure the success of a facebook post?
What metrics would you calculate and what data will you log
Then a SQL question which was of medium difficulty on the same metric. They gave a table with data in it and asked to write the SQL. I cannot disclose the exact question though.
Lastly I was asked to write a python function to calculate this metric while the data was being streamed to your function so you have to take care of memory usage as well as speed.
Tech 2
Facebook wants to launch a new service, how would you measure the success
How will you setup the data model (dimensional model) to perform various metric calculations
Then 6 SQL questions on your data model ranging from Medium to Hard difficulty
Tech 3
Again started with product scenario, where I was asked how would you investigate 10% increase in user numbers but only a 5% increase in engagement.
What metric would you calculate, how would you visualize
Then a table was given with data and I was asked to write a SQL. I wrote the basic SQL but the catch was that the table had billions of rows so they expected me to design an efficient solution by using aggregate tables. The interviewer video line was not so good and the interviewer was not able to explain the question correctly as well. So I had trouble answering this question to their expected perfection. You should not let their engineers give you any hint, they tell you that you should listen for hints, but in reality if you take their hints and solve the question they will mark you as No hire with the reason that you are not good at SQL or coding.
After satisfying the interviewer I had already used 45 minutes, now I was left to do the last Python question in 10 minutes because last 5 mins are kept for you to ask some stupid questions to the interviewer. The python question was about creating select SQL statement for converting data from one table structure to a final table structure. A PYTHON FUNCTION TO WRITE A SQL STATEMENT!!! It was a weird kind of question which I never expected. So I was not able to complete it in time. Although I still believe if I had 10 more minutes I could have solved it.
Hi, very nice article indeed. I sent you an email, would appreciate your response on it.
Thanks
Niharika
Wonderful post Raghav.I just started preparing for the interview.Any documents while you were preparing will you be able to share pls last mytrading0230@gmail.com ? Anything should help.
“Lastly I was asked to write a python function to calculate this metric while the data was being streamed to your function so you have to take care of memory usage as well as speed.” — Raghav, can you please give one example of this kind of question?
Very detailed post though. Will be helpful.
Hi Raghav,
Great Blog. Very Informative.
You mentioned that
“The python question was about creating select SQL statement for converting data from one table structure to a final table structure. A PYTHON FUNCTION TO WRITE A SQL STATEMENT!!! ”
Did they ask you to recreate the logic you used in the SQL query such as case statements, group by logic etc into your python code. Or was it like embed the sql statement using python ? Very curious.
Cheers
Hi,
I’m having my onsite next month and I was wondering if i could get some tips from yourself. can I email you?
thanks