A Failure’s Guide to the Facebook DE (Analytics) Interview

Hi Blind, I’m fresh off of failing my second attempt at the DE analytics virtual onsite at Facebook and I want to help other people avoid that feeling of emptiness and despair that comes with rejection. I’m well aware this interview isn’t impossible, but I feel it’s hard only for the sake of being hard. Recruiters love to brag about the fail rates. Be sure to roast me for being bad in the comments. There are two sets of questions so folks don’t get the same thing on the retry - I try to present both when the differences matter. First things first. Facebook requires you to be absolutely perfect. There is no room for error on any section. 90% finished on that last coding problem? Fail. Didn’t pull the random metric the interviewer was thinking of at that particular moment? Fail. Why would anyone good enough at coding to ace this interview want this fucking job? Just go be an SDE and stack that paper to the ceiling. Anyways, here’s what you can expect on every section: 1. Data modeling: either Uber or Dropbox. That’s fucking it. They’ll start with product sense and ask what metrics this product would track. If you don’t answer DAU you’ll fail. Sprinkle on some others such as app registrations, upgrades, #rides/#file uploads. Product sense came easy for me but I come from a BI background prior to my DE job. Look up Cracking the PM Interview if you suck at this and you’ll be an expert in no time. Focus on customer experience rather than monetizing. Anyways, if you’ve read chapter 2 of Kimball you’re g2g. Simple data model. Know facts and dimensions. Practice this shit and try writing queries against it . That’s the next section btw. Write queries against your data model. It’s ok if you need to change up your data model. SQL questions are easy, prepare sql to find users who ONLY take rides to or from airports or who ONLY upload photos. Easiest interview for me but everyone is different. 2. Ownership: Say hello to your standard issue, director level bullshit fest. The recruiter gives you the questions ahead of time - take notes and script this out. Why DE? Why Facebook? Tell me about a time you disagreed with coworker/manager? Tell me about a time when someone used data to change your mind? What accomplishment are you most proud of? What would you do in your first 90 days? Etc etc etc. This isn’t a throwaway interview, there are people who nail everything else and fuck this up and walk away with no offer like yours truly. ETL 1: this is where the fun begins. Starts with product sense. They’ll throw a chart at you and ask you why is this anomaly here. If you’ve ever had to analyze data before this should be easy. They might ask if metric A increases by 10% but metric B only increases by 5% what is going on? What would you do to track these anomalies?(spoiler: automated alerts and self service datasets for stakeholders). SQL: know partitions. You’ll have to write a query that functions as the SELECT portion of a daily load job into a aggregate table. You’ll have to use both the demoralized and prior snapshots from aggregate table to solve this. Hint: full outer join. Know case statements and aggregations (duh). Not hard but you need to work fast because it’s a big fucking query and you need to save time for coding. Python: Write a function that parses a nested dictionary and output another dictionary that summarizes metrics by user key OR write a python script that constructs a dynamic sql query based on two input json structures. You’ll have to define all data structures. Use multiple functions if need to complete. My second attempt I got the dynamic sql one and was caught flat footed. This was my weakest part and I honestly can’t tell you how to prep for this. Some will say “dO MoAr LeEtCoDe” but that won’t help you unless you have the practical experience to go with it. ETL 2: more product sense. Work through the user journey for user comments or the news feed. How do we track engagement? How do we determine meaningful interactions? You get the picture. Seriously grab that PM book if this section makes you shit your pants - it’s helpful. SQL: I’ll have a self join problem with an inability to use window functions today *daring aren’t we*. Practice sql questions that force you to use inequalities in the join clause based on session datetime. Another long query with lots of detailed criteria. It’s not hard but it’s a lot. Work fast to save time for coding. Python: ahh the dreaded streaming question. It’s really not that bad. It fucked me over the first attempt because I panicked and overcomplicated everything. The python-sql question above fucked me the second time. Anyway, focus on transforming the input given. You’ll likely need a function and a nested dictionary to process the data into the format you need and then another function to calculate and return the result. This is closer to easy leetcode compared to the other ETL round. General tips: Facebook interviewers suck. They constantly interrupt you. Pause for 2 seconds and they’ll treat you like you’re brain dead and start giving you unhelpful hints that burn up the clock. One interviewer even started filling out part of my code after I had barely started like fuck me dude give me a second to think. One interviewer straight up yelled at me for a minor sql syntax error. Most of them gave condescending answers when I got to ask questions like they are curing cancer on the daily. Current job: L5 DE at Amazon TC: 177K YOE: 6 (4 BI, 2 DE) #facebook #data #analytics #interview