Workings for our public salary data https://medlo.com.au/doctor-salary-australia

calculate-nsw-psych-rates.sql

SELECT
  EXTRACT(MONTH FROM s."shiftStart")::int AS month,

  /* ---------- average hourly rate ---------- */
  ROUND(AVG(
    CASE
      WHEN s."positionParentCategory" = 'Consultant' THEN
            CASE
              WHEN s."isDayRate"   THEN s.cost / NULLIF(s.hours, 0)
              WHEN s."isHourlyRate" THEN s.rate
            END
    END
  ))::int AS average_rate_consultant,

  ROUND(AVG(
    CASE
      WHEN s."positionParentCategory" = 'Registrar' THEN
            CASE
              WHEN s."isDayRate"   THEN s.cost / NULLIF(s.hours, 0)
              WHEN s."isHourlyRate" THEN s.rate
            END
    END
  ))::int AS average_rate_registrar,

  /* ---------- shift counts ---------- */
  COUNT(CASE WHEN s."positionParentCategory" = 'Consultant' THEN 1 END)::int
    AS shift_count_consultant,
  COUNT(CASE WHEN s."positionParentCategory" = 'Registrar' THEN 1 END)::int
    AS shift_count_registrar,

  h."state" AS state
FROM "shift" s
JOIN "hospital" h ON h."id" = s."hospitalId"

/* ----------- filters (same logic you apply in JS) ----------- */
WHERE (
        (s."isDayRate"    = TRUE AND s.cost BETWEEN 1000 AND 50000) OR
        (s."isHourlyRate" = TRUE AND s.rate BETWEEN 100  AND 1000 )
      )
  AND s."hours" > 0                               -- protect against ÷0
  AND s."shiftStart" >= NOW() - INTERVAL '12 months'
  AND h."state" = 'NSW'

GROUP BY month, h."state"
ORDER BY month ASC