1
\$\begingroup\$

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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.