Daily Leads And Partners Problem
Description
LeetCode Problem 1693.
Table: DailySales
1
2
3
4
5
6
7
8
9
10
11
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| date_id | date |
| make_name | varchar |
| lead_id | int |
| partner_id | int |
+-------------+---------+
This table does not have a primary key.
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.
Write an SQL query that will, for each date_id and make_name, return the number of distinct lead_id’s and distinct partner_id’s.
Return the result table in any order.
The query result format is in the following example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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 |
+-----------+-----------+---------+------------+
Result table:
+-----------+-----------+--------------+-----------------+
| 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 |
+-----------+-----------+--------------+-----------------+
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].
MySQL Solution
1
2
3
4
5
select date_id, make_name,
count(distinct lead_id) as unique_leads,
count(distinct partner_id) as unique_partners
from DailySales
group by date_id, make_name
LeetCode Database - Easy
LeetCode 1407
LeetCode 1435
LeetCode 1484
LeetCode 1495
LeetCode 1511
LeetCode 1517
LeetCode 1527
LeetCode 1543
LeetCode 1565
LeetCode 1571
LeetCode 1581
LeetCode 1587
LeetCode 1607
LeetCode 1623
LeetCode 1633
LeetCode 1661
LeetCode 1667
LeetCode 1677
LeetCode 1683
LeetCode 1693
LeetCode 1729
LeetCode 1731
LeetCode 1741
LeetCode 1757
LeetCode 1777
LeetCode 1789
LeetCode 1795
LeetCode 1809
More LeetCode Database
MySQL Tutorials