Count encounters: how many patients have >=X number of post-transplant encounters?

[comorbidity]

Example SQL query

WITH 
total as 
(
    select      count(distinct subject_ref) as cnt_pat,
                count(distinct encounter_ref) as cnt_enc,
                count(distinct documentreference_ref) as cnt_doc
    from        irae__sample_casedef_post
),
group_by_pat as 
(
    select      subject_ref, 
                count(distinct encounter_ref) as cnt_enc,
                count(distinct documentreference_ref) as cnt_doc
    from        irae__sample_casedef_post
    group by    subject_ref 
), 
group_by_enc as 
(
    select      cnt_enc, 
                count(distinct subject_ref) as cnt_pat
    from        group_by_pat
    group by    cnt_enc 
    order by    cnt_enc
), 
cumulative as 
(
    select  E1.cnt_enc,
            sum(E2.cnt_pat) as sum_pat
    from    group_by_enc as E1,
            group_by_enc as E2, 
            total
    where   E1.cnt_enc >= E2.cnt_enc        
    group by E1.cnt_enc
    order by E1.cnt_enc
)
select  cumulative.cnt_enc, 
        cumulative.sum_pat,  
        round(cumulative.sum_pat * 100.0 / total.cnt_pat, 2) as prct_pat
from    cumulative, 
        total 

[comorbidity]

BCH "average" number of encounters per patient = 72 encounters (wow!)

That is awesome 👏 from a study perspective and also hard from a chart review perspective. If we review "every encounter" at least once, that means on average 72 notes per patient (too many). Consider Dirichlet Multinomial where possible at 95% confidence.

Total: 310 patients at BCH

  5% of patients have <=7 post-transplant encounters
25% of patients have <=39 post-transplant encounters
50% of patients have <=72 post-transplant encounters
75% of patients have  <=121 post-transplant encounters
90% of patients have <= 178 post-transplant encounters
95% of patients have <= 230 post-transplant encounters

[comorbidity]

WITH 
total as 
(
    select      count(distinct subject_ref) as cnt_pat,
                count(distinct encounter_ref) as cnt_enc,
                count(distinct documentreference_ref) as cnt_doc
    from        irae__sample_casedef_post
),
group_by_enc as 
(
    select      encounter_ref, 
                count(distinct documentreference_ref) as cnt_doc
    from        irae__sample_casedef_post
    group by    encounter_ref
), 
group_by_doc as 
(
    select      cnt_doc, 
                count(distinct encounter_ref) as cnt_enc
    from        group_by_enc
    group by    cnt_doc
), 
cumulative as 
(
    select  G1.cnt_enc, 
            sum(G1.cnt_doc) as sum_doc
    from    group_by_doc as G1,
            total
    group by G1.cnt_enc
    order by G1.cnt_enc
)
select  cumulative.cnt_enc,  
        cumulative.sum_doc,  
        round(cumulative.sum_doc * 100.0 / total.cnt_doc, 2) as prct_doc
from    cumulative, 
        total 
order by cumulative.cnt_enc