- **************************************************************
- ****************************************************************************************************
- *************************************************************************************************
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;
;
Explanation
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.
Copied
Expected results
Your results