统计开发push数据

-- commit 次数统计排名
select author_id,b.email,b.name,count(1) as sum_cnt ,
sum(case when to_char(a.created_at,‘yyyy-mm‘)=‘2020-01‘ then 1 end) as "1mon",
sum(case when to_char(a.created_at,‘yyyy-mm‘)=‘2020-02‘ then 1 end)  as "2mon",
sum(case when to_char(a.created_at,‘yyyy-mm‘)=‘2020-03‘ then 1 end)  as "3mon",
sum(case when to_char(a.created_at,‘yyyy-mm‘)=‘2020-04‘ then 1 end)  as "4mon",
sum(case when to_char(a.created_at,‘yyyy-mm‘)=‘2020-05‘ then 1 end)  as "5mon",
sum(case when to_char(a.created_at,‘yyyy-mm‘)=‘2020-06‘ then 1 end)  as "6mon",
sum(case when to_char(a.created_at,‘yyyy-mm‘)=‘2020-07‘ then 1 end)  as "7mon",
sum(case when to_char(a.created_at,‘yyyy-mm‘)=‘2020-08‘ then 1 end)  as "8mon",
sum(case when to_char(a.created_at,‘yyyy-mm‘)=‘2020-09‘ then 1 end)  as "9mon",
sum(case when to_char(a.created_at,‘yyyy-mm‘)=‘2020-10‘ then 1 end)  as "10mon",
sum(case when to_char(a.created_at,‘yyyy-mm‘)=‘2020-11‘ then 1 end)  as "11mon",
sum(case when to_char(a.created_at,‘yyyy-mm‘)=‘2020-12‘ then 1 end)  as "12mon"
from events a left join users b on a.author_id=b.id
where a.created_at>=‘2020-01-01‘ and b.state=‘active‘ and a.action=5
group by 1,2,3 order by 4 desc

-- 每个开发提交最多小时的是每天的几点
select ww.name,ww.email,ww.h_time,ww.cnt from (select wa.email,wa.name,wa.h_time,wa.cnt, ROW_NUMBER() OVER( partition by wa.email,wa.name  ORDER BY cnt desc )  as rnk from
 (select b.email,b.name,extract(hour from a.created_at) as h_time,count(1) as cnt from
events a left join users b on a.author_id=b.id
where a.created_at>=‘2020-01-01‘ and b.state=‘active‘ and a.action=5
group by 1,2,3)wa ) ww where ww.rnk=1