[LeetCode][MySQL] 1693. Daily Leads and Partners

Easy 🔗1693. Daily Leads and Partners

📝문제 요약

Table: DailySales

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| date_id     | date    |
| make_name   | varchar |
| lead_id     | int     |
| partner_id  | int     |
+-------------+---------+
There is no primary key (column with unique values) for this table. It may contain duplicates.
This table contains the date and the name of the product sold and the IDs of the lead and partner it was sold to.
The name consists of only lowercase English letters.

For each date_id and make_name, find the number of distinct lead_id’s and distinct partner_id’s.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input:
DailySales table:
+-----------+-----------+---------+------------+
| date_id   | make_name | lead_id | partner_id |
+-----------+-----------+---------+------------+
| 2020-12-8 | toyota    | 0       | 1          |
| 2020-12-8 | toyota    | 1       | 0          |
| 2020-12-8 | toyota    | 1       | 2          |
| 2020-12-7 | toyota    | 0       | 2          |
| 2020-12-7 | toyota    | 0       | 1          |
| 2020-12-8 | honda     | 1       | 2          |
| 2020-12-8 | honda     | 2       | 1          |
| 2020-12-7 | honda     | 0       | 1          |
| 2020-12-7 | honda     | 1       | 2          |
| 2020-12-7 | honda     | 2       | 1          |
+-----------+-----------+---------+------------+
Output:
+-----------+-----------+--------------+-----------------+
| date_id   | make_name | unique_leads | unique_partners |
+-----------+-----------+--------------+-----------------+
| 2020-12-8 | toyota    | 2            | 3               |
| 2020-12-7 | toyota    | 1            | 2               |
| 2020-12-8 | honda     | 2            | 2               |
| 2020-12-7 | honda     | 3            | 2               |
+-----------+-----------+--------------+-----------------+
Explanation:
For 2020-12-8, toyota gets leads = [0, 1] and partners = [0, 1, 2] while honda gets leads = [1, 2] and partners = [1, 2].
For 2020-12-7, toyota gets leads = [0] and partners = [1, 2] while honda gets leads = [0, 1, 2] and partners = [1, 2].

✏️문제 풀이

SELECT

SELECT date_id, make_name, 
		COUNT(DISTINCT lead_id) AS unique_leads, 
		COUNT(DISTINCT partner_id) AS unique_partners

• date_id : 날짜
• make_name : 자동차 이름
• unique_leads : 고유한 리드 수
  • DISTINCT lead_id : lead_id 의 중복을 제거
  • COUNT(...) : 고유한 lead의 개수를 카운팅
• unique_partners : 고유한 파트너 수
  • DISTINCT partner_id : partner_id 의 중복을 제거
  • COUNT(...) : 고유한 partner의 개수를 카운팅

FROM

FROM DailySales

• DailySales 테이블의 데이터를 가져옴

GROUP BY

GROUP BY 1, 2

• date_id와 make_name 조합에 대해 통계를 내야함
  • 1번 컬럼(date_id )과 2번 컬럼(make_name)을 기준으로 그룹화

💯제출 코드

SELECT date_id, make_name, 
		COUNT(DISTINCT lead_id) AS unique_leads, 
		COUNT(DISTINCT partner_id) AS unique_partners
FROM DailySales
GROUP BY 1, 2