188. Top 1 popular question by department

hard google

  • **************************************************************
  • ****************************************************************************************************
  • *************************************************************************************************

Table 1: google_employee

   col_name   | col_type
 employee_id  | int
 start_dt     |  date
 department   | varchar(30)

Table 2: 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

   department   | post_id
 Engineering    |      19
 Executives     |     120
 Human Resource |      97
 Marketing      |      50

Solution postgres

WITH questions AS (
    SELECT post_id, F.employee_id AS author_id, created_dt, E.department
    FROM google_forum F
    INNER JOIN google_employee E
    ON E.employee_id = F.employee_id
    WHERE post_type = 'question'
comments AS (
    SELECT post_id, employee_id AS commentor_id, created_dt
    FROM google_forum 
    WHERE post_type = 'comment'
department_rankings AS (
    SELECT department, post_id, ROW_NUMBER()OVER(PARTITION BY department ORDER BY num_comments DESC) AS ranking
    FROM (
        SELECT Q.post_id, department, COUNT(C.post_id) AS num_comments
        FROM questions Q
        INNER JOIN comments C
        ON Q.post_id = C.post_id
        AND Q.author_id != C.commentor_id
        GROUP BY 1, 2
    ) X
SELECT department, post_id
FROM department_rankings
WHERE ranking =1;


This query retrieves the top-ranked post IDs for each department in a Google forum. It does this by first creating two subqueries, one for questions and one for comments, and joining them on employee IDs. It then creates a third subquery that counts the number of comments for each post and assigns a ranking within each department based on the number of comments. Finally, it selects the department and post ID for each department's top-ranked post.

Expected results

More Google questions

ID Title Level FTPR
101 Average number of visits per user google easy
102 Histogram by visit session duration google hard
103 Median and average session duration by day of the week google medium
104 MAU: Monthly active users google easy
105 Number of days gap between last two actions google hard
106 Mobile vs. web google medium
107 Customer count by platform google medium
147 2 days streak customers google hard
148 Most popular video category google medium
149 fans by video category google easy
150 Countries with above average customers google easy
186 Average comments per question google easy
187 Comment distribution at Google Forum google easy