I haven't seen an interview post about this company, so let me be the first.
There were 5 SQL questions, and the interviewer was a DS. The questions themselves weren't that hard, but a lot of the open discussion was pretty tricky. Two tables: users with user_id, signup_date, and login with user_id, browser, date.
A few of the questions stuck with me:
Calculate login DAU, and if there are days with no DAU, those days should show 0. I said you'd need a date dimension table, and then they asked me how to create that table. I looked it up afterward on Postgres:
SELECT generate_series('2023-01-01'::date, '2023-12-31'::date, '1 day'::interval)::date AS CalendarDate;
Not sure if there's a better solution.
Calculate MAU by date. I asked if this meant a rolling window, and they said yes, so I said let's define the L30D window. They asked why 30 days, and whether there could be other day counts. Extra context: if the product is a weekday product with no usage on weekends, would the L30D lookback have a problem?
Rehashed user_id — I found this question really interesting. The setup was that at some point the user_id gets completely rehashed, and how does that affect the MAU calculation. They also asked for the max overestimate and min overestimate as a percentage. I initially misunderstood and thought the rehash would happen on every login, so the max would be current_login x 2. The interviewer never directly answered my clarifying question. Only at the end did they hint that there's only one rehash — say T-1 has no rehash, and T does. Then what changes between T and T-1? I answered MAX 100%, MIN 0% at the time.
Throughout, I felt like there was something off about communicating with the interviewer. I could understand what they were saying, but they never seemed to directly answer my questions, and most of the time they didn't confirm or push back on my approach. Not sure if that's just how the company works. There were 5 questions, almost every one had a follow-up, and the full hour was used up.
Discussion
Loading comments…