Golden Analytics — Query Challenge

The Challenge

This is a challenge for potential query engineers to solve.

The context of the challenge is a business intelligence application. This application will need to issue analytical queries (e.g. selection/projection, aggregation, filtering, joins, calculations) against a set of tables on a remote database server and return the results. The long-term goal would be to support many different relational databases, with this API serving as the translation layer between the application and those databases.

The goal of the project is to make an API that can express these kinds of queries and execute them across a set of relational database backends, without having to worry about the details of how they are implemented on each backend. You'll demonstrate this by writing a driver application that uses your API to execute queries against both backends, and a testing framework that demonstrates how you'd test your API against these databases, architected to expand to others in the future.

From a language and technology perspective, we would prefer if you demonstrated all of this in Node/TypeScript (this is our current platform), but you can select another platform if you like. You should use the Docker image goldenbeers/golden-query-challenge:1.0 on the project. It contains two databases (MySQL 8 and Postgres 17) pre-loaded with sample data.

We expect you to use AI in completing this challenge. Please keep track of your major prompts and how you guided the AI during the project. We also don't expect you to get everything to polished completion. Expect to spend about 45 minutes on the challenge, and keep track of what you would do if you had extra time to spend on it.

AI will likely get you most of the way there. After you have your basic API in place, pick one way to extend it (an advanced SQL feature, an interesting third database, etc).

Things you should deliver

1. One video (~6 minutes). Start with a short intro: tell us who you are, one thing about yourself that isn't on your resume, and what specifically excites you about Golden Analytics. Then walk us through your API and why you designed it this way. One take is fine. We're listening to what you say, not how polished you sound.
2. A driver application demonstrating some of the queries below and your one extension
3. A test suite for your API using the provided databases
4. A README, written by you (not the AI): summarizing the design of your API; describing your major prompts and where you redirected the AI; what you would do if you had more time

Database schema

The sample data is the Northwind database (schema diagram). The link shows a schema diagram with the default relationships between tables.

Sample queries

1. The most recent N orders
2. The count of customers from a given city, e.g. 'london', or 'mexico d.f.'
3. Average number of units per order
4. For each employee, the number of shipped vs. unshipped orders
5. The total freight / total order price ratio per customer
6. The 75th percentile of order price by customer's country