Get created as well as deleted records of last week

Question

With this query, I can get all the records created in the last week:

SELECT day, COALESCE(ct, 0) AS ct FROM (SELECT now::date - d AS day FROM generate_series (0, 6) d) d -- 6, not 7 LEFT JOIN ( SELECT created_at::date AS day, count(*) AS ct FROM entries WHERE created_at >= date_trunc('day', now()) - interval '6d' GROUP BY 1 ) e USING (day); 

It returns a result similar to this:

  count | date 2 | 15.01.2014 0 | 14.01.2014 1 | 13.01.2014 0 | 12.01.2014 0 | 11.01.2014 0 | 10.01.2014 9 | 09.01.2014 

Now I also want to display all deleted records of last week! I can get them through the deleted_at field: I tried:

 SELECT day, COALESCE(ct, 0) AS created, COALESCE(dl,0) AS deleted FROM (SELECT current_date - d AS day FROM generate_series (0, 6) d) d LEFT JOIN ( SELECT created_at::date AS day, count( CASE WHEN (created_at >= date_trunc('day', now()) - interval '6d') THEN 1 ELSE 0 END ) AS ct, count( CASE WHEN (canceled_at >= date_trunc('day', now()) - interval '6d') THEN 1 ELSE 0 END ) AS dl FROM entries GROUP BY 1 ) e USING (day); 

But it didn’t work! Now I get two lines that are the same:

 deleted | created | date 2 | 2 | 15.01.2014 0 | 0 | 14.01.2014 1 | 1 | 13.01.2014 0 | 0 | 12.01.2014 0 | 0 | 11.01.2014 0 | 0 | 10.01.2014 9 | 9 | 09.01.2014 

How am I wrong? How can I display created as well as deleted records?

Answer 1

Since both timestamps can exist 0 - n times in the timeframe and independently of each other, you need to do more:

Postgres 9.3+ required:

 WITH var(ts_min) AS (SELECT date_trunc('day', now()) - interval '6 days') SELECT day , COALESCE(c.created, 0) AS created , COALESCE(d.deleted, 0) AS deleted FROM var v CROSS JOIN LATERAL ( SELECT d::date AS day FROM generate_series (v.ts_min , v.ts_min + interval '6 days' , interval '1 day') d ) t LEFT JOIN ( SELECT created_at::date AS day, count(*) AS created FROM entries WHERE created_at >= (SELECT ts_min FROM var) GROUP BY 1 ) c USING (day) LEFT JOIN ( SELECT canceled_at::date AS day, count(*) AS deleted FROM entries WHERE canceled_at >= (SELECT ts_min FROM var) GROUP BY 1 ) d USING (day) ORDER BY 1; 

CTE var intended only for the convenience of providing an initial timestamp.

SQL Fiddle

Answer 2

At first glance, it looks like you're executing the count function, not the amount you need, simply by simply counting each record twice.

 sum( CASE WHEN (created_at >= date_trunc('day', now()) - interval '6d') THEN 1 ELSE 0 END) AS ct, sum(CASE WHEN (canceled_at >= date_trunc('day', now()) - interval '6d') THEN 1 ELSE 0 END) AS dl 

You need to use the amount, which will then add all cases when you return 1, and not the account, which counts all the values ​​regardless of whether they are 1 or 0!