CO17: Distribution of condition occurrence records per person

This query is used to provide summary statistics for the number of condition occurrence records (condition_occurrence_id) for all persons: the mean, the standard deviation, the minimum, the 25th percentile, the median, the 75th percentile, the maximum and the number of missing values. There is no input required for this query.

Input:
<None>

Sample query run:
The following is a sample run of the query. 
with ranked as
  (
  SELECT num_of_conditions, sum(1) over (partition by 1 
  ORDER BY num_of_conditions asc rows BETWEEN unbounded preceding AND current row) AS rownumasc
  FROM ( 
                                        SELECT count(*) as num_of_conditions
                                        FROM condition_occurrence
                                        WHERE person_id!=0
                                        GROUP BY person_id
                                )
  ),
  other_stat AS
  (
   SELECT count(num_of_conditions) AS condition_num_count,
                        min(num_of_conditions) AS condition_num_min,
      max(num_of_conditions) AS condition_num_max,
                        avg(num_of_conditions) AS condition_num_averege,
      stddev(num_of_conditions) as condition_num_stddev
   FROM (
                                   SELECT count(*) AS num_of_conditions, person_id
                                   FROM   condition_occurrence
                                   WHERE person_id!=0
                                   GROUP BY person_id
                           )
  )
SELECT
 (  
        SELECT count(distinct person_id)
        FROM condition_occurrence
        WHERE person_id!=0 AND condition_occurrence_id IS NULL
 ) AS condition_null_count,
 * FROM other_stat,
 (
  SELECT num_of_conditions AS condition_num_25percentile 
  FROM (SELECT *,(SELECT count(*) FROM ranked) as rowno FROM ranked)
  WHERE (rownumasc=cast (rowno*0.25 as int) AND mod(rowno*25,100)=0) OR
     (rownumasc=cast (rowno*0.25 as int) AND mod(rowno*25,100)>0) OR
     (rownumasc=cast (rowno*0.25 as int)+1 AND mod(rowno*25,100)>0)
 ) AS condition_num_25percentile,
 (
  SELECT num_of_conditions AS condition_num_median
  FROM (SELECT *,(SELECT count(*) FROM ranked) AS rowno FROM ranked)
  WHERE (rownumasc=cast (rowno*0.50 AS int) AND mod(rowno*50,100)=0) OR
     (rownumasc=cast (rowno*0.50 AS int) AND mod(rowno*50,100)>0) OR
     (rownumasc=cast (rowno*0.50 AS int)+1 AND mod(rowno*50,100)>0)
 ) AS condition_num_median,
 (
  SELECT num_of_conditions AS condition_num_75percentile
  FROM (SELECT *,(SELECT count(*) FROM ranked) as rowno FROM ranked)
  WHERE (rownumasc=cast (rowno*0.75 as int) AND mod(rowno*75,100)=0) OR
     (rownumasc=cast (rowno*0.75 as int) AND mod(rowno*75,100)>0) OR
     (rownumasc=cast (rowno*0.75 as int)+1 AND mod(rowno*75,100)>0)
 ) AS condition_num_75percentile
Output:
Output field list:
 Field  Description
condition_null_count Number of persons with at least one null condition_occurrence_id
condition_num_count Number of distinct persons with conditions
condition_num_min The lowest number of condition occurences
condition_num_max The highest number of condition occurences
condition_num_averege The average number of condition occurences
condition_num_stddev The standard deviation of condition occurence numbers
condition_num_25percentile A condition occurence number where 25 percent of the other numbers are lower
condition_num_median A condition occurence number where half of the other numbers are lower and half are higher
condition_num_75percentile A condition occurence number where 75 percent of the other numbers are lower

Sample output record:
 Field  Description
condition_null_count 4395019
condition_num_count  
condition_num_min 1
condition_num_max 7144
condition_num_averege 51
condition_num_stddev 86.63
condition_num_25percentile 11
condition_num_median 26
condition_num_75percentile 58
Comments