Tarlen Tarlen - 3 months ago 15
SQL Question

Grouping median by day of week

I am trying to group the median response time on conversations by weekday

This is what I got so far, but it does not group correctly (multiple entries for each app_id, each with it's own value of the response time. Instead these should be grouped into one, and only display the median)

Any ideas?

select
c.app_id,
case when extract(dow from c.first_response_at) = 1 then percentile_cont(0.5) within group ( order by c.response) else 0 end as mon,
case when extract(dow from c.first_response_at) = 2 then percentile_cont(0.5) within group ( order by c.response) else 0 end as tue,
case when extract(dow from c.first_response_at) = 3 then percentile_cont(0.5) within group ( order by c.response) else 0 end as wed,
case when extract(dow from c.first_response_at) = 4 then percentile_cont(0.5) within group ( order by c.response) else 0 end as thu,
case when extract(dow from c.first_response_at) = 5 then percentile_cont(0.5) within group ( order by c.response) else 0 end as fri,
case when extract(dow from c.first_response_at) = 6 then percentile_cont(0.5) within group ( order by c.response) else 0 end as sat,
case when extract(dow from c.first_response_at) = 7 then percentile_cont(0.5) within group ( order by c.response) else 0 end as sun
from
(
with t(app_id, response, first_response_at, updated_at) as (
select app_id, extract(epoch from (first_response_at - started_at)) as response_time, first_response_at, c.updated_at
from apps a
left join conversations c on c.app_id = a.id
order by c.updated_at desc
)
select app_id, response, first_response_at, updated_at
from t
) as c
group by c.app_id, c.first_response_at, c.updated_at
order by c.updated_at desc

Answer

#1: You need to move the CASE into PERCENTILE_CONT as proposed by Gordon Linoff.

#2: remove , c.first_response_at, c.updated_at from group by

#3: order by min or max(c.updated_at)

#4: no need for a nested CTE/Order By

#5: move the Extract(dow) into the Deried Table

select
    c.app_id,
    PERCENTILE_CONT(0.5) WITHIN GROUP ( ORDER BY CASE WHEN dow = 1 THEN c.response end) as mon,
    PERCENTILE_CONT(0.5) WITHIN GROUP ( ORDER BY CASE WHEN dow = 2 THEN c.response end) as tue,
    PERCENTILE_CONT(0.5) WITHIN GROUP ( ORDER BY CASE WHEN dow = 3 THEN c.response end) as wed,
    PERCENTILE_CONT(0.5) WITHIN GROUP ( ORDER BY CASE WHEN dow = 4 THEN c.response end) as thu,
    PERCENTILE_CONT(0.5) WITHIN GROUP ( ORDER BY CASE WHEN dow = 5 THEN c.response end) as fri,
    PERCENTILE_CONT(0.5) WITHIN GROUP ( ORDER BY CASE WHEN dow = 6 THEN c.response end) as sat,
    PERCENTILE_CONT(0.5) WITHIN GROUP ( ORDER BY CASE WHEN dow = 7 THEN c.response end) as sun
from
 (
   select app_id,
       extract(epoch from (first_response_at - started_at)) as response_time, 
       EXTRACT(dow FROM c.first_response_at) as dow,
       c.updated_at
   from apps a
   left join conversations c on c.app_id = a.id
 ) as c
group by c.app_id
order by max(c.updated_at) desc
Comments