OP15: Counts of age, stratified by gender

This query is used to count the age across all observation records stratified by gender (gender_concept_id). The age value is defined by the earliest observation date. Age is summarized for all existing gender_concept_id values.


Sample query run:
The following is a sample run of the query. 
SELECT age, 
       count(*) AS num_people
  FROM /* person, gender, age */
     ( SELECT person_id, NVL( concept_name, 'MISSING' ) AS gender
            , extract( YEAR FROM first_observation_date ) - year_of_birth AS age
         FROM -- person, first observation date
            ( SELECT person_id
                   , min( observation_period_start_date ) AS first_observation_date
                FROM observation_period
               GROUP BY person_id
         JOIN person USING( person_id )
         LEFT OUTER JOIN vocabulary.concept ON concept_id = gender_concept_id
        WHERE year_of_birth IS NOT NULL
GROUP BY age, gender 
ORDER BY age, gender
Output field list:
 Field  Description
age Age across within observation
gender Gender concept name stratification
num_people Number of person within group

Sample output record:
 Field  Description
age  1
gender  MALE
num_people  22501