Posted by Xiaoyu, Nov. 13, 2023, 9:16 p.m.
Q188 Top 1 popular question by department
My code:
with question as(
SELECT post_id, created_dt, employee_id
FROM google_forum
where post_type = 'question'
)
,comment as(
SELECT post_id, created_dt,employee_id
FROM google_forum
where post_type = 'comment'
)
,A as(
select q.post_id, q.employee_id as author_id, count(c.post_id) as num_comments
from question q
left join comment c on c.post_id = q.post_id and c.created_dt = q.created_dt and c.employee_id <> q.employee_id
group by q.post_id,q.employee_id
)
select department,post_id
from(
select e.department, A.post_id,
row_number() over(partition by e.department order by num_comments desc) as ranking
from A
inner join google_employee e on A.author_id = e.employee_id
)B
where ranking =1
After submit my code, the resullt is wrong. Please advise. Thanks!