I have 2 questions which I was unable to answer in an interview, and hence do not have the answer.
2 questions are:
a. Write a query to get close rate by make and partner name (close rate = sales count / lead count)
Attempt:
select (totalsales/totalleads) as close_rate
(select count(sale_id) as totalsales from daily_sales group by make_name) S
outer join
(select count(lead_id) as totalleads from daily_leads group by make_name) L
on S.make_name=L.make_name
outer join
(select partner_name from partners) P
on L.partner_id=P.partnerid
b. Write a query to Aggregate data to get daily leads and sales count by make and date
Attempt:
select count(sale_id), count(lead_id)
from daily_leads dl
full outer join
on daily_sales ds
on dl.lead_id=ds.lead_id
group by make_name, date
TABLES:
Daily_Leads
+---------------------------------------------------+
| lead_id partner_id date_id make_name |
+---------------------------------------------------+
| 4 2 2019-01-01 toyota |
| 6 2 2019-01-01 honda |
| 8 2 2019-01-02 toyota |
| 9 70 2019-01-01 honda |
| 2 70 2019-01-01 ford |
| 7 1 2019-01-01 bmw |
| 10 4 2019-01-01 bmw |
| 25 1 2019-01-02 ford |
| |
+---------------------------------------------------+
Table Name: daily_sales
+-----------------------------------------------------------------+
| lead_id partner_id date_id make_name Sale_id |
+-----------------------------------------------------------------+
| 4 2 2019-01-01 toyota 1 |
| 6 2 2019-01-01 honda 2 |
| 8 2 2019-01-02 toyota 3 |
| 9 70 2019-01-02 honda 4 |
| 10 4 2019-01-01 bmw 5 |
+-----------------------------------------------------------------+
Table name partners
+----------------------+
| id partner_name |
+----------------------+
| 1 True |
| 2 App |
| 4 Way |
| 70 Fbook |
+----------------------+I could not solve these. If someone could help me out. I do not have the expected output, or any database preference.
Was also asked question on how to remove duplicates, and about SCD Type 2.