Daily Leads and Partners - Problem

Given a table DailySales that contains sales data with columns for date_id, make_name, lead_id, and partner_id.

For each unique combination of date_id and make_name, find:

  • The number of distinct lead_id's
  • The number of distinct partner_id's

The table may contain duplicate rows, so you need to count only unique leads and partners for each date and product combination.

Return the result table in any order.

Table Schema

DailySales
Column Name Type Description
date_id date Date of the sales transaction
make_name varchar Name of the product sold (lowercase English letters only)
lead_id int ID of the lead associated with the sale
partner_id int ID of the partner associated with the sale
Note: This table has no primary key and may contain duplicate rows

Input & Output

Example 1 — Multiple Sales Records
Input 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 honda 1 2
Output:
date_id make_name unique_leads unique_partners
2020-12-8 toyota 2 3
2020-12-7 toyota 1 1
2020-12-7 honda 1 1
💡 Note:

For 2020-12-8, toyota: distinct leads are [0, 1] (2 unique) and distinct partners are [1, 0, 2] (3 unique). For 2020-12-7, toyota: only lead 0 and partner 2 appear. For 2020-12-7, honda: only lead 1 and partner 2 appear.
Example 2 — Duplicate Records
Input Table:
date_id make_name lead_id partner_id
2020-12-8 nissan 0 1
2020-12-8 nissan 0 1
2020-12-8 nissan 0 1
Output:
date_id make_name unique_leads unique_partners
2020-12-8 nissan 1 1
💡 Note:

Despite having 3 identical rows, there is only 1 distinct lead (ID 0) and 1 distinct partner (ID 1) for the 2020-12-8, nissan combination. COUNT DISTINCT eliminates duplicates automatically.

Constraints

  • date_id is a valid date
  • make_name consists of only lowercase English letters
  • lead_id and partner_id are non-negative integers
  • The table may contain duplicate rows

Visualization

Tap to expand
Daily Leads and Partners Problem OverviewInput: Sales Datadate_idmake_namelead_idpartner_id2020-12-8toyota012020-12-8toyota102020-12-8toyota122020-12-7honda12GROUP BYCOUNT DISTINCTOutput: Summary Reportdate_idmake_nameunique_leadsunique_partners2020-12-8toyota232020-12-7honda11Duplicates are automatically eliminated by COUNT DISTINCT
Understanding the Visualization
1
Input
Sales table with possible duplicates
2
Group By
GROUP BY date_id and make_name
3
Count Distinct
Count unique leads and partners per group
Key Takeaway
🎯 Key Insight: Use GROUP BY with COUNT DISTINCT to create summary reports from detailed transaction data

Related Problems

Asked in
Amazon 12 Facebook 8 Microsoft 6
23.4K Views
Medium Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen