Posted by Raj, June 2, 2024, 11 p.m.
Question 188: Top 1 popular question by department(Using Postgres)
For this question, don't you have to set the two created_at columns equal to each other. The question states the following condition:
- Rank order the question's popularity by the number of comments on the same day the post was created.
Doesn't this mean the day the question was posted and the day the comment was posted should be the same.
For example, if the question was posted on 2021-09-01, then we want to find users that posted comments on 2021-09-01 to this question. The date the comment was posted was the same day as the day the question was posted
The following is the code I used for this question:
with employee_comment as(
SELECT ge.employee_id, ge.department, gf.post_id, gf.post_type, gf.created_dt
FROM google_employee as ge
join google_forum as gf on ge.employee_id = gf.employee_id
order by ge.employee_id
),
employee_question as(
select ec1.department, ec1.post_id, ec1.created_dt
from employee_comment as ec1
join employee_comment as ec2 on ec1.post_id= ec2.post_id
where ec1.post_type = 'question' and ec2.post_type = 'comment'
and ec1.employee_id != ec2.employee_id
and ec1.created_de = ec2.created_dt
order by ec1.department, ec1.post_id asc
),
top_questions as(
select department, post_id, count(*), row_number() over(partition by department order by
count(*) desc) as ranking
from employee_question
group by department, post_id
)
select department, post_id
from top_questions
where ranking = 1
For this question, I used Postgres