Complicated select from one table

Question

-- I'm trying to create a report for Call Center with quality rating system.
-- Most of the calls are routed to the queues, where they wait to be answered by agents.
-- When finishing the call, agent can hangup, requeue the call to another queue
-- or requeue the call to the Rating queue, where caller may rate (or not)
-- the quality of service (e.g. 1..5)
-- There are also cases when calls are not answered, do not reach queue, etc.
-- Below is a simplified example table. 
-- Requeued call keeps the same callid, but requeuecount is increased by 1.

callid | queue   | agent | rate | requeuecount
:----- | :------ | :---- | ---: | -----------:
122    |         | Olga  | null |           -1
123    |         |       | null |           -1
124    | Sales   |       | null |            0
125    | Service | Tom   | null |            0
126    | Sales   | Yan   | null |            0
127    | Sales   | Kim   | null |            0
127    | Service | John  | null |            1
127    | Rating  |       |    4 |            2
128    | Service | Oleg  | null |            0
128    | Rating  |       | null |            1
129    | Service | John  | null |            0
130    | Service | John  | null |            0
130    | Rating  |       |    2 |            1
131    | Sales   | Kim   | null |            0
131    | Service | Oleg  | null |            1
132    | Service | Oleg  | null |            0
132    | Rating  |       |    5 |            1

-- Those only agents who requeued calls to Rating queue.
-- queue name, agent name, number of calls requeued to Rating, 
-- number of rated calls, average rating

select cr.queue, cr.agent, count(cr.callid) as req2rating, 
count(cr1.rate) as ratingscount, avg(cr1.rate) as averagerating
from callrecord cr
inner join callrecord cr1 
on cr.callid=cr1.callid
and cr.requeuecount=cr1.requeuecount-1
and cr1.queue='Rating'
group by cr.queue, cr.agent

queue   | agent | req2rating | ratingscount | averagerating
:------ | :---- | ---------: | -----------: | ------------:
Service | John  |          2 |            2 |             3
Service | Oleg  |          2 |            1 |             5

-- All agents with their number of received calls.
-- queue name, agent name, number of calls he have got

select queue, agent, count(callid) as totalcalls
from callrecord
group by queue, agent

queue   | agent | totalcalls
:------ | :---- | ---------:
        |       |          1
Rating  |       |          4
Sales   |       |          1
Service | John  |          3
Sales   | Kim   |          2
Service | Oleg  |          3
        | Olga  |          1
Service | Tom   |          1
Sales   | Yan   |          1

-- I want to combine the two above tables into one.
-- Straightforward method, that works:

select s1.queue, s1.agent, s1.totalcalls, s2.req2rating, s2.ratingscount, s2.averagerating from
  (select queue, agent, count(callid) as totalcalls
  from callrecord
  group by queue, agent) s1
left join
  (select cr.queue, cr.agent, count(cr.callid) as req2rating, 
  count(cr1.rate) as ratingscount, avg(cr1.rate) as averagerating
  from callrecord cr
  inner join callrecord cr1 
  on cr.callid=cr1.callid
  and cr.requeuecount=cr1.requeuecount-1 and cr1.queue='Rating'
  group by cr.queue, cr.agent) s2
on s1.queue=s2.queue and s1.agent=s2.agent

queue   | agent | totalcalls | req2rating | ratingscount | averagerating
:------ | :---- | ---------: | ---------: | -----------: | ------------:
        |       |          1 |       null |         null |          null
Rating  |       |          4 |       null |         null |          null
Sales   |       |          1 |       null |         null |          null
Service | John  |          3 |          2 |            2 |             3
Sales   | Kim   |          2 |       null |         null |          null
Service | Oleg  |          3 |          2 |            1 |             5
        | Olga  |          1 |       null |         null |          null
Service | Tom   |          1 |       null |         null |          null
Sales   | Yan   |          1 |       null |         null |          null

-- Maybe there is a more clever and efficient way of doing this?
-- I mean using only one select operand.

db<>fiddle here

Answer 1

You can make a select of callrecords-agents and left join on callrecords-ratings in this manner:

select cr.queue, cr.agent, count(cr.callid) as totalcalls,
       count(rt.callid) as req2rating,
       count(rt.rate) as ratingscount,
       avg(rt.rate) as averagerating
  from callrecord as cr
  left join callrecord as rt on
            rt.callid = cr.callid and
            rt.queue = 'Rating' and
            rt.requeuecount = cr.requeuecount+1
 group by cr.queue, cr.agent

This returns req2rating and ratingscount as 0, if you want null could use the nullif function:

select cr.queue, cr.agent, count(cr.callid) as totalcalls,
       nullif(count(rt.callid), 0) as req2rating,
       nullif(count(rt.rate), 0) as ratingscount,
       avg(rt.rate) as averagerating
  from callrecord as cr
  left join callrecord as rt on
            rt.callid = cr.callid and
            rt.queue = 'Rating' and
            rt.requeuecount = cr.requeuecount+1
 group by cr.queue, cr.agent