| Table of Contents | ||
|---|---|---|
|
Reports Logic
Based on current tables next data must be aggregated and analyzed:
...
Using replicated tables from fraud data mart and BI tool the reports with outliers must be shown.
Until there is no enough statistic to use statistical methods to find outliers, the top least(5%, 25 rows) can be shown instead.
For now there are 3 reports are required - for autorization, doctors and phone_numbers.
Predefine views with aggregation
1. Doctor aggregation
number of patients per doctor (total_patients_doctor)
doctors with patients_qty<= 10 are not taken into account.
Conditions: employee, declarations
- employee.id=declarations.employee_id
- employee.employee_type='DOCTOR'
- employee.is_active=true
- employee.status='APPROVED'
- declarations.is_active=true
- declarations.status='active'
Fields:
do not take into account party_id with avg(qty_person_id_60)=0, exclude them from report.
| SOURCE | FIELD | NAME | DESCRIPTION | ||
|---|---|---|---|---|---|
| employee | party_id | party_id | |||
| employee | legal_entity_id | legal_entity_id | |||
| declarations | count(person_id) | patients_qty | qty patients by doctor (total till report date) | ||
| declarations | avg(qty_person_id_30)/ avg(qty_person_id_60) | patient_increase_30d | For each doctor calculate lifetime - (report_date-inserted_at) If doctor lifetime <=90 days or avg(qty_person_id_60)=0 - then null if doctor lifetime >= 90 days and avg(qty_person_id_60)>0: avg(qty_person_id_30) - average number of patients (new declarations) for a doctor for the last 30 days avg(qty_person_id_60) - average number of patients (new declarations) for a doctor for 60 days before last 30 days $inserted_at | report_date | the date when calculated |
number and percentage of patients per doctor that have set offline authorization method (autorization_doctor)
doctors with patients_qty<= 10 are not taken into account.
Conditions:
- employee.id=declarations.employee_id
- declarations.person_id=presons.id
- employee.division_id=divisions.id
- employee.employee_type='DOCTOR'
- employee.is_active=true
- employee.status='APPROVED'
- declarations.is_active=true
- declarations.status='active'
- divisions.addresses.{type:"RESIDINCE"}
...
| SOURCE | FIELD | NAME | DESCRIPTION | |||
|---|---|---|---|---|---|---|
| employee | party_id | party_id | ||||
| employee | legal_entity_id | legal_entity_id | ||||
| divisions | residence_settlement_type | residence_settlement_type | when type<>'CITY' then 'OTHER' else type | |||
| persons | count(authentication_methods.type='OFFLINE'.person_id) | offline_patients_qty | authentication_methods.type='OFFLINE' | |||
| persons | count(authentication_methods.type='OFFLINE'.person_id)/count(person_id) | ratio_offline_patients_qty | ratio of patients with offline method of authorization within particular doctor | |||
| persons | count(person_id) | patients_qty | qty patients by doctor (total till report date) | $inserted_at | report_date | the date when calculated |
2. Patient aggregation
Using
number of patients with same phone number (patients_phonenumber)
phone numbers with patients_qty<= 1 are not taken into account.
The table recalculates on daily basis
...
number and percentage of patients per legal_entity that have set offline authorization method (autorization_legal_entity)
legal entities with patients_qty<= 50 are not taken into account.
Conditions:
- employee.id=declarations.employee_id
- declarations.person_id=presons.id
- employee.division_id=divisions.id
- employee.employee_type='DOCTOR'
- employee.is_active=true
- employee.status='APPROVED'
- declarations.is_active=true
- declarations.status='active'
- divisions.addresses.{type:"RESIDINCE"}
...
| SOURCE | FIELD | NAME | DESCRIPTION | |||
|---|---|---|---|---|---|---|
| employee | legal_entity_id | legal_entity_id | ||||
| divisions | divisions.addresses.residence_settlement_type | residence_settlement_type | when type<>'CITY' then 'OTHER' else type | |||
| persons | count(authentication_methods.type='OFFLINE'.person_id) | offline_patients_qty | authentication_methods.type='OFFLINE' | |||
| persons | count(authentication_methods.{type}='OFFLINE'.person_id)/count(person_id) | ratio_offline_patients_qty | ratio of patients with offline method of authorization within particular legal entity | |||
| persons | count(person_id) | patients_qty | qty patients by legal entity (total till report date) | $inserted_at | report_date | the date when calculated |
Authorizations_fraud report
Using
- autorization_legal_entity table - legal entities with patients_qty<= 50 are not taken into account.
autorization_doctor table - doctors with patients_qty<= 10 are not taken into account.
Required input filter parameters:
- settlement_type ['CITY', 'OTHER']
- type ['DOCTOR', 'LEGAL_ENTITY']
Order by ratio_offline_patients_qty desc and show least(5%, 25 rows) with the highest value.
Output fields:
...
| report |
...
| date |
...
Phone_numbers_fraud report
Using
- patients_phonenumber table - phone numbers with patients_qty<= 1 are not taken into account.
Order by ratio_offline_patients_qty desc and show least(5%, 25 rows) with the highest value.
Output fields:
- phone_number
- patients_qty
- report_date
Doctors_fraud report
Using
- total_patients_doctor table - doctors with patients_qty<= 10 are not taken into account.
...
| ) |
...
- patients_qty
- patient_increase_30d
Output fields:
...