Daily Brand Lead-Partner Reach Summary

Easy

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 1

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 2

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 3

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.

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

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 |

[ {"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} ]

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 |

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 |