You need a query that returns the number of receivers on each theme, in two days intervals, is this correct?
yes, this is correct. Actually the interval in days is variable ( it would specified using PreparedStatemtent ).
Sorry I didn't say from the begining.
Please try something like this. I am not sure if it works, because I don't have a table like yours.
I am assuming your table is called table
SELECT a."date",a."theme",a.receiver,COUNT(*)
FROM table a
INNER JOIN table b
ON a."theme" = b."theme" AND a.receiver = b.receiver
WHERE (((b."date" - a."date") = 1) OR (b."date" - a."date") = 0)
GROUP BY a."date",a."theme",a.receiver
HAVING (a."date" - '2007-01-01' ) % 2 = 0
ORDER BY a."date"
The query above it's not working, I've got the following error:
ERROR: operator does not exist: interval % integer
HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.
