Report SQLs

SELECT EXTRACT(YEAR FROM to_timestamp(arh.created_at)) AS year, EXTRACT(MONTH FROM to_timestamp(arh.created_at)) AS month, 
COUNT(DISTINCT case when arh.event = 1 then c.id end) as unfinished, 
COUNT(DISTINCT case when arh.new_value = '2' then c.id end) as new,
COUNT(DISTINCT case when (arh.new_value = '4' OR arh.new_value = '8') AND (arh.prev_value = '2' OR arh.prev_event = 1) then c.id end) as rejection,
COUNT(DISTINCT case when arh.new_value = '3' AND (arh.prev_value = '2' OR arh.prev_event = 1) then c.id end) as in_progress,
COUNT(DISTINCT case when arh.new_value = '7' AND arh.prev_value = '3' then c.id end) as interview, 
COUNT(DISTINCT case when arh.new_value = '5' AND arh.prev_value = '7' then c.id end) as active,
COUNT(DISTINCT case when (arh.new_value = '4' OR arh.new_value = '8') AND arh.prev_value = '7' then c.id end) as rejection_after_interview,
COUNT(DISTINCT case when arh.new_value = '6' AND (arh.prev_event = 1 OR arh.prev_value = '2' OR arh.prev_value = '3' OR arh.prev_value = '7') then c.id end) as offline
FROM (
SELECT c.id, arh.created_at, arh.event, arh.new_value, 
LAG(arh.new_value) OVER (PARTITION BY arh.row_id ORDER BY arh.created_at) prev_value,
LAG(arh.event) OVER (PARTITION BY arh.row_id ORDER BY arh.created_at) prev_event
FROM arhistory AS arh
LEFT JOIN candidates AS c ON arh.row_id = c.id
WHERE arh.table_name = 'candidates' AND c.date_add > '2022-10-01 00:00:00' AND c.id IS NOT NULL AND (arh.field_name = 'status_id' OR arh.field_name IS NULL)
) AS arh
LEFT JOIN candidates AS c ON c.id = arh.id
GROUP BY year, month
ORDER BY year, month;

SELECT EXTRACT(YEAR FROM c.date_add) AS year, EXTRACT(MONTH FROM c.date_add) AS month, 
AVG((GREATEST(rejectionDate, rejectionDeletionDate) - unfinishedDate)/60/60/24) AS avg_days_to_rejection,
AVG((inProgressDate - unfinishedDate)/60/60/24) AS avg_days_to_inprogress,
AVG((interviewDate - inProgressDate)/60/60/24) AS avg_days_from_inprogress_to_interview,
AVG((activeDate - interviewDate)/60/60/24) AS avg_days_from_interview_to_active,
AVG((offlineDate - unfinishedDate)/60/60/24) AS avg_days_from_unfinished_to_offline
FROM (
SELECT DISTINCT ON (unfinishedDate, newDate, inProgressDate, rejectionDate, activeDate, offlineDate, interviewDate, rejectionDeletionDate) 
c.id, 
MAX(arh.created_at) filter (where arh.event = 1) as unfinishedDate,
MAX(arh.created_at) filter (where arh.new_value = '2') as newDate,
MAX(arh.created_at) filter (where arh.new_value = '3') as inProgressDate,
MAX(arh.created_at) filter (where arh.new_value = '4') as rejectionDate,
MAX(arh.created_at) filter (where arh.new_value = '5') as activeDate,
MAX(arh.created_at) filter (where arh.new_value = '6') as offlineDate,
MAX(arh.created_at) filter (where arh.new_value = '7') as interviewDate,
MAX(arh.created_at) filter (where arh.new_value = '8') as rejectionDeletionDate
FROM arhistory AS arh
LEFT JOIN candidates AS c ON arh.row_id = c.id
WHERE arh.table_name = 'candidates' AND c.date_add > '2022-10-01 00:00:00' AND c.id IS NOT NULL AND (arh.field_name = 'status_id' OR arh.field_name IS NULL)
GROUP BY c.id
ORDER BY unfinishedDate, newDate, inProgressDate, rejectionDate, activeDate, offlineDate, interviewDate, rejectionDeletionDate) AS arh
LEFT JOIN candidates AS c ON c.id = arh.id
GROUP BY year, month
ORDER BY year, month;