Medication Orders & Diagnosis Codes Used

with cte_icd_code as (
    select
        id
      , code
      , description
    from icd10
    qualify row_number() over (partition by id order by id desc) = 1
)
select
   mo.ID as "MED_ORDER_ID"
 , mo.patient_id
 , pt.first_name as "PATIENT_FIRST_NAME"
 , pt.last_name as "PATIENT_LAST_NAME"
 , pt.dob as "PATIENT_DOB"
 , mo.MEDICATION_ID
 , mo.DISPLAYED_MEDICATION_NAME
 , med.brand_name
 , med.generic_name
 , icd.code
 , icd.description
 , concat('(', mo.signed_by_user_id, ')', ' ', s_user.first_name, ' ', s_user.last_name, ', ', s_user.credentials) as "SIGNED_BY"
 , mo.SIGNED_TIME
 , concat('(', mo.created_by_user_id, ')', ' ', c_user.first_name, ' ', c_user.last_name, ', ', c_user.credentials) as "CREATED_BY"
 , mo.CREATION_TIME
 , concat('(', mo.deleted_by_user_id, ')', ' ', d_user.first_name, ' ', d_user.last_name, ', ', d_user.credentials) as "DELETED_BY"
 , mo.DELETION_TIME
 , mo.LAST_MODIFIED
from med_order_icd10_codes dx
  join med_order mo on mo.id = dx.med_order_id
  join cte_icd_code icd on icd.id = dx.icd10_code
  join patient pt on pt.id = mo.patient_id
  left join medication med on med.id = mo.medication_id
  left join user c_user on mo.created_by_user_id = c_user.id
  left join user d_user on mo.deleted_by_user_id = d_user.id
  left join user s_user on mo.signed_by_user_id = s_user.id;