1693. Daily Leads and Partners
Model: qwen2.5-coder:14b
Topics:
FAANG asked: 0
Acceptance rate: 90.7%
Problem Breakdown
The problem requires us to determine the number of unique daily leads and partners from given tables. We are provided with two tables: DailySales and Partners. The DailySales table contains records of sales made each day, including the lead ID and partner ID. The Partners table contains information about the partners, including their IDs and names.
Inputs:
DailySales: A table with columns[sale_id, date, lead_id, partner_id].Partners: A table with columns[partner_id, name].
Outputs:
- The number of unique daily leads.
- The number of unique partners.
Implicit Constraints or Tricky Edge Cases:
- Duplicate entries in the
DailySalestable should be considered only once. - If there are no records in either table, the output should be zero for both leads and partners.
- Negative numbers or invalid IDs should not affect the count.
Intuition
The brute-force approach would involve iterating through each record in the DailySales table and checking if the lead ID and partner ID have been encountered before. This approach would result in a time complexity of O(n^2) due to nested loops for uniqueness checks, which is inefficient for large datasets.
A better approach involves using SQL’s set operations to efficiently count unique leads and partners. By leveraging DISTINCT and JOIN, we can achieve this with linear time complexity relative to the size of the input tables.
Approach
-
Count Unique Leads:
- Use a
SELECT DISTINCTquery on thelead_idcolumn from theDailySalestable. - Count the number of distinct lead IDs.
- Use a
-
Count Unique Partners:
- Join the
Partnerstable with theDailySalestable on thepartner_idcolumn. - Use a
SELECT DISTINCTquery on thenamecolumn from thePartnerstable to ensure uniqueness by name. - Count the number of distinct partner names.
- Join the
-
Return Results:
- Return the counts of unique leads and partners as a result set.
Complexity Analysis
-
Time Complexity: O(n + m), where n is the number of records in
DailySalesand m is the number of records inPartners. The join operation and distinct operations are linear with respect to the size of the input tables. -
Space Complexity: O(1) for the query execution, as we are only storing intermediate results and not additional data structures.
Solution
import pandas as pd
def count_unique_leads_and_partners(daily_sales: pd.DataFrame, partners: pd.DataFrame) -> pd.DataFrame:
# Count unique leads
unique_leads = daily_sales['lead_id'].nunique()
# Join DailySales with Partners on partner_id and count unique partner names
merged_df = daily_sales.merge(partners, on='partner_id', how='left')
unique_partners = merged_df['name'].nunique()
# Create result DataFrame
result = pd.DataFrame({
'unique_leads': [unique_leads],
'unique_partners': [unique_partners]
})
return result
Edge Cases Handled
- Empty Tables: If either
DailySalesorPartnersis empty, the respective count will be zero. - Duplicate IDs: The use of
nunique()ensures that duplicate IDs are counted only once. - Invalid IDs: Any invalid or negative IDs in
DailySalesthat do not exist inPartnerswill not affect the partner count.