You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
246 lines
12 KiB
246 lines
12 KiB
---
|
|
gitea: none
|
|
include_toc: true
|
|
---
|
|
|
|
## Appointment Booking Challenge
|
|
### Overview
|
|
We want to build an appointment booking system that allows customers to schedule appointments with our sales managers to discuss
|
|
one or more of our products. For the MVP, we will have a website that displays available appointment slots that a customer can choose
|
|
from.
|
|
|
|
The goal of this project is to implement the backend for this system. We need an endpoint that returns the available appointment slots
|
|
for a customer.
|
|
|
|
There are a few rules we need to consider when checking for available appointment slots for a customer:
|
|
|
|
* Each slot corresponds to a one-hour appointment
|
|
* Slots can have overlapping time ranges. For example, it is possible to have the following three slots:
|
|
* 10:30 - 11:30
|
|
* 11:00 - 12:00
|
|
* 11:30 - 12:30
|
|
* A sales manager CANNOT be booked for two overlapping slots at the same time. For example, if a sales manger has a slot booked
|
|
at 10:30 - 11:30, then the 11:00 - 12:00 cannot be booked anymore.
|
|
* Customers are matched to sales managers based on specific criteria. A slot CANNOT be booked by a customer if the sales manager
|
|
does not match any of these three criteria:
|
|
* Language. Currently we have 2 possible languages: German, English
|
|
* Product(s) to discuss. Currently we have 2 possible products: SolarPanels, Heatpumps
|
|
* Internal customer rating. Currently we have 3 possible ratings: Gold, Silver, Bronze.
|
|
* Customers can book one appointment to discuss multiple products
|
|
|
|
### Requirements
|
|
Design and implement a REST endpoint in any language of your choice that:
|
|
|
|
* Listens for POST requests on this route: http://localhost:3000/calendar/query
|
|
* Connects to the provided Postgres database instance
|
|
* Receives a request body in this format:
|
|
```
|
|
{
|
|
"date": "2024-05-03",
|
|
"products": ["SolarPanels", "Heatpumps"],
|
|
"language": "German",
|
|
"rating": "Gold"
|
|
}
|
|
```
|
|
* Returns a response with an array of available slots that can be booked by the customer in this format
|
|
```
|
|
[
|
|
{
|
|
"available_count": 1,
|
|
"start_date": "2024-05-03T10:30:00.00Z"
|
|
},
|
|
{
|
|
"available_count": 2,
|
|
"start_date": "2024-05-03T12:00:00.00Z"
|
|
}
|
|
]
|
|
```
|
|
|
|
#### NOTES:
|
|
|
|
* You can use any language, framework, library of your choice for this challenge.
|
|
* The system should not book appointments in this challenge; your focus is returning available slots.
|
|
* We provide you with a docker database already populated with data. You are not allowed to modify the database structure of the
|
|
database in any way but you can create indexes or views if you think it is necessary. The docker database can be downloaded
|
|
here: https://[redacted]/Take_Home_Challenge_Resources.zip
|
|
|
|
#### Database Schema
|
|
|
|
The provided database has the following schema:
|
|
|
|
##### Table: sales_managers
|
|
|
|
| Column Name | Column Type | Comment |
|
|
| ----------- | ----------- | ------- |
|
|
| id (PK) | serial | ID of the sales manager |
|
|
| name | varchar(250) | Full name of sales manager |
|
|
| languages | array(varchar(100)) | List of languages spoken by sales manager |
|
|
| products | array(varchar(100)) | List of products the sales manager can work with |
|
|
| customer_ratings | array(varchar(100)) | List of customer ratings the sales manager can work with |
|
|
|
|
##### Table: slots
|
|
|
|
| Column Name | Column Type | Comment |
|
|
| ----------- | ----------- | ------- |
|
|
| id (PK) | serial | ID of the slot |
|
|
| start_date | timestampz | Start date and time of the slot |
|
|
| end_date | timestampz | End date and time of the slot |
|
|
| booked | bool | Value indicating whether the slot has already been booked |
|
|
| sales_manager_id (FK) | integer | ID of the sales manager the slot belongs to |
|
|
|
|
### Getting Started
|
|
Download the resources for this challenge here https://[redacted]/Take_Home_Challenge_Resources.zip. This is a zip file that contains two folders:
|
|
|
|
* database: This folder contains a Dockerfile that can be used to start a Postgres database server and an init.sql file that
|
|
initializes the database and preloads it with data.
|
|
* test-app: This folder contains a node application that can be used to run test scenarios to verify your application.
|
|
|
|
#### Setup the database
|
|
|
|
Extract the `Take_Home_Challenge_Resources.zip` file and run the following commands in the database folder. This requires
|
|
that you have docker installed on your local environment
|
|
|
|
```
|
|
docker build -t enpal-coding-challenge-db .
|
|
docker run --name enpal-coding-challenge-db -p 5432:5432 -d enpal-coding-challenge-db
|
|
```
|
|
|
|
Once the docker container is up and running, ensure you can connect to it using your favourite DB query tool (e.g.: DBeaver or pgAdmin).
|
|
|
|
The default connection string is `postgres://postgres:mypassword123!@localhost:5432/coding-challenge`
|
|
|
|
If you want to use a local database installation instead, you also can get the `init.sql` file and run it in your local database.
|
|
|
|
#### Setup tests
|
|
Extract the `Take_Home_Challenge_Resources.zip` file and run the following commands in the `test-app` folder. This requires
|
|
that you have node installed on your local environment
|
|
|
|
```
|
|
npm install
|
|
npm run test
|
|
```
|
|
|
|
The tests try to connect to an endpoint running on http://localhost:3000/calendar/query and run several test scenarios. Since that is not
|
|
probably running yet the tests will fail.
|
|
|
|
You can inspect the `test.js` file and see some example requests and the expected responses.
|
|
|
|
#### Start coding
|
|
|
|
You can now create an api in your language of choice that fulfils the requirements.
|
|
|
|
### How to submit the solution
|
|
|
|
The solution should contain:
|
|
|
|
* your application code
|
|
* docker setup that starts the database
|
|
* instructions on how to run your application.
|
|
|
|
You can send this to us as a zip file or push this to a github repository and send us the link.
|
|
|
|
Your solution must connect to the database in the docker container and then we’ll run the same tests provided to you. We might ALSO
|
|
run additional tests, such as loading thousands of records in the database to assert the application is performant enough.
|
|
|
|
### Evaluation Criteria
|
|
|
|
Please note that we will be evaluating your solution not just based on correctness but also on the following criteria. We place a high
|
|
importance on these criteria, and we strongly encourage you to carefully consider them as you develop your solution:
|
|
|
|
* Accuracy in adhering to the specified rules.
|
|
* Efficiency and performance of the api endpoint.
|
|
* Clarity, readability and testability of the code.
|
|
* Handling of edge cases and error conditions.
|
|
|
|
## Solution
|
|
|
|
### How to launch
|
|
|
|
You'll need Node.js and npm to launch it, but then the tests that came with the challenge
|
|
already required both Node.js and npm.
|
|
|
|
Solution is in [`service/`](service/); commands are:
|
|
|
|
* `npm ci`, to install dependencies;
|
|
* `npm run start`, to typecheck and start the backend
|
|
(optional `PORT` and `PG_CONNECTION_STRING` environment variables are supported,
|
|
e.g. `PORT=4000 PG_CONNECTION_STRING=/run/postgresql npm run start`;
|
|
they default to port and connection string specified in the challenge);
|
|
* `npm run lint`, to lint the backend;
|
|
* `npm run test`, to run unit and integration tests
|
|
(actually there are no useful tests in this backend right now,
|
|
because the only interesting thing in it is one huge SQL query);
|
|
* `npm run test:e2e`, to run end-to-end tests,
|
|
(it too supports optional `PG_CONNECTION_STRING` environment variable)
|
|
(there are no useful e2e tests right now as well).
|
|
|
|
### Used framework
|
|
|
|
I had to spend most of the time on creating an API project and writing boilerplate
|
|
rather than actually solving the interesting part.
|
|
Since the challenge says that I can use any language or framework for this challenge,
|
|
and since it already has tests written in Node.js,
|
|
I decided to go with Nest.js (+ TypeScript) because it provides somewhat reasonable defaults,
|
|
and allows one to get to actually implementing the interesting part reasonably fast (although still too slow).
|
|
|
|
All request validation is done using standard Nest.js approach.
|
|
If the database is not available at the startup, it will fail.
|
|
|
|
### The main task
|
|
|
|
Producing the list of matching slots seems like a task for DB (especially with the given DB structure),
|
|
so everything is happening in one huge query in [`service/src/db/index.ts`](service/src/db/index.ts).
|
|
|
|
### DB optimization
|
|
|
|
The challenge also says that I can add indexes to the existing DB structure,
|
|
and that you are going to evaluate the solution based on efficiency and performance.
|
|
However, in order to make decisions regarding efficiency and performance,
|
|
one has to know the actual use cases, and the actual distribution of the data.
|
|
20 rows and vague descriptions in the challenge are simply not enough to guess
|
|
how exactly should the solution scale, and in which directions.
|
|
|
|
That said, I made several assumptions about the future state of the data, as follows:
|
|
|
|
* There will not be a lot of managers, so doing full scan on managers table for every query
|
|
(trying to find suitable managers) is not a performance issue.
|
|
Even if in the future there will be millions of entries in managers table,
|
|
presumably not all of them will actually be active (and presumably we're only interested
|
|
in the slots for the future dates), so perhaps adding some kind of `is_active` field
|
|
and indexing by it will provide us with better specificity
|
|
than indexing by languages / customer ratings / product types.
|
|
So I did not add any indexes to the managers table.
|
|
* Another related assumption is that there will only be a few active managers
|
|
matching language and product type and customer rating at once.
|
|
* For slots, I assumed that there will be a lot of slots per manager;
|
|
and that old slots are not going to be removed from DB (even the booked ones).
|
|
So I added an index on manager id + booked flag + start date,
|
|
to allow for fast retrieval of all free slots for a handful of matching managers for the specified date.
|
|
* Since the challenge says that each slot corresponds to one-hour appointment,
|
|
I assumed that slots can overlap but never be contained in each other.
|
|
This allowed me to simplify the check whether `a` overlaps with `b` to just
|
|
`(a.start_date <= b.start_date < a.end_date) or (a.start_date < b.end_date <= b.end_date)`,
|
|
so in order to check if there are any slots overlapping with `a`, it is enough to just find
|
|
all slots with either `start_date` or `end_date` in a certain range.
|
|
I added an additional index on manager id + booked flag + end date, meaning that
|
|
to answer the question "does this free slot overlap with any booked slots of the same manager,
|
|
it is enough to only query two indexes (and ensure that the results are empty),
|
|
without having to do a full scan on anything.
|
|
|
|
Ultimately, I added two indexes (both defined in [`database/init.sql`](database/init.sql))
|
|
which should allow this solution to scale to large number of slots (but still limited number of managers).
|
|
|
|
### Other assumptions
|
|
|
|
And finally, assumptions about dates:
|
|
|
|
* The challenge does not say anything about timezones, so I decided not to bother with them either.
|
|
This would be unacceptable in a production-ready project, but for production-ready project,
|
|
one would have at least accept the timezone in request, and not just "YYYY-MM-DD" string
|
|
which doesn't mean a lot by itself.
|
|
So this solution might contain some timezone-related bugs... but technically they are not bugs,
|
|
because they still will not violate any requirements in the challenge.
|
|
* The challenge does not say anything about the meaning of the requested date.
|
|
So I decided to treat it as specifying the range for the slot start date
|
|
(e.g. time slot from 2025-01-12 23:30 to 2025-01-13 00:30
|
|
will be returned for requests for 2025-01-12, but not for 2025-01-13).
|
|
|