Group by month and name SQL

I need some help with SQL.

I have

• Table1 with columns Id, Date1 and Date2

• Table2 with columns Table1Id and Table2Id

• Table3 with columns Id and Name

Here is my try:

with tmp_tab as (
    select
        v."Name" as name
        , date_part('month', cv."OfferAcceptedDate") as MonthAcceptedName
        , date_part('month', cv."OfferSentDate") as MonthSentName
        , 1 as cntAcc
        , 1 as cntSent
        from hr_metrics."CvInfo" as cv
        join hr_metrics."CvInfoVacancy" as civ 
            on civ."CvInfosId" = cv."Id" 
        join hr_metrics."Vacancy" as v 
            on civ."VacanciesId" = v."Id" 
        where cv."OfferSentDate" is not null
        and date_part('year', cv."OfferSentDate") = date_part('year', CURRENT_DATE)
        group by  v."Name" , date_part('month', cv."OfferAcceptedDate"), 
        date_part('month', cv."OfferSentDate")
    )

select  distinct
    tmp_tab."name" as name,
    tmp_tab.MonthSentName as mSent,
    tmp_tab.MonthAcceptedName as mAcc,
    Sum(tmp_tab.cntSent) as sented,
    Sum(tmp_tab.cntacc) as accepted
    from tmp_tab as tmp_tab
group by tmp_tab.name, tmp_tab.MonthSentName, tmp_tab.MonthAcceptedName;

I need to take Count(date2)/Count(date1) grouped by monthes and name. I have no idea how to do that, as there is no table with monthes. DB – Postgres

sample data from comment:

t1
1 | 01/01/2021 | 31/03/2021 
2 | 05/01/2021 | 18/01/2021 
3 | 12/01/2021 | 31/01/2021 
4 | 13/03/2021 | 22/03/2021 


t2 
1 | 1 
2 | 1 
3 | 2 
4 | 1 

t3 
1 | SomeName1 
2 | someName2 

Desired result:

Name | month | value 
SomeName1 | 1 | 12 
SomeName1 | 3 | 2 
SomeName2 | 1 | 1 

Update: if count(date2) == 0, than count(date2) = -1

Answer

Source answer

Here code for my question thats work. And yeah, i’ve asked it on ru too.

    select name, month, sum((SRC=1)::int) as AcceptedCount, sum((SRC=2)::int) as SentCount,
       case when sum((SRC=1)::int) = 0 then -1
            else sum((SRC=2)::int)::float / sum((SRC=1)::int) end as Result
  from (
    select v.name, SRC,
           extract('month' from case SRC when 1 then OfferAcceptedDate else OfferSentDate end) as month
      from (select (date_part('year', CURRENT_DATE)::char(4) || '-01-01')::timestamptz as from_date) x
      cross join (select 1 as SRC union all select 2) s
      join CvInfo as cv on (SRC=1 and cv.OfferAcceptedDate >= from_date and cv.OfferAcceptedDate < from_date + interval '1 year')
                        or (SRC=2 and cv.OfferSentDate >= from_date and cv.OfferSentDate < from_date + interval '1 year')
       join CvInfoVacancy as civ on civ.CvInfosId = cv.Id
       join Vacancy as v on civ.VacanciesId = v.Id
      where case SRC when 1 then OfferAcceptedDate else OfferSentDate end is not null
  ) x
  group by name, month