Work Climate sorting per submissions

work_climate_sorting_per_submission.sql

-- LATERAL was introduced in PostgreSQL 9.3

-- Considering the last submission
SELECT employees.*, 
	   COALESCE(scores.value, -6) value
FROM employees
LEFT JOIN LATERAL (
    SELECT scores.*
    FROM scores
    WHERE scores.employee_id = employees.id
    	AND scores.submited_on BETWEEN '2024-03-05 09:23:07.097882-06' AND '2024-06-05 09:23:07.097882-06'
    	AND scores.facet_id = 1
    ORDER BY scores.submited_on DESC
    LIMIT 1 -- last submition
) scores ON true
ORDER BY value ASC;



-- Considering average of the last 4 recrods

SELECT employees.*, COALESCE(avg_scores.avg_value, -6) AS avg_value
FROM employees
LEFT JOIN LATERAL (
    SELECT AVG(subquery.value) AS avg_value
    FROM (
        SELECT scores.value
        FROM scores
        WHERE scores.employee_id = employees.id
          AND scores.submited_on BETWEEN '2024-03-05 09:23:07.097882-06' AND '2024-06-05 09:23:07.097882-06'
          AND scores.facet_id = 1
        ORDER BY scores.submited_on DESC
        LIMIT 4
    ) subquery
) avg_scores ON true
ORDER BY avg_value ASC;