Yes you are right, I forgot to change COUNT(*) to COUNT(id), as you mention COUNT(u.*) will also work. I just couldn't get the idea of generating a sequence form 0 to 6 to work correctly. The approach I'm using seems to give the correct results (with COUNT(u.id)).I am fairly certain this does not give you the correct results. Specifically, the minimum value for each cDate is going to be 1 since count(*) counts NULLs. count(u) should probably work.
Correct. generate_series(0,6) won't work since there is no context as whether it is supposed to cover a single week or multiple years or anything in between.
Any non-null column can be supplied to the count() function: count ignores nulls. In this case you want to ignore the placeholder null that you are creating during the left join. My original suggestion avoided these extra placeholder values and instead forces you to process the master date range and the user-by-date pieces separately and then substitute 0 for any master date where the corresponding user-by-date was missing. If performance were important it may be worth testing both versions otherwise my guess is this version is more readable (for you).
David J.
