BeginnersMindTruly
BeginnersMindTruly

Reputation: 721

Deconstructing a complex SQL query into multiple queries and joins

Could someone please help break up this complex SQL query into individual steps? I am trying to build multiple tables and joins out of this multistep query that feels quite opaque. Thank you!

select 
    visit_date, 
    count(vo1.visit_id) as num_visits,
    sum(case when co1.person_id is not null then 1 else 0 end) as num_visits_w_cond
from 
    visit_occurrence vo1
left join
    (select distinct 
         person_id, visit_id, condition_date
     from 
         condition_occurrence
     where 
         condition_id = 12345)) co1 on vo1.person_id = co1.person_id
                                    and vo1.visit_date = co1.condition_date
                                    and vo1.visit_id = co1.visit_id
where 
    visit_id = 1234
group by 
    visit_date
order by 
    visit_date;

Ideally, I'd like to generate a few data tables in the intermediate steps and then join and count at the end but am not sure what this would look like.

EDIT: Thank you for your comments. Regarding clarity of the individual tables:

  1. The first select will query the visit_occurrence table for all visit_ids that match a visit_id # of "1234" and return the distinct person_id, visit_date, and visit_id for all visits. So a person_id, visit_date, visit_id tuple is unique, i.e. the same person_id with a different visit_date or different visit_id does not qualify as a duplicate. Only an identical tuple is a duplicate.

  2. The second select will query the condition_occurrence table for all condition_ids that match a condition_id # of 12345 and return distinct person_id, visit_id, condition_date. So a person_id, visit_id, condition_date tuple is unique, i.e. the same person_id with a different visit_id or condition_date is not a duplicate. Only an identical tuple is a duplicate.

  3. Join table 1 and table 2 on person_id, visit_date = condition_date, visit_id = visit_id. Then count how many distinct person_ids occur on each date.

  4. From table 1, count how many visit_ids are associated with each date.

Hopefully that's more clear? Thank you again for the feedback.

Upvotes: 0

Views: 1424

Answers (1)

anu start
anu start

Reputation: 395

I doubt this will perform any better, but it's close to what you're asking for. I would stay away from temp tables (which is what I inferred from your question).

with cteVO as --common table expression for visit_occurrence
(
    select distinct person_id, visit_date, visit_id
    from visit_occurrence
    where visit_id = 1234
),
cteCO as -- common table expression for condition_occurrence
(
    select distinct person_id, visit_id, condition_date
    from condition_occurrence 
    where condition_id = 12345
)
-- Join both CTEs to get the count of person_id and count of visit_id
SELECT cteVO.visit_date, COUNT(cteVO.person_id) AS count_person_id,
    COUNT(cteVO.visit_id) AS count_visit_id
FROM cteVO
INNER JOIN cteCO ON cteVO.person_id = cteCO.person_id 
    AND cteVO.visit_date = cteCO.condition_date
    AND cteVO.visit_id = cteCO.visit_id
GROUP BY cteVO.visit_date

Upvotes: 1

Related Questions