I need to write a code where I need to identify the first time someone was diagnosed with a particular clinical condition. Is this the most efficient script using Min Event Dates, Distinct IDs and using the DENSE RANK functions.
with table1 as
(
SELECT
distinct a.patid,
b.person_id,
MIN(a.event_date) AS earliest_eventdate,
DENSE_RANK() OVER (PARTITION BY coalesce(b.person_id, a.patid) ORDER BY a.event_date) AS row_num
FROM

clinical a
inner JOIN patient b ON a.patid = b.patid

WHERE
snomed_id in
(‘12345678’)

group by a.patid, b.person_id, event_date

),
table2 as
(

select * from table1 where row_num =1

)
select 

SUBSTRING(t.earliest_eventdate::text, 1, 7) AS year_2024,
COUNT(DISTINCT CASE WHEN (t.person_id IS NULL) THEN t.patid ELSE t.person_id END) AS patient
FROM
table2 t

WHERE

 t.earliest_eventdate::date BETWEEN (select month_start from clinical_latest_month)  AND  (select month_end from clinical_latest_month)
 
GROUP BY
year_2024
ORDER BY year_2024

Script above but i want to make it more efficient ?