# 186. Average comments per question

• Write a query to return the average number of comments per question.
• E.g., if a question is asked in 2022-01-01 and received 1 comment on 2022-01-01 and 3 comments on 2022-01-02, only count the 1 comment.

```   col_name   | col_type
--------------+--------------------------
created_dt   | date
post_id      | int
post_type    | varchar(20) -- either a question or a comment
employee_id  | int
content      | text
```

Sample results

```          avg
------------------------
0.72631578947368421053
```

Expected results

Solution postgres

``````WITH questions AS (
SELECT post_id, created_dt
WHERE post_type = 'question'
),
SELECT post_id, created_dt
WHERE post_type = 'comment'
)

FROM questions Q
ON Q.post_id = C.post_id
AND Q.created_dt = C.created_dt
GROUP BY 1
) X;```
```

Explanation

This query calculates the average number of comments per question in a Google forum. It does this by first creating two temporary tables, "questions" and "comments," which contain the post ID and creation date for all posts of the respective types. Then, it joins these two tables on the post ID and creation date to count the number of comments for each question. Finally, it takes the average of these counts to get the desired result.