Phone Interview:

1. Introduction (what did you do?)
2. LP questions x 2 (invent and simplify, something else I can't remember)
3. SQL questions x 5:
   • For all questions I got a schema
   • First question was a join
   • Second question involved sum(), group by
   • Third question involved using a window function to rank by magnitude row_number() over (partition by X order by Y)
   • Fourth and fifth question also involved using window functions to calculate cumulative sums/ranks
4. Find all pairs that sum to Y in array

Knocked phone interview out of park, so went through a bunch of meetings with HR to organize the virtual onsite

Asked if they could make it over two days, they accommodated me.

First day:

1. LP round. Tons and tons of leadership principles, but was very friendly. There was one active interviewer and at least one person lurking.

2. Code/Datawarehousing round:

   SQL Round: This fucked me. Unlike the phone interview I was asked to write SQLs without being given a schema, ironically the SQL questions were easier, but the interviewer was unfriendly and mocked my responses. Here they are:

   • Something like a simple aggregation (not window)
   • Antijoin
   • Aggregation with sort (not window)
   • Datefunction using current date (I was kind of upset by how this interviewer was treating me, so I forgot the current date function..)

   Datawarehousing Round:
   Design tables for customers and products. No schema was given. I had to guess what was in the table, while the interviewer mocked me. I used a standard star schema with the sales being the fact table and customer/other stuff in the dimensions and powered through it. Terrible experience.

Second day:

1. Datawarehousing/architecture + Coding round

2. Meeting with hiring manager (just LP)

   Datawarehousung/Architecture + Coding round:

   1. Design a billing system for AWS.

      • Much like the previous shitty interview, no schema was given. I had to guess what was in all the tables. I wasn't even sure what the constraints were. I first designed a streaming/CDC solution, but then the interviewer said "just for analytics" and hinted that I was just supposed to create a bunch of tables like the previous interview, so I once again made a star schema and gave reasons and hints how to normalize it further for snowflake if it was required.
      • I was asked the tradeoffs between normalizing and not normalizing, i.e. when to normalize and not normalize
      • Difference between transactional and anayltical databases (OLTP vs. OLAP, columnar vs. RDBS) what they are better at, etc.
      • How do you deal with dimensions that constantly change over time? What four types are there?
      • What is a bridge table?
      • How do you go about optimizing queries?
      • What is data skew?

   2. Coding round:

      • With 5 minutes left, I was given the roman numeral to integer problem: https://leetcode.com/problems/roman-to-integer/
      • Couldn't do it, because of mind lock, which is sad after having solved hundreds of these fucking problems, but with 5 minutes left and having answered a ton of questions, my mind wouldn't let me do anything else without a break.
      • Went to paindrome detection with 1 minute left, said thanks and just gave up.... goes to show how important mental endurance is, especially after shitty interviews.

   Meeting with hiring manager isn't even worth mentioning. Asked me some LP questions and was very friendly, gave me some info about Amazon and that was it.

No offer. No feedback.

Good luck!