Two optimization questions

Hi,

I think it can be done better than I did and I want to learn...


1. I have a table that registers the history of messages:
output_message_history(id, event_type, event_time)
I need those ID-s from the table where there is one 'MESSAGE SENT' event 
and one 'MESSAGE SUBMITTED' event and there are no more events on that 
message.

select id
from output_message_history
group by content_id
having
sum(case when event_type='MESSAGE SENT' then 1 else 0 end) = 1
and
sum(case when event_type='MESSAGE SUBMITTED' then 1 else 0 end) = 1
and count(*) = 2

Can it be done another, more optimal way?


2. I have to create reports like this one:

time       count
12:00      3
12:01      2
12:02      4
12:03      0  <--- now this one is problematic for me
12:04      5

So I need something like this:

select date_trunc('minute', crd), count(*) from subscriber
where crd between '2006-09-08' and '2006-09-12'
group by date_trunc('minute', crd)

But the output of this query won't show minutes with 0 count. I searched 
the archives and found an example using a view to solve this problem, 
but creating such a view for grouping by minutes or seconds doesn't seem 
to be nice solution. Any ideas how to solve this problem?

Thanks for any answers,

Zizi