I just finished the interview and I want to write this up while it's still fresh. Before the interview they told me it would be SQL and Python, but in the end it was SQL only. It opened with a self-introduction and a few simple questions about the project I've been working on recently.
The setup was the pirated-theme question that's already known on this forum, though the specific version I got might be a bit different.
Q1: the classic question of whether pirated usage is going up. I started by asking how "usage" should be defined, and got asked back to define it myself, so I gave two options: 1) installed-pirated-theme shop_id divided by total installed shop_id, and 2) valid time of pirated installs. I only coded the first one, which was the simpler of the two — join against the pirated theme table, count both shop_id sets by month, then compute the percentage.
Q2: calculate revenue loss over time. There were two tricks here. First, valid_to can be null, so you need a coalesce with a default value. Second, "over time" actually means a monthly running total — cumulative through January, cumulative through February, and so on. This part was a lot more involved. I cross joined a calendar table and used a window SUM function to get the rolling sum.
Q3: present the results to a PM. I basically just talked through this one for about three minutes, mostly because the results from the first two questions were too thin to say much about. The pirated percentage in the first question went from 0% to 100%, which is a red flag worth flagging. For the second question, if the rolling sum looks exponential on a chart, that's also a red flag, but personally I think a simple monthly sum shows the trend more clearly.
The interviewer was a bit passive. Whatever I asked, he'd throw it back at me — "you decide," "it's up to you whether to proceed" — there wasn't much positive engagement either way.
I missed the first trick in Q2 at first. I wrote out the full result and still had 30 minutes left, so I said on my own that I usually like to break the result down and double check it. He just said, go ahead and check then — so I caught that bug myself. After I fixed it, only then did he tell me that "over time" meant a rolling sum, and I had to scramble to rewrite the join logic again. By the end I only had 7 minutes left for Q3 and for Q&A.
Overall I wouldn't call this a straightforward SQL round — five tables, and you have to talk through your reasoning as you go, so it's basically impossible to just feed it to an LLM. I did look up one function's syntax mid-interview.
Not sure if I can pass with such a passive interviewer.
Discussion
Loading comments…