186. Average comments per question

easy google

  • Write a query to return the average number of comments per question.
  • Only include comments made on the same day a question is asked.
  • 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.

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

Expected results

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.


More Google questions

ID Title Level FTPR
187 Comment distribution at Google Forum google easy
50%
188 Top 1 popular question by department google hard
100%