Page Comparison

with number_of_months_to_show as
(
  select  select 8 --how many months to show on the chart
    asas show
),
  
month as --generating months
(
select       
  
select       
  (date_trunc('month', now()) - interval '1' month* generate_series(0,(select show from number_of_months_to_show)-1)) as month
),
  
states as --collecting info
(
   select select to_char(month, 'YYYY-MM') as month,
  state  state, title, created_at, merge_date, subject, target_branch
from month a
left join pullrequest b on b.created_at between month and month+interval '1 month'
)
  
select
        monthmonth::varchar,
        sumsum(case when state='COMPLETED' then 1 else 0 end) as "Accepted",
        sumsum(case when state='ACTIVE' then 1 else 0 end) as "In Review",
     sum   sum(case when state='ABANDONED' then 1 else 0 end) as "Rejected"
from states
group by month
order by month asc

select
    titletitle, created_at, merge_date, subject, target_branch
from states
where state='COMPLETED'
and month=clicked_x_value

with month as
(
select       
  
select       
  (date_trunc('month', now()) - interval '1' month* generate_series(0,7)) as month
),
  
states as
(
  select  select to_char(month, 'YYYY-MM') as month,
    statestate, title, created_at, merge_date, subject, target_branch
from month a
left join pullrequest b on b.created_at between month and month+interval '1 month'
)
  
select
        monthmonth::varchar,
        100100.0*sum(case when state='COMPLETED' and subject='Repo1' then 1 else 0 end)/NULLIF(sum(case when subject='Repo1' then 1 else 0 end),0) as "Repo1",
        100100.0*sum(case when state='COMPLETED' and subject='Repo2' then 1 else 0 end)/NULLIF(sum(case when subject='Repo2' then 1 else 0 end),0) as "Repo2",
        100100.0*sum(case when state='COMPLETED' and subject='Repo3' then 1 else 0 end)/NULLIF(sum(case when subject='Repo3' then 1 else 0 end),0) as "Repo3"
from states
group by month
order by month asc

select
    titletitle, created_at, merge_date, state, target_branch
    fromfrom
(select *
from states
where state!='COMPLETED' and subject='Repo1' ) as temp
where month=clicked_x_value