PostgreSQL – Find number of installs each day given a table of installs and uninstalls

I have a table for machine installations in this way:

installationID, machineID, installed_at, uninstalled_at
A, 1, 2020-01-01, Null
B, 2, 2020-01-01, 2020-01-02
C, 3, 2020-01-02, Null
D, 2, 2020-01-04, Null

I need a query that returns the number of installed machines per day. Like this:

Date, installed
2020-01-01, 2
2020-01-02, 3
2020-01-03, 2 
2020-01-04, 3

I know that given a date, say ‘2020-01-03’, I can get the number of installed machines as follows:

SELECT date, count(machineID) 
from installs 
where installed_at >= '2020-01-03' 
and (uninstalled_at is Null or uninstalled_at <= '2020-01-03')

However, I do not know how to query in such a way I can get the result for all dates at one query.

Answer

Using generated series from 2020-01-01 to 2020-01-15. Adjust ‘<= / >’ as needed.

select generate_series dt, count(*) n
from  generate_series('2020-01-01'::timestamp , '2020-01-15'::timestamp, '1 day')  
left join tbl on installed_at <= generate_series and ( uninstalled_at is null or uninstalled_at > generate_series)
group by generate_series
order by generate_series;