I own a Oculus Meta Quest 2 now and I got it in a weird way…

My girlfriend went to a devlead conference recently and came across a booth by Skiller Whale. They offer customized e-learning and skill assessment for teams and hiring. And they ran a contest to win a Oculus Quest.

Theresa asked me if we should give it a go together and so we did. Unfortunately I didn't screenshot the original task but it went like this:

You're given CSV files of a customer and order table. The goal is to rank all customers by the amount they spent each day. Then average their ranks over a given time span and return the X highest ranking customer.

It sounded relatively simple at first. But it turned out to be a bit trickier than first assumed.

First of all it had all to be done in a single Postgres query. So you couldn't rank in one query, then do further processing. This is were I learned about the RANK() function which I hadn't used before and which does exactly what was needed.

The next hurdle was that customers should also be ranked when not having spent anything on a day (receiving the last rank then). This meant a simple join wasn't enough. Instead I needed to join each day as additional column. This probably could have been done using some clever sequencing mechanisms in Postgres, but instead I simply assumed there to be at least one order each day, so I could simply do a distinct sub select for the dates.

We solved the problem in sqlite first, but for porting the whole thing to Postgres I turned to db-fiddle. Their Text to DDL mechanism allows you to paste CSV data and it will automatically create the create table and insert statements for you. They also provide different database backends, including Postgres.

My final SQL is available here.

However, the task wasn't done yet. When we submitted our solution, we had only solved two of three conditions: our SQL returned the right result and it ran fast enough. But we took too long to submit the solution.

Coming up with the final SQL had taken a couple of hours. And the competition asked for submissions to be sent in within one second. However they also said that multiple submissions are allowed and that automating is encouraged.

So the next step was to write a simple script that would

• create a new challenge
• parse the date range and wanted position from the text
• replace those values in the SQL
• submit the solution

And that finally got us the first two places on the leader board with full checkmarks.

It was a fun challenge. Nearly as fun as the Quest itself. Thanks again to Skiller Whale.

Similar posts:

• Mixing Worlds