Daily Leads and Partners - Problem

You're working as a data analyst for a car dealership chain, and you need to analyze their daily sales performance across different car brands and locations.

You have a DailySales table that tracks every sale made:

Column NameType
date_iddate
make_namevarchar
lead_idint
partner_idint

Note: This table has no primary key and may contain duplicate rows. Each row represents a sale transaction with the date, car brand (make_name), the lead who brought the customer (lead_id), and the dealership partner who closed the sale (partner_id).

Your Task: For each unique combination of date_id and make_name, calculate:

  • The number of distinct leads who brought customers
  • The number of distinct partners who made sales

This analysis will help the company understand which brands are performing well and how many unique salespeople are involved in each brand's daily sales.

Example: If on 2020-12-08, Toyota had sales involving leads [1,1,2] and partners [3,4,4], the result should show 2 unique leads and 2 unique partners for Toyota on that date.

Input & Output

example_1.sql โ€” Basic Example
$ 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 |
โ€บ Output: | date_id | make_name | unique_leads | unique_partners | |------------|-----------|--------------|----------------| | 2020-12-7 | toyota | 1 | 2 | | 2020-12-8 | honda | 2 | 2 | | 2020-12-8 | toyota | 2 | 3 |
๐Ÿ’ก Note: For 2020-12-7 toyota: lead_id 0 appeared multiple times (count distinct = 1), partner_ids were 1,2 (count distinct = 2). For 2020-12-8 honda: lead_ids were 1,2 (count distinct = 2), partner_ids were 1,2 (count distinct = 2). For 2020-12-8 toyota: lead_ids were 0,1 (count distinct = 2), partner_ids were 0,1,2 (count distinct = 3).
example_2.sql โ€” Single Brand Day
$ Input: DailySales table: | date_id | make_name | lead_id | partner_id | |------------|-----------|---------|------------| | 2020-12-9 | bmw | 5 | 7 | | 2020-12-9 | bmw | 5 | 7 | | 2020-12-9 | bmw | 5 | 8 |
โ€บ Output: | date_id | make_name | unique_leads | unique_partners | |------------|-----------|--------------|----------------| | 2020-12-9 | bmw | 1 | 2 |
๐Ÿ’ก Note: Even though there are 3 rows, only 1 distinct lead (id=5) and 2 distinct partners (ids=7,8) were involved in BMW sales on 2020-12-9. Duplicate rows don't affect the distinct count.
example_3.sql โ€” Multiple Brands Same Day
$ Input: DailySales table: | date_id | make_name | lead_id | partner_id | |------------|-----------|---------|------------| | 2020-12-10 | ford | 1 | 3 | | 2020-12-10 | ford | 2 | 3 | | 2020-12-10 | nissan | 1 | 4 | | 2020-12-10 | nissan | 1 | 5 |
โ€บ Output: | date_id | make_name | unique_leads | unique_partners | |------------|-----------|--------------|----------------| | 2020-12-10 | ford | 2 | 1 | | 2020-12-10 | nissan | 1 | 2 |
๐Ÿ’ก Note: Each brand is counted separately. Ford had 2 distinct leads (1,2) but only 1 partner (3). Nissan had 1 distinct lead (1) but 2 distinct partners (4,5). The same lead_id can appear across different brands.

Constraints

  • 1 โ‰ค Number of rows โ‰ค 104
  • date_id is in format YYYY-MM-DD
  • make_name consists of lowercase English letters only
  • The table may contain duplicate rows
  • lead_id and partner_id are positive integers

Visualization

Tap to expand
๐ŸŽฏ Sales Performance Analytics DashboardRaw Sales Data โ†’ Smart Grouping โ†’ Distinct Counting โ†’ Analytics Report๐Ÿ“Š Raw Data2020-12-08 | toyota | 1 | 32020-12-08 | toyota | 1 | 42020-12-08 | toyota | 2 | 32020-12-07 | honda | 3 | 5Multiple records...๐Ÿ”„ GROUP BYGroup 1:2020-12-08, toyotaLeads: {1, 2}Partners: {3, 4}Group 2: ...๐Ÿงฎ COUNT DISTINCTFor each group:Count unique leadsCount unique partnersโœ“ Handles duplicatesโœ“ Memory efficient๐Ÿ“ˆ Final ReportDate | Brand | Leads | Partners2020-12-07 | honda | 1 | 12020-12-08 | toyota | 2 | 2Clean, actionable dataโœ“ Ready for analysis๐Ÿ’ก Why This Solution Works Perfectly:โ€ข Single Scan: Processes each record exactly onceโ€ข SQL Optimized: Database engines are built for GROUP BY + COUNT DISTINCTโ€ข Memory Smart: Uses efficient hash tables internallyโ€ข Business Ready: Scales to enterprise-level data๐Ÿ† Real-World Business Impact:๐Ÿ“Š Performance Tracking: Identify which brands need more sales support๐Ÿ‘ฅ Team Utilization: See how many unique salespeople work each brand daily๐Ÿ“ˆ Growth Analysis: Track sales team expansion and brand performance over time
Understanding the Visualization
1
Data Collection
Sales records flow in with date, brand, lead, and partner information
2
Smart Grouping
System automatically groups records by date and brand combination
3
Unique Counting
For each group, count how many different leads and partners were active
4
Analytics Output
Generate clean report showing performance metrics per brand per day
Key Takeaway
๐ŸŽฏ Key Insight: SQL's GROUP BY with COUNT DISTINCT is specifically designed for this type of aggregation problem - it's both the simplest and most efficient solution, leveraging decades of database optimization.
Asked in
Amazon 45 Microsoft 38 Google 32 Meta 28
24.3K Views
High Frequency
~12 min Avg. Time
856 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