Description
Write a SQL query to find daily count of unique leads and partners for each make_name. Return date_id, make_name, unique_leads, unique_partners.
Table:
daily_sales| Column | Type |
|---|---|
| date_id | TEXT |
| make_name | TEXT |
| lead_id | INT |
| partner_id | INT |
Examples
Input:
CREATE TABLE daily_sales (date_id TEXT, make_name TEXT, lead_id INT, partner_id INT); INSERT INTO daily_sales VALUES ('2024-01-01', 'Toyota', 1, 100), ('2024-01-01', 'Toyota', 2, 100);Output:
2024-01-01|Toyota|2|1Explanation:
Toyota has 2 unique leads (1, 2) but only 1 unique partner (100), so the output is 2|1.
Input:
CREATE TABLE daily_sales (date_id TEXT, make_name TEXT, lead_id INT, partner_id INT); INSERT INTO daily_sales VALUES ('2024-02-15', 'Ford', 101, 200), ('2024-02-15', 'Ford', 101, 201), ('2024-02-15', 'Ford', 102, 200), ('2024-02-16', 'Ford', 103, 202);Output:
2024-02-15|Ford|2|2
2024-02-16|Ford|1|1Explanation:
Shows duplicate lead_id 101 and partner_id 200 on same date - COUNT DISTINCT eliminates duplicates. Feb 15 has 2 unique leads (101,102) and 2 unique partners (200,201). Feb 16 has 1 unique lead and partner each.
Input:
CREATE TABLE daily_sales (date_id TEXT, make_name TEXT, lead_id INT, partner_id INT); INSERT INTO daily_sales VALUES ('2024-03-10', 'BMW', 500, 300), ('2024-03-10', 'BMW', 501, 300), ('2024-03-10', 'Mercedes', 502, 301), ('2024-03-10', 'Mercedes', 502, 302), ('2024-03-10', 'Mercedes', 503, 301);Output:
2024-03-10|BMW|2|1
2024-03-10|Mercedes|2|2Explanation:
Multiple makes on same date with different lead/partner ratios. BMW has 2 unique leads (500,501) but only 1 unique partner (300). Mercedes has 2 unique leads (502,503) and 2 unique partners (301,302) despite 3 total records.
Constraints
- •
Use COUNT DISTINCT