Select data based on records created_at column

Question

I have two tables Keywords, ProjectReports:

http://sqlfiddle.com/#!15/06ae3/1

As you can see in that example everything works fine, but I want to get more data from those 2 tables and I don't know how.

1. In the position column I would like to display only the smallest value and just one value instead of that array. ex: [1, 2, 3] will be 1
2. As you can see there is the keyword with id 4 which doesn't have any data in the project_reports table, in this case I would like to add the value 0 in the position column and add the current day date in the CREATED_AT column.
3. Also I would like to display the position value for the same keywords, where created_at column from project_reports table had the value now() - interval '1 day'; now() - interval '1 week'; now() - interval '1 mon', if there is no position for those days add 0.

Expected output:

Keywords:

 ID: 1 name: test
 ID: 2 name: test1
 ID: 3 name: test2

Project_reports:

 ID: 1, keyword_id: 1, project_id: 1, position: 1, created_at: '2014-07-09'
 ID: 2, keyword_id: 1, project_id: 1, position: 2, created_at: '2014-07-09'
 ID: 3, keyword_id: 1, project_id: 1, position: 3, created_at: '2014-07-10'
 ID: 5, keyword_id: 1, project_id: 1, position: 4, created_at: '2014-07-10'
 ID: 6, keyword_id: 2, project_id: 1, position: 1, created_at: '2014-07-09'

After query

k_name: test, k_id: 1, p_id: 1, position 3, yesterday_position: 1, created_at: '2014-07-10'
k_name: test1, k_id: 2, p_id: 1, position 0, yesterday_position: 1, created_at: '2014-07-10'
k_name: test2, k_id: 3, p_id: 1, position 0, yesterday_position: 0, created_at: '2014-07-10'
k_name: test, k_id: 1, p_id: 1, position 1, yesterday_position: 0, created_at: '2014-07-09'
k_name: test1, k_id: 2, p_id: 1, position 1, yesterday_position: 0, created_at: '2014-07-09'
k_name: test2, k_id: 3, p_id: 1, position 0, yesterday_position: 0, created_at: '2014-07-09'

• Erwin Brandstetter: I don't see primary keys in your example. Those make a difference. Can we assume the id columns to be primary keys?
• alexsmn: yes the id is the primary key, sorry for that.
• Erwin Brandstetter: OK, then your test data are flawed. Duplicate keys in project_reports. Please clarify.
• alexsmn: I'm not sure, which keys are duplicated?
• Erwin Brandstetter: Found this by chance. Check out how @replies works.

Answer 1

Note the slightly modified schema of my test in the fiddle.
Using actual primary keys and proper column names instead of id.

Also, you seem to be operating with dates exclusively. So I suggest to convert your timestamp columns to date.

Items 1 and 2

SELECT k.keyword_id
 , k.name
 , pr.project_id
 , COALESCE(min(pr.position), 0) AS pos
 , COALESCE(pr.created_at, now()::date) AS created_at
FROM keyword k 
LEFT JOIN project_report pr USING (keyword_id)
GROUP BY k.keyword_id, pr.project_id, pr.created_at
ORDER BY keyword_id, created_at
;

• In Postgres 9.1 or later the pk column covers the whole table in GROUP BY.
• Use COALESCE to replace possible NULL values.

A guess at item 3

WITH cte AS (
 SELECT k.keyword_id
 , k.name
 , pr.project_id
 , COALESCE(min(pr.position), 0) AS pos
 , COALESCE(pr.created_at, now()::date) AS created_at
 FROM keyword k 
 LEFT JOIN project_report pr USING (keyword_id)
 GROUP BY k.keyword_id, pr.project_id, pr.created_at
 )
, x AS (
 SELECT DISTINCT ON (keyword_id, project_id) *
 FROM cte
 ORDER BY keyword_id, project_id, created_at DESC
 )
SELECT x.*
 , COALESCE(y.pos, 0) AS yesterday_pos 
 , COALESCE(w.pos, 0) AS week_pos 
 , COALESCE(m.pos, 0) AS month_pos 
FROM x
LEFT JOIN cte y ON y.keyword_id = x.keyword_id
 AND y.project_id = x.project_id
 AND y.created_at = x.created_at - interval '1 day'
LEFT JOIN cte w ON w.keyword_id = x.keyword_id
 AND w.project_id = x.project_id
 AND w.created_at = x.created_at - interval '1 week'
LEFT JOIN cte m ON m.keyword_id = x.keyword_id
 AND m.project_id = x.project_id
 AND m.created_at = x.created_at - interval '1 month'
;

Explain

1. In CTE cte produce daily aggregates per (k.keyword_id, pr.project_id).
2. In CTE x pick the latest day per (k.keyword_id, pr.project_id).
3. In the outer query LEFT JOIN the latest day x to cte multiple times to retrieve past values from the same (k.keyword_id, pr.project_id) for 1 day / week / month earlier.

SQL Fiddle.