Number of records created per day

Question

In my PostgreSQL database I have the following schema:

CREATE TABLE programs (
    id integer,
    description text
);

CREATE TABLE public.messages (
    id integer,
    program_id integer,
    text text,
    message_template_id integer
);

CREATE TABLE public.message_templates (
    id integer,
    deliver_day integer
);

INSERT INTO programs VALUES(1, 'Test program');

INSERT INTO messages VALUES(1,1, 'Test message 1', 1);
INSERT INTO message_templates VALUES(1, 1);

INSERT INTO messages VALUES(2,1, 'Test message 2', 2);
INSERT INTO message_templates VALUES(2, 3);

INSERT INTO messages VALUES(3,1, 'Test message 3', 3);
INSERT INTO message_templates VALUES(3, 5);

Now I want to get number of message sent per day throughout the life of the program, query result should look like this:

  day      count
--------|----------
   1         1
   2         0
   3         1
   4         0
   5         1

Is there any way of doing that in PostgreSQL?

https://www.db-fiddle.com/f/gvxijmp8u6wr6mYcSoAeVV/2

Answer 1

I decided to use generate_series:

SELECT d AS "Day", count(mt.id)  FROM generate_series(
  (SELECT min(delivery_day) from message_templates),
  (SELECT max(delivery_day) from message_templates)
) d
left join message_templates mt on mt.delivery_day = d 
group by d.d

Query is working fine. Maybe there is better way of doing this?

Answer 2

You could use this:

WITH tmp AS 
( 
    SELECT m.program_id, a.n AS d
    FROM generate_series(1, 
        (SELECT MAX(deliver_day) FROM message_templates) 
    )  AS a(n)
    CROSS JOIN 
    (
        SELECT DISTINCT program_id
        FROM messages
    ) m
)
SELECT t.program_id,
    t.d AS "day", 
    COUNT(m.program_id) AS "count" -- COUNT(m.id) 
FROM tmp t
LEFT JOIN message_templates mt
ON t.d = mt.deliver_day 
LEFT JOIN messages m
ON m.message_template_id = mt.id AND t.program_id = m.program_id
GROUP BY t.program_id, t.d 
ORDER BY t.program_id, t.d; 

Tested in db-fiddle