[DataLemur] 실행 쿼리 수 -> 히스토그램 표

* join 시에 ON과 WHERE에 조건을 거는 상황의 차이를 명확히 드러내주는 문제라서 기록한다.

 

https://datalemur.com/questions/sql-ibm-db2-product-analytics

IBM SQL Interview Question | DataLemur

 

테이블 (1. queries, 2.employees)

• 1. queries: 직원들이 실행한 모든 쿼리에 대한 기록
• 2. employees: 모든 직원에 대한 정보

---

문제

• Display the number of unique queries as histogram categories, along with the count of employees who executed that number of unique queries.
• 수행된 쿼리 수와 해당 쿼리 수를 실행한 직원들의 수 => 요약해서, "히스토그램 카테고리"로 표를 보여줘라!
• 조건
  • 2023/07-2023/09 사이의 값만 보여줄 것
  • 쿼리 날리지 않은 직원도 포함 (즉 the number of unique queries가 0이고, 그에 해당하는 직원 수까지 보여줘야 정답이라는 것)

---

쿼리문 (틀린 ver)

처음에 쿼리를 다음과 같이 짰는데 답이 틀렸다고 나왔다. 아래의 쿼리문 (정답ver)와 교차해보며 다른점을 확인해보면 아~주 사소하다는 것을 알 수 있다. 날짜 범위 조건을 on절에 걸었는지 / where절에 걸었는지의 차이!

with cte as (
  select e.employee_id,count(distinct q.query_id) as unique_queries
  from employees e 
  left join queries q  
  on e.employee_id = q.employee_id 
  where to_char(query_starttime, 'YYYY-MM') between '2023-07' and '2023-09'
  group by 1)
  
  select unique_queries, count(employee_id) as employee_count
  from cte
  group by unique_queries
  order by 1

 

 

이 쿼리의 결과는 다음과 같이 나온다. unique_queries가 0인 값은 나오지 않는다. 왜그럴까?

 

🚨 문제점

• LEFT JOIN 으로 employees를 기준으로 queries 테이블을 붙인다.
• WHERE 절에서 query_starttime이 특정 기간에 포함되는 조건을 걸면:
  • query_starttime IS NULL인 행은 조건을 만족하지 못해 삭제됨.
  • 결과적으로 쿼리를 한 번도 실행하지 않은 직원은 제거됨.

⚠️ 이슈

• LEFT JOIN을 했지만 WHERE절에서 필터링이 들어가면서 INNER JOIN처럼 작동한다.
• 즉, 쿼리를 2023-07 ~ 2023-09에 실행한 직원만 포함됨. 
• 다음 테이블을 예시로 들어보자면, jiming jang의 데이터는 삭제됨
  

  e.employee_id	e.full_name	e.gender	q.employee_id	q.query_id	q.query_starttime	q.execution_time
  1	bella jeong	female	1	34	2023-08-07	34
  2	jimin jang	male	NULL	NULL	NULL	NULL

 

---

쿼리문 (정답 ver)

with cte as (
  select e.employee_id,count(distinct q.query_id) as unique_queries
  from employees e 
  left join queries q  
  on e.employee_id = q.employee_id 
  and to_char(query_starttime, 'YYYY-MM') between '2023-07' and '2023-09'
  group by 1)
  
  select unique_queries, count(employee_id) as employee_count
  from cte
  group by unique_queries
  order by 1

 

✅해결방식

 

• 날짜 필터 조건을 WHERE절이 아니라 ON절에 포함시킴.
• 조인 조건에 부합하는 쿼리만 연결되고, 부합하지 않으면 NULL로 남겨둠.
• 즉, employees 테이블의 모든 직원은 유지되고,
  • 쿼리를 아예 실행하지 않은 직원
  • 해당 기간(2024/07~2024/09)에만 쿼리를 실행하지 않은 직원
    • 이 두 그룹 모두 queries 테이블 정보가 NULL로 처리된다.
• 이번 쿼리에서는 jiming jang의 데이터는 그대로 남음

  e.employee_id	e.full_name	e.gender	q.employee_id	q.query_id	q.query_starttime	q.execution_time
  1	bella jeong	female	1	34	2023-08-07	34
  2	jimin jang	male	NULL	NULL	NULL	NULL

---

정리

상황	조건	이유
🎯 조인 후에도 기준 테이블의 모든 행을 유지하고 싶을 때 (예: LEFT JOIN에서 기준 테이블 전부 포함)	ON절	조건에 맞는 대상만 붙이고, 나머지는 NULL로 남김 (제외되지 않음)
🔍 조건에 맞는 행만 분석 대상으로 삼고 싶을 때 (필터링 목적)	WHERE절	조건을 만족하는 행만 남기고 나머지는 제거함