[SQL] data.world tutorial SQL WITH 문 예제 풀이

WITH is a keyword that enables you to form complex queries out of simple named subqueries.

WITH 는 복잡한 쿼리를 단순한 이름의 subquery 로 구성하게 해주는 키워드이다. 

https://docs.data.world/documentation/sql/concepts/advanced/WITH.html

WITH

 

With the way our dataset is structured we can see a lot of information about what’s in the pipeline for sales agents, but we can’t easily see the connection between the managers and the pipeline.

데이터셋이 구조화된 방식에 따라 우리는 sales_agent 단위의  pipeline 에 대한 정보는 확인할 수 있지만, manager 단위의 pipeline 정보는 쉽게 확인할 수가 없다. 

 

However if we create a subquery using WITH we can run many different queries against it and get information for managers about accounts, deal stages, products, and deal values.

하지만 WITH 문을 사용하여 subquery 를 작성하면, manager 단위의 pipeline 정보: accounts, deal stages, products, deal values 등에 대한 정보를 추출할 수 있다. --- WITH 문도 subquery 의 일종이다. 

 

The ability to use the results of a query as though it were just another table makes WITH a powerful tool for querying.

WITH 문의 결과가 마치 다른 하나의 테이블이라고 생각하고 사용하면 된다. 

 

---

 

1. 요구사항 검토

For example, if we wanted to know which managers had the most closed deals and for which accounts

managers, accounts 단위로 closed deals 정보를 알고 싶다. (the most 를 가장 알고 싶다.)

 

2. 데이터셋 확인

- sales_teams: manager 와 sales agents 정보
- ales_pipeline: sales_agents 단위의 거래 정보

sales agents 기준으로 두개의 테이블을 JOIN 한다. 

 

3. 쿼리 구조 검토

WITH total_pipeline ( 매니저 단위 거래정보 ) 를 가지고
manager, account 단위로 거래량 정보, 성사된 거래만 거래량 제공, 거래량 기준 내림차순으로 정렬

 

4. 쿼리 작성

WITH total_pipeline AS ( SELECT teams.manager, teams.sales_agent, pipeline.account, pipeline.deal_stage, pipeline.close_value  FROM sales_pipeline pipeline JOIN sales_teams teams ON pipeline.sales_agent = teams.sales_agent ) SELECT manager, account, COUNT(close_value) FROM total_pipeline WHERE deal_stage = "Won" GROUP BY manager, account ORDER BY COUNT(close_value) DESC

 

 

---

1. 요구사항 검토

we are only showing the number of deals per manager from companies that have over 10,000 in revenue

추가로 요구사항이 들어왔다. 위 결과셋에서 revenue 기준 10,000 이상인 회사 (계정) 만 결과를 보고 싶다고-
(나중에 알고보니 국내 회사계정만 확인하면 된단다.)

 

2. 데이터셋 확인

accounts 테이블: accounts 별 revenue 지표 

이 테이블이 더 필요하다. 

 

3. 쿼리 구조 검토

WITH big_accounts AS ( 10,000 이상인 회사 계정 ), 

total_pipeline AS ( 매니저 단위 거래정보, 10,000이상인 회사계정만  )

manager, account 단위로 거래량 정보, 성사된 거래만 거래량 제공, 거래량 기준 내림차순으로 정렬

 

- WITH 로 참고할 subquery 를 2개 작성한다. 2번째 subquery 결과셋인 total_pipeline 에서는 앞서 작성한 1번째 subquery 결과셋인 big_accounts 를 활용할 수 있다. 

- WITH 문에서 사용된 (  )  subquery 에는 ORDER BY, LIMIT, OFFSET 등을 사용할 수 없다. (결과 출력하기 직전에 편의를 제공하는 이런 기능들은 제공하지 않는다.) 

 

4. 쿼리 작성

WITH big_accounts AS ( SELECT account FROM accounts WHERE revenue > 10000 ), total_pipeline AS ( SELECT teams.manager, teams.sales_agent, pipeline.account, pipeline.deal_stage, pipeline.close_value  FROM sales_pipeline pipeline JOIN sales_teams teams ON pipeline.sales_agent = teams.sales_agent JOIN big_accounts big_accounts ON pipeline.account = big_accounts.account ) SELECT manager, account, COUNT(close_value) FROM total_pipeline WHERE deal_stage = "Won" GROUP BY manager, account ORDER BY COUNT(close_value) DESC

 

---

주요 골자를 만들고 대상을 좁혀나가면 조금 더 수월한듯 :D