Bithealth Associate AI Engineer Pre Test: Data Processing + SQL Querying + Mini Project

This project contains the solution for the Technical Test in the context of a hospital AI system. It consists of three parts:

1. Python - Basic Data Processing
   This part includes a Jupyter notebook for processing patient data. The notebook filters patient records based on age and symptom count, then outputs the results.
2. Querying – SQL for Patient Visit Insights
   This part writes an SQL query to retrieve patient visit insights based on given criteria. The query extracts the most recent visits to the Neurology department, where the patient is over 50 years old and has at least 3 recorded symptoms.
3. End-to-End Mini Project
   This part implements a FastAPI service that uses an LLM to recommend the most relevant hospital department based on a patient’s symptoms. It uses LangChain to communicate with the LLM and generate recommendations.

Feel free to inspect each part, run it locally, and modify as needed. Click here to learn more about the project: bithealth-dp-q-mp/assets/Bithealth AI Pre Test.pdf.

📁 Project Structure

bithealth-dp-q-mp
│
├─ 1. Python - Basic Data Processing/
│  ├─ patients.csv              # Original patients data
│  ├─ filtered_patients.csv     # Filtered patients data
│  └─ filter_patients.ipynb     # Jupyter notebook for filtering patients
│
├─ 2. Querying – SQL for Patient Visit Insights/
│  ├─ query.txt                 # SQL query for patient visit insights
│  └─ query_patients.ipynb      # Jupyter notebook for querying patient visits
│
├─ 3. End-to-End Mini Project/
│  ├─ .env                      # Environment variables
│  └─ app/
│     ├─ llm_chain.py           # LLM chain logic
│     ├─ main.py                # Main application file
│     └─ models.py              # Request/Response definitions
│
└─ requirements.txt             # Python deps

⚙️ Local Setup

0. Make sure to have the prerequisites:

   • Git
   • Git Large File Storage
   • Python
   • Conda or venv

1. Clone the repository:

    git clone https://github.com/verneylmavt/bithealth-dp-q-mp.git
    cd bithealth-dp-q-mp

2. Create environment and install dependencies:

   conda create --name bithealth-dp-q-mp python=3.11
   conda activate bithealth-dp-q-mp
   
   pip install -r requirements.txt

3. Run each part:

   1. Python - Basic Data Processing
      1. Navigate to first part directory:

          cd "1. Python - Basic Data Processing"

      2. Open the filter_patients.ipynb in Jupyter Notebook:

         jupyter notebook filter_patients.ipynb

      3. Execute all cells
      4. Check the filtered results in filtered_patients.csv
   2. Querying – SQL for Patient Visit Insights
      1. Navigate to second part directory:

          cd "2. Querying – SQL for Patient Visit Insights"

      2. Inspect the SQL Query in query.txt:

         start query.txt

      3. Open the query_patients.ipynb in Jupyter Notebook:

         jupyter notebook query_patients.ipynb

      4. Execute all cells
   3. End-to-End Mini Project
      1. Navigate to third part directory:

          cd "3. End-to-End Mini Project"

      2. Fill the required Google AI API key in .env
      3. Run the FastAPI app:

         uvicorn app.main:app --reload --host 0.0.0.0 --port 8000

      4. Make an API call to POST /recommend:

         curl -X POST "http://localhost:8000/recommend" \
         -H "Content-Type: application/json" \
         -d '{"gender": "female", "age": 62, "symptoms": ["pusing", "mual", "sulit berjalan"]}'

      5. Alternatively, open the API documentation to make an API call and interact with the app:

         start "http://127.0.0.1:8000/docs"