I have a query that can be seen on this fiddle and I'm relatively new to PSQL, having a few months of experience.
As mentioned in the comment I should put the query here and the code is perfectly working. The reason for this question is to find an improvement on it.
The link to the third party is to show the result of it otherwise would be difficult to visualize it here.
WITH statuses_flow AS ( SELECT c.id, c.study_id, c.site_id, s.type AS status_type, s.timestamp AS status_from, sa.type, row_number() OVER (ORDER BY s.candidate_id, s.timestamp) AS row_no FROM public.candidates c JOIN public.statuses s ON s.candidate_id = c.id JOIN public.statuses sa ON sa.candidate_id = c.id AND sa.id in( SELECT max(id) FROM public.statuses GROUP BY candidate_id) WHERE c.study_id in('INIT1') AND c.site_id in('Test1') AND sa.type != 'ANONYMISED' ORDER BY row_no ASC ) SELECT statuses_flow.id, statuses_flow.study_id AS "studyId", statuses_flow.site_id AS "siteId", statuses_flow.status_type AS "statusType", statuses_flow.status_from AS "statusFrom", next_status.status_from AS "statusTo", CASE WHEN next_status.status_from IS NULL THEN NULL ELSE ( SELECT created_at AS first_contact FROM public.activities WHERE candidate_id = statuses_flow.id AND TYPE in('PHONE', 'SMS', 'EMAIL') AND created_at BETWEEN statuses_flow.status_from AND next_status.status_from ORDER BY created_at FETCH FIRST 1 ROWS ONLY) END AS "first_contact" FROM statuses_flow LEFT JOIN statuses_flow next_status ON statuses_flow.id = next_status.id AND statuses_flow.row_no + 1 = next_status.row_no WHERE statuses_flow.status_type in('PENDING_SITE', 'PENDING_CALLCENTER', 'INCOMPLETE', 'REJECTED_CALLCENTER', 'REJECTED_SITE', 'CONSENTED') ORDER BY statuses_flow.id, statuses_flow.status_from
This query collects and returns some metrics joining 3 tables about candidates being in a specific status during a medical study process.
The query in the real life is very slow and needs some adjustments to be written better.
The indexes are already applied on the original DB but still would be useful to find a better way.
The section of the query which I'm not convinced at this point is lines 29 to 42 from the DB fiddle link above.
I was wondering if by any chance could be changed to use a left join or another method to make a better performance.
It is returning per every candidate something as for example for one candidate the table below
|1||Study1||Site1||INCOMPLETE||2021-07-20 09:30:52.101055+00||2021-07-20 09:31:53.568346+00||NULL|
|1||Study1||Site1||PENDING_CALLCENTER||2021-07-20 09:31:53.568346+00||2021-07-20 09:35:34.171876+00||2021-07-20 09:31:55.849+00|
|1||Study1||Site1||PENDING_SITE||2021-07-20 09:35:34.171876+00||2021-07-20 09:52:42.185163+00||2021-07-20 09:35:56.642+00|
To explain, the same candidate, in a range of time can be in different statuses and for example, Candidate 1 (for simplicity C1) in the table above, was in 4 statuses.
C1 stayed in incomplete status from/to and on this status period was not contacted so that why we have null on last column
C1 was contacted in the next 2 statuses and we determine the
first_contact checking from the
activities table the minimum activity timestamps of the types PHONE, EMAIL, and SMS in the range of time C1 was in that specific status from/to.
The first_contact is the first time a candidate was contacted by EMAIL PHONE or SMS.
On the last status, we have
statusTo = null so also
firstContact is null.
The result I'm getting now is correct but seems not efficient to me and probably can get better but have no confidence in the way to manage the change in this situation.