Forum

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!

Answers

Think the biggest issue is this one:

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 

Not quite understand the logic here.

I'd suggest run the CTE one by one and compare the results of the CTE in our solution side by side to figure it out.

The rest of the code looks good to me.

 

SQLPad user avatar

Leon (949)

Nov. 16, 2023, 6:32 p.m.