Postgres: Count multiple events for distinct dates

People of Stack Overflow!

Thanks for taking the time to read this question. What I am trying to accomplish is to pivot some data all from just one table. The original table has multiple datetime entries of specific events (e.g. when the customer was added add_time and when the customer was lost lost_time). This is one part of two rows of the deals table:

id	add_time	last_mail_time	lost_time
5	2020-03-24 09:29:24	2020-04-03 13:20:29	NULL
310	2020-03-24 09:29:24	NULL	2020-04-03 13:20:29

I want to create a view of this table. A view that has one row for each distinct date and counts the number of events at this specific time.

This is the goal (times do not match with the example!):

I have working code, like this:

SELECT DISTINCT
    change_datetime,
    (SELECT COUNT(add_time) as add_time_count FROM deals WHERE add_time::date = change_datetime),
    (SELECT COUNT(lost_time) as lost_time_count FROM deals WHERE lost_time::date = change_datetime) 
FROM (
    SELECT
        add_time::date AS change_datetime
    FROM
        deals
    UNION ALL
    SELECT
        lost_time::date AS change_datetime
    FROM
        deals
    ) AS foo
WHERE change_datetime IS NOT NULL
ORDER BY
    change_datetime;

but this has some ugly O(n2) queries and takes a lot of time.

Is there a better, more performant way to achieve this?

Thanks!!

Answer

You can use a lateral join to unpivot and then aggregate:

select t::date,
       count(*) filter (where which = 'add'),
       count(*) filter (where which = 'mail'),
       count(*) filter (where which = 'lost')
from deals d cross join lateral
     (values (add_time, 'add'),
             (last_mail_time, 'mail'),
             (lost_time, 'lost')
     ) v(t, which)
group by t::date;