Montaseri wrote:
> Given table T1 and columns id, start_date, stop_date and status, propose
> a query that reports count of items opened and closed . status is an
> enum including NEW, xxx, xxxx, CLOSED. The first status of an item is
> NEW (could be used in place of start_date)
> For example
>
> Date Opened Closed
> =============================
> yyyy-mm-dd 25 6
> yyyy-mm-dd 0 16
> yyyy-mm-dd 12 0
> etc
Divide and Conquer; break up a large task into smaller tasks.
1) Generate a range of dates (this one goes back 1 year):
select (now()::date+generate_series(-365,0) * interval '1 days')::date
2) How many projects were opened on each day?
select start,count(*) as open from t1 group by start
3) How many projects were opened on each day?
select stop,count(*) as closed from t1 group by stop
Now do put these all together with subselects and joins
select date,open,closed from (
select (now()::date+generate_series(-365,0) * interval '1 days')::date
) as series
left join (
select start,count(*) as open from t1 group by start
) as t2 on t2.start::date=series.date::date
left join (
select stop,count(*) as closed from t1 group by stop
) as t3 on t3.stop::date=series.date::date
where open is not null or closed is not null
Frank
