mesclar dois COUNT em uma consulta SQL

Question

Preciso fazer uma consulta para mostrar new_customers X customer_cancellations

com esta consulta posso obter os new_customers por mês:

selecione count(start_date), to_char(start_date, 'MM') como monthNumber, to_char(start_date, 'YY') como yearNumber
do cliente onde start_date não é nulo
agrupar por to_char(start_date, 'MM'), to_char(start_date, 'YY')
ordem por anoNúmero, mêsNúmero;

com este outro, consigo os cancelamentos dos clientes por mês:

selecione count(cancellation_date), to_char(cancellation_date, 'MM') como monthNumber, to_char(cancellation_date, 'YY') como yearNumber
do cliente onde cancel_date não é nulo
agrupar por to_char(data_cancelamento, 'MM'), to_char(data_cancelamento, 'AA')
ordem por anoNúmero, mêsNúmero;

as duas consultas retornam algo como:

contar| número do mês | número do ano
1 | 1 | 20
7 | 2 | 20
5 | 3 | 20

mas gostaria de algo assim:

customer_out_count|customer_in_count| número do mês | número do ano
 0 |1 | 1 | 20
 0 |7 | 2 | 20
 1 |0 | 3 | 20
 0 |1 | 4 | 20
 5 |7 | 5 | 20
 1 |5 | 6 | 20

Eu já tentei esta outra consulta:

selecione 'start' como tipo, count(start_date) como contagem, to_char(start_date, 'MM') como monthNumber, to_char(start_date, 'YY') como yearNumber
do cliente onde start_date não é nulo
agrupar por to_char(start_date, 'MM'), to_char(start_date, 'YY')
União
selecione 'cancellation' como tipo, count(cancellation_date) como counta, to_char(cancellation_date, 'MM') como monthNumber, to_char(cancellation_date, 'YY') como yearNumber
do cliente onde cancel_date não é nulo
agrupar por to_char(data_cancelamento, 'MM'), to_char(data_cancelamento, 'AA')
ordem por anoNúmero, mêsNúmero;

O resultado é "ok":

digite |newcount | número do mês | número do ano
 iniciar |1 | 1 | 20
 cancelamento |1 | 1 | 20
 cancelamento |7 | 2 | 20
 iniciar |3 | 3 | 20
 cancelamento |1 | 4 | 20
 iniciar |7 | 5 | 20
 iniciar |5 | 6 | 20

Mas precisarei fazer algumas operações no código para conseguir o que preciso.

Como posso mesclar essas duas consultas em uma? Estou usando o postgreslq.

Answer 1

Uma opção desarticula as linhas e, em seguida, agrega. No Postgres, você expressaria isso com uma junção lateral:

select
    sum(x.customer_in_count)  as customer_in_count,
    sum(x.customer_out_count) as customer_out_count
    to_char(x.dt, 'MM') as monthNumber, 
    to_char(x.dt, 'YY') as yearNumber
from customer c
cross join lateral (values
    (c.start_date, 1, 0), (c.cancellation_date, 0, 1)
) as x(dt, customer_in_count, customer_out_count)
where x.dt is not null
group by monthNumber, yearNumber
order by monthNumber, yearNumber