I wrote an SQL query to find how many leases were "active" for each day of 2018. So if a contract began on 27/12/18 and ended on 27/12/18 and another one began on 27/12/18 and ended on 28/12/18, the number for 27/12/18 is 2 and the number for 28/12/18 is 1.
My query works fine but while trying to write this query I learned new functions like coalesce() and generate_series() but I am not sure if I used them in a decent way.
Registrationform(PK plate:varchar, PK period_begin:timestamp, PK period_end:timestamp, FK email:varchar)
SELECT TO_CHAR(ts::date, 'DD/MM/YYYY') AS date, coalesce(count, 0) AS number FROM (SELECT date::date, count(*) FROM registration_form cross join generate_series(period_begin, period_end, '1day'::interval) date where period_end between '2018-01-01' and '2018-12-31' GROUP BY 1 order by date) as m RIGHT JOIN (SELECT * FROM generate_series(timestamp '2018-01-01', timestamp '2018-12-31', interval '1 day')) AS series(ts) ON m.date = series.ts