Daily Brand Lead-Partner Reach Summary

EASY10 pts

A sales analytics team stores deal interactions in the table:

daily_sales(date_id, make_name, lead_id, partner_id)

Column details:

date_id: the calendar date for the sale event
make_name: product brand identifier
lead_id: lead identifier associated with the event
partner_id: partner identifier associated with the event

Important data behavior:

The table can contain duplicate rows.
The same lead or partner can appear multiple times in a day for the same brand.

Task: For every (date_id, make_name) pair present in the table, compute:

unique_leads: number of distinct lead_id values
unique_partners: number of distinct partner_id values

Return exactly these columns:

date_id
make_name
unique_leads
unique_partners

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

daily_sales:
| date_id    | make_name | lead_id | partner_id |
|------------|-----------|---------|------------|
| 2024-10-08 | toyota    | 0       | 1          |
| 2024-10-08 | toyota    | 1       | 0          |
| 2024-10-08 | toyota    | 1       | 2          |
| 2024-10-07 | toyota    | 0       | 2          |
| 2024-10-07 | toyota    | 0       | 1          |
| 2024-10-08 | honda     | 1       | 2          |
| 2024-10-08 | honda     | 2       | 1          |
| 2024-10-07 | honda     | 0       | 1          |
| 2024-10-07 | honda     | 1       | 2          |
| 2024-10-07 | honda     | 2       | 1          |

Output

[
  {"date_id":"2024-10-07","make_name":"honda","unique_leads":3,"unique_partners":2},
  {"date_id":"2024-10-07","make_name":"toyota","unique_leads":1,"unique_partners":2},
  {"date_id":"2024-10-08","make_name":"honda","unique_leads":2,"unique_partners":2},
  {"date_id":"2024-10-08","make_name":"toyota","unique_leads":2,"unique_partners":3}
]

Explanation

Counts are computed independently for lead_id and partner_id inside each date-brand group.

Example

Input

daily_sales:
| date_id    | make_name | lead_id | partner_id |
|------------|-----------|---------|------------|
| 2025-01-05 | ford      | 10      | 20         |
| 2025-01-05 | ford      | 10      | 20         |
| 2025-01-05 | ford      | 10      | 21         |
| 2025-01-05 | ford      | 11      | 21         |

Output

[
  {"date_id":"2025-01-05","make_name":"ford","unique_leads":2,"unique_partners":2}
]

Explanation

Exact duplicate rows do not increase distinct counts.

Example

Input

daily_sales:
| date_id    | make_name | lead_id | partner_id |
|------------|-----------|---------|------------|
| 2025-03-01 | mazda     | 5       | 100        |
| 2025-03-01 | mazda     | 5       | 101        |
| 2025-03-01 | mazda     | 5       | 102        |
| 2025-03-02 | mazda     | 6       | 102        |
| 2025-03-02 | mazda     | 7       | 102        |
| 2025-03-02 | mazda     | 8       | 102        |

Output

[
  {"date_id":"2025-03-01","make_name":"mazda","unique_leads":1,"unique_partners":3},
  {"date_id":"2025-03-02","make_name":"mazda","unique_leads":3,"unique_partners":1}
]

Explanation

Distinct leads and distinct partners can evolve very differently across groups.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in daily_sales <= 300000
date_id is a valid date string (YYYY-MM-DD)
make_name contains lowercase English letters
0 <= lead_id <= 1,000,000,000
0 <= partner_id <= 1,000,000,000
Duplicate rows may exist
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with exact output columns

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

daily_sales =

[{"date_id":"2024-10-08","make_name":"toyota","lead_id":0,"partner_id":1},{"date_id":"2024-10-08","make_name":"toyota","lead_id":1,"partner_id":0},{"date_id":"2024-10-08","make_name":"toyota","lead_id":1,"partner_id":2},{"date_id":"2024-10-07","make_name":"toyota","lead_id":0,"partner_id":2},{"date_id":"2024-10-07","make_name":"toyota","lead_id":0,"partner_id":1},{"date_id":"2024-10-08","make_name":"honda","lead_id":1,"partner_id":2},{"date_id":"2024-10-08","make_name":"honda","lead_id":2,"partner_id":1},{"date_id":"2024-10-07","make_name":"honda","lead_id":0,"partner_id":1},{"date_id":"2024-10-07","make_name":"honda","lead_id":1,"partner_id":2},{"date_id":"2024-10-07","make_name":"honda","lead_id":2,"partner_id":1}]

Daily Brand Lead-Partner Reach Summary

Hints

Daily Brand Lead-Partner Reach Summary

Hints