Blame for kdd-benchmark/input/task_11/context/knowledge.md

# Knowledge Guide for Thrombosis Prediction Database

## 1. Introduction
This guide serves as a comprehensive semantic layer for the "thrombosis_prediction" database, providing a unified understanding of the data structure, key entities, and business metrics. It is designed to ensure consistency, accuracy, and clarity in data usage across the enterprise, adhering to strict data governance standards.

## 2. Core Entities & Fields

### Patient
- **ID (integer):** Unique identifier for each patient.
- **SEX (text):** Gender of the patient, denoted as 'M' for male and 'F' for female.
- **Birthday (date):** Date of birth of the patient.
- **Description (date):** The initial date when a patient's data was recorded.
- **First Date (date):** The date when a patient first visited the hospital.
- **Admission (text):** Indicates whether the patient was admitted to the hospital ('+') or followed up at the outpatient clinic ('-').
- **Diagnosis (text):** The disease diagnosed in the patient.

### Examination
- **ID (integer):** Unique identifier for each patient.
- **Examination Date (date):** Date of the medical examination.
- **Thrombosis (integer):** Degree of thrombosis, with '1' being the most severe and '2' indicating severe cases.
- **Symptoms (text):** Symptoms observed during the examination.

### Laboratory
- **ID (integer):** Unique identifier for each patient.
- **Date (date):** Date of the laboratory test.
- **LDH (integer):** Lactate dehydrogenase level, with values above 500 considered beyond the normal range.
- **UA (real):** Uric acid level, with specific normal ranges based on gender.

## 3. Metric Definitions

### Key Performance Indicators (KPIs)
- **Inpatient vs. Outpatient Ratio for Males:**
  - **Formula:** \(\text{Ratio} = \frac{\text{COUNT}(\text{ID}) \text{ where Admission} = '+' \text{ and SEX} = 'M'}{\text{COUNT}(\text{ID}) \text{ where Admission} = '-' \text{ and SEX} = 'M'}\)
  - **Description:** Measures the proportion of male patients admitted to the hospital versus those followed up at the outpatient clinic.

- **Percentage of Female Patients Born After 1930:**
  - **Formula:** \(\text{Percentage} = \frac{\text{COUNT}(\text{ID}) \text{ where year(Birthday)} > 1930 \text{ and SEX} = 'F'}{\text{COUNT}(\text{ID}) \text{ where SEX} = 'F'} \times 100\)
  - **Description:** Calculates the percentage of female patients born after 1930.

- **SLE Diagnosis Inpatient to Outpatient Ratio:**
  - **Formula:** \(\text{Ratio} = \frac{\text{COUNT}(\text{ID}) \text{ where Diagnosis} = 'SLE' \text{ and Admission} = '+'}{\text{COUNT}(\text{ID}) \text{ where Diagnosis} = 'SLE' \text{ and Admission} = '-'}\)
  - **Description:** Compares the number of inpatients to outpatients among patients diagnosed with SLE.

## 4. Constraints & Conventions

### Filtering Criteria
- **Gender-Based Filters:** Use 'SEX' field with 'M' for males and 'F' for females.
- **Admission Status:** Use 'Admission' field with '+' for inpatients and '-' for outpatients.

### Temporal Boundaries
- **Date Formats:** All dates are stored in 'YYYY-MM-DD' format.
- **Fiscal Year:** Aligns with the calendar year, starting on January 1st and ending on December 31st.

### Unit Conversions
- **Age Calculation:** Age is calculated as the difference between the current year and the year of birth.

## 5. Exemplar Use Cases

### Use Case 1: Calculate the Percentage of Male Patients with Abnormal Uric Acid Levels
- **SQL:** 
  ```sql
  SELECT CAST(SUM(CASE WHEN UA > 8.0 AND SEX = 'M' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(*) FROM Laboratory
  ```
- **Explanation:** This query calculates the percentage of male patients with uric acid levels above 8.0.

### Use Case 2: Identify Patients with Severe Thrombosis
- **SQL:** 
  ```sql
  SELECT DISTINCT ID, SEX, Diagnosis FROM Examination WHERE Thrombosis = 2
  ```
- **Explanation:** Lists patients with a thrombosis level of 2, indicating severe cases.

### Use Case 3: Determine the Average Age of Patients
- **SQL:** 
  ```sql
  SELECT AVG(SUBTRACT(YEAR(CURRENT_TIMESTAMP), YEAR(Birthday))) FROM Patient
  ```
- **Explanation:** Calculates the average age of all patients in the database.

### Use Case 4: List Female Patients Diagnosed with APS
- **SQL:** 
  ```sql
  SELECT ID, Birthday FROM Patient WHERE SEX = 'F' AND Diagnosis = 'APS'
  ```
- **Explanation:** Retrieves IDs and birthdays of female patients diagnosed with APS.

### Use Case 5: Count of Patients with Normal Platelet Levels
- **SQL:** 
  ```sql
  SELECT COUNT(*) FROM Laboratory WHERE PLT BETWEEN 100 AND 400
  ```
- **Explanation:** Counts the number of patients with platelet levels within the normal range.

## 6. Ambiguity Resolution

### Similar Fields
- **Diagnosis vs. Disease:** Both refer to the same attribute; use 'Diagnosis' for consistency.
- **Admission vs. Description:** 'Admission' refers to the patient's status (inpatient or outpatient), while 'Description' is the initial record date.

### Contextual Usage
- **Thrombosis Levels:** Ensure clarity by using specific integer values to denote severity, with '1' being the most severe.

This guide is intended to provide a clear and authoritative reference for data usage within the thrombosis_prediction database, ensuring consistency and accuracy in all analytical endeavors.