I applied online. The process took 5 weeks.

One con with their whole process was the time they take to schedule all the rounds. They took more than a month and multiple back-and-forths just to schedule 3 rounds.

• Round 0: A HackerRank test with 4-5 SQL questions and 1 Python question.
• Recruiter Screening: A screening round.
• Round 2: A General Coding round and a Data Coding round.

For the General Coding round, we had to execute the Python/Java code and show the output. Both my panels for the General Coding and Data Coding rounds were from the US, so the interviews were scheduled at night IST. The General Coding interviewer was helpful and hinted toward edge cases. For the Data Coding round, they rescheduled it once because the panel didn't show up the first time.

Even after rescheduling, I was looking forward to the Data Coding round, but the interviewer seemed inexperienced (even though he was experienced professionally with work experience at multiple orgs) and was confidently uttering a few incorrect things and didn't clearly specify his expectations. For this round, the main expectation that the interviewer never set for me was that he was evaluating all the SQL answers from the lens of "How would this SQL query behave at scale on Spark?" while I was answering it from the lens of "How would I solve this question analytically in SQL?". With respect to this, for one question, he said that we can use CTE, to which I replied that CTEs are only for readability, but he replied that CTEs in Spark would force the pre-aggregation to be performed before (while this is factually correct, he never mentioned that he was evaluating and expecting from the POV of executing it in Spark, but expected me to answer that!). At one point, I had to make answers which he wanted to hear, regardless of whether my initial answer was correct or not. For one of the simple questions I knew I had correctly answered, he kept me wondering if I had correctly answered it. I'd say my luck was bad that I got this interviewer panel. I got rejected at this point as I had expected.

For the HackerRank assessment, they will give a table schema and base SQL questions on that, and execution will test it on a few test cases. It is similar for the Python question as well. In my case, the question just asked the same SQL question, but the code had to be implemented in Python.

General Coding round: I had to write the code either in Python or Java. I was given a dictionary of listings. Each listing had multiple list-type key/value pairs inside, like amenities, ratings, reviews, guest IDs, and host IDs. Based on this listings variable, there were multiple questions:

• Q1: Give all cohosts with the count of their listings.
• Q2: Give the guest ID with the lowest average rating. How do you handle ties?
• Q3: Sort listings from top to bottom as per average rating. In case of a tie, use the one with the latest review date.

Data Coding round: The interviewer shared two table schemas with example data rows. I had to write SQL for all the questions based on that. One table was an impressions table (id, visitor_id, page_name, referrer_page_name, ts, ds), and the other was an actions table (id, page_impression_id, action, ts, ds). Some helpful date functions are current_date(), DATE_ADD(current_date, INTERVAL 1 DAY).

• Q1: Write a query to find which visitor visited the most number of distinct pages yesterday and how many distinct pages they visited.
• Q2: Write a query to find the total number of actions taken on each page (i.e. page_name) visited yesterday.
• Q3: Write a query to list the pages (i.e. page_name) for page_impressions where visitors used the “BUTTON CLICK” action more than once yesterday.
• Q4: We want to periodically send a survey to all “super users”. “Super users” are defined as visitors who have at least one impression every day over each of the last seven days. Write a query that would return the “super users” as of yesterday.
• Q5: We suspect that our page impression logging is currently broken, such that loggers might send duplicate impressions (two or more records exist with the same exact information). Write a query that can dedupe the events in the impression table so that duplicate events are filtered out.
• Q6: As an effort to simplify the application, we’ve been asked to identify which pages users spend the most time on. Write a query that provides us with an estimation of the average duration for how long a user spends on each page. Be sure to call out any assumptions you are making and how they might affect the query results.