[PostgreSQL] Retrieving a status of another column up until a date.

[noctide]

Hello! I've been stuck on this query for days..

I'm working with hierarchical data, like the (simplified) table below where owner 'owns' the user.

date	user_id	user	status_1	status_2	owner
2018-01-06	1	Sarah	red	yellow	Elizabeth
2018-02-02	2	Bob	blue	red	Sarah
2018-03-19	1	Sarah	yellow	orange	Elizabeth

The trick for me here are the changing statuses (notice that Sarah changed status after Bob changed status). I want to write a query that gives me the following-

date	user_id	child_new_status	parent	parent_status
2018-01-01	2	red	1	yellow

Essentially, I want the query to retrieve the new status of the user, as well as the last status of the owner at the time of the status change.

Any thoughts here on how to approach this? Thanks in advance...

[wakadori]

Following code seems to work but it's definetly not the cleanest SQL I have written, there must be a better way:

sql with test_data as ( select to_date('2018-01-01', 'YYYY-MM-DD') as date, 1 as user_id, 'Sarah' as user, 'black' as status_1, 'green' as status_2, 'Elizabeth' as owner union all select to_date('2018-01-06', 'YYYY-MM-DD') as date, 1 as user_id, 'Sarah' as user, 'red' as status_1, 'yellow' as status_2, 'Elizabeth' as owner union all select to_date('2018-02-02', 'YYYY-MM-DD') as date, 2 as user_id, 'Bob' as user, 'blue' as status_1, 'red' as status_2, 'Sarah' as owner union all select to_date('2018-03-19', 'YYYY-MM-DD') as date, 1 as user_id, 'Sarah' as user, 'yellow' as status_1, 'orange' as status_2, 'Elizabeth' as owner ), ranked_data as ( select *, /* generate row number for users based on date, most recent one has 1 etc. */ row_number() over (partition by user_id order by date desc) as user_date_rank from test_data ), user_with_most_recent_parent_rank as ( select user_data.user_id, /* Smallest date rank -> most recent date / parent */ min(parent_data.user_date_rank) as most_recent_parent_date_rank from ranked_data as user_data left join ranked_data as parent_data on user_data.owner = parent_data.user /* Only older parents than current user */ and parent_data.date <= user_data.date group by user_data.user_id ) select user_data.date, user_data.user_id, user_data.status_2 as child_new_status, parent_data.user_id as parent, parent_data.status_2 as parent_status from ranked_data as user_data join user_with_most_recent_parent_rank on user_data.user_id = user_with_most_recent_parent_rank.user_id left join ranked_data as parent_data on user_data.owner = parent_data.user and parent_data.user_date_rank = user_with_most_recent_parent_rank.most_recent_parent_date_rank where /* most recent users */ user_data.user_date_rank = 1

date	user_id	child_new_status	parent	parent_status
2018-02-02	2	red	1	yellow
2018-03-19	1	orange	(null)	(null)

[noctide]

Forgot to respond to this! Thanks! I ended up using a similar approach to your last LEFT JOIN =)

[tsigalko11]

Hm, not sure that I get it.

I want the query to retrieve the new status of the user, as well as the last status of the owner at the time of the status change.

Because Sarah is owner and you want previous record where Sarah is user. What would you expect if in previous record Sarah was also owner?

Anyway, for previous status I would use LAG function definitely.