- ********************************************************************
- ***************************************************************
- ***************************************************************************************************************************************
Table: google_forum
Google's internal Q&A forum.
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
Solution postgres
WITH questions AS (
SELECT post_id, created_dt
FROM google_forum
WHERE post_type = 'question'
),
comments AS (
SELECT post_id, created_dt
FROM google_forum
WHERE post_type = 'comment'
)
SELECT avg(num_comments) FROM (
SELECT Q.post_id, COUNT(C.post_id) AS num_comments
FROM questions Q
LEFT JOIN comments C
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.
Copied
Expected results
Your results