Daily Leads and Partners

Difficulty: Easy | Acceptance: 86.70% | Paid: No Topics: Database

Table: DailySales +-------------+-------+ | Column Name | Type | +-------------+-------+ | date_id | date | | lead_name | varchar | | partner_name| varchar | +-------------+-------+ There is no primary key for this table. It may contain duplicates. This table contains the date and the name of the lead and the partner that was contacted on that date.

Write an SQL query to return the name of the lead and the partner and the number of times they appeared in the daily_sales table. Return the result table in any order.

The result format is in the following example.

• Examples
• Constraints
• Approach 1: Hash Map Grouping
• Approach 2: Sorting and Iterating

Examples

Example 1:

Input: DailySales table: +-----------+-----------+--------------+ | date_id | lead_name | partner_name | +-----------+-----------+--------------+ | 2020-12-8 | gmail | google | | 2020-12-8 | gmail | facebook | | 2020-12-8 | outlook | google | | 2020-12-8 | outlook | google | | 2020-12-8 | gmail | google | +-----------+-----------+--------------+

Output: +-----------+--------------+-------+ | lead_name | partner_name | count | +-----------+--------------+-------+ | gmail | facebook | 1 | | gmail | google | 2 | | outlook | google | 2 | +-----------+--------------+-------+

Explanation:

• The pair (gmail, facebook) appeared once.
• The pair (gmail, google) appeared twice.
• The pair (outlook, google) appeared twice.
• The pair (outlook, facebook) never appeared.

Constraints

The DailySales table may contain duplicates.

Hash Map Grouping

Intuition The core logic of this problem is to count the frequency of specific pairs of strings (lead_name, partner_name). This is directly equivalent to the SQL GROUP BY clause. We can use a hash map (or dictionary) where the key is a combination of the lead and partner names, and the value is the running count.

Steps

• Initialize an empty hash map.
• Iterate through each row in the input data.
• Construct a unique key string by concatenating lead_name and partner_name (or use a tuple/object as the key).
• Increment the count for this key in the hash map.
• After processing all rows, iterate through the hash map and format the keys and values into the required output list format.

from collections import defaultdict
from typing import List

class Solution:
    def dailyLeadsAndPartners(self, daily_sales: List[List[str]]) -> List[List[str]]:
        counts = defaultdict(int)
        # Input format: [date_id, lead_name, partner_name]
        for row in daily_sales:
            lead = row[1]
            partner = row[2]
            # Use a tuple as the key
            counts[(lead, partner)] += 1
        
        result = []
        for (lead, partner), count in counts.items():
            result.append([lead, partner, str(count)])
        return result

Complexity

• Time: O(n), where n is the number of rows in the DailySales table. We iterate through the list once, and hash map operations are O(1) on average.
• Space: O(n), to store the unique pairs in the hash map. In the worst case, every row is a unique pair.
• Notes: This is the most efficient approach for unsorted data and is the standard algorithmic equivalent of SQL’s GROUP BY.

Sorting and Iterating

Intuition If we sort the data first by lead_name and then by partner_name, all identical pairs will be adjacent. We can then iterate through the sorted list and count consecutive duplicates. This avoids the overhead of a hash map.

Steps

• Sort the input list of rows based on lead_name and partner_name.
• Initialize an empty result list.
• Iterate through the sorted rows. If the current row is the same as the previous one, increment a counter. If it is different, append the previous pair and its count to the result, then reset the counter.
• Handle the last group after the loop finishes.

from typing import List

class Solution:
    def dailyLeadsAndPartners(self, daily_sales: List[List[str]]) -> List[List[str]]:
        if not daily_sales:
            return []
        
        # Sort by lead_name, then partner_name (indices 1 and 2)
        daily_sales.sort(key=lambda x: (x[1], x[2]))
        
        result = []
        count = 1
        prev_lead = daily_sales[0][1]
        prev_partner = daily_sales[0][2]
        
        for i in range(1, len(daily_sales)):
            curr_lead = daily_sales[i][1]
            curr_partner = daily_sales[i][2]
            
            if curr_lead == prev_lead and curr_partner == prev_partner:
                count += 1
            else:
                result.append([prev_lead, prev_partner, str(count)])
                prev_lead = curr_lead
                prev_partner = curr_partner
                count = 1
                
        # Append the last group
        result.append([prev_lead, prev_partner, str(count)])
        return result

Complexity

• Time: O(n log n), due to the sorting step. The iteration itself is O(n).
• Space: O(n) or O(log n) depending on the sorting algorithm’s space complexity (e.g., quicksort vs mergesort). The result list takes O(n) space.
• Notes: This approach is useful if memory is extremely constrained and the data is already sorted, or if hash map collisions are a concern. However, for general cases, the Hash Map approach is preferred.