Medical Inquiry and Medical Inquiry Responses Insights

Introduction: Efficient management of Medical Inquiries and their corresponding Medical Inquiry Responses is critical to ensure timely and accurate communication with Healthcare Professionals (HCPs). These inquiries often pertain to drug-related information, such as storage conditions, dosage, side effects, and interactions. Effective tracking and resolution of these inquiries contribute to compliance with regulatory requirements and improved HCP satisfaction.

 

Requirements:

 

'purgo_playground.t1_medical_inquiry', 'purgo_playground.t1_medical_inquiry_response'.

 

overall Inquiries-response data: Join both tables by ‘InquiryID'.

 

unresolve inquiries: Filter the unresolve inquiries by status as ‘Pending’.

 

Aggregate inquiries by ‘InquiryType’ and 'urgency’ and aggregate the count of resolved as resolved count from overall Inquiries-response data and pending as pending count from unresolve inquiries.

 

Final Output: Show overall inquiry-response data, unresolved inquiries and aggregated data from resolved .

 

Write the Pyspark code without syntax error.

 

Unity Catalog details: 'purgo_playground.t1_medical_inquiry', 'purgo_playground.t1_medical_inquiry_response'