Medical inquiry and Medical Inquiry Response Insights - Urgency vs Response Status Analysis and Summary of Specialities

Introduction: This analysis focuses on investigating the relationship between the urgency level of medical inquiries (e.g., "High", "Medium", "Low") and their corresponding response status (e.g., "Resolved", "Pending"). Also inquiries across various medical specialties, such as Oncology, Cardiology, and Neurology, and examine the corresponding status of these inquiries (whether they are Resolved or still Pending).This helps identify which urgencies and specialties have more pressing or complex inquiries that require quicker resolution and highlights areas where follow-up may be necessary.

 

Requirements:

 

Use the table ‘t1_medical_inquiry' and 't1_medical_inquiry_response’. Calculate the inquiries between Urgency and Response Status with count of ‘InquiryID'.

 

Count inquiries by specialty and status by aggregation count of ‘InquiryID'

 

Final Output: Show the result of Urgency vs Response Status Analysis and summary of inquiries by specialty and status.

 

Unity Catalog: 'purgo_playground.t1_medical_inquiry',

 

'purgo_playground.t1_medical_inquiry_response'.

 

Write the Pyspark code without syntax error.