Generate a company-wise interview conversion funnel - Flipkart Online Assessment Data Science Role

Problem Preview

"You are given a university placement database consisting of the following tables: departments, students, courses, enrollments, companies, interviews, offers. Write an SQL query to generate a company-wise interview conversion funnel. For every company, display the following columns: company_name, unique_students_interviewed, unique_students_reached_hr_round, offers_made, offers_accepted, hr_reach_rate, offer_accept_rate. Sort the result by offers_accepted in descending order, offers_made in descending order, offer_accept_rate in descending order, company_name in ascending order, company_id in ascending order. Where: * **unique_students_interviewed** is the count of distinct students who attended at least one interview for that company. * **unique_students_reached_hr_round** is the count of distinct students who attended at least one interview with round_type = 'HR' for that company. * **offers_made** is the total number of offers linked to interviews conducted by that company. * **offers_accepted** is the total number of offers where offer_status = 'ACCEPTED'. * **hr_reach_rate** is calculated as: (unique_students_reached_hr_round / unique_students_interviewed) × 100 (Round hr_reach_rate to 2 decimal places. Return 0 if no students were interviewed.) * **offer_accept_rate** is calculated as: (offers_accepted / offers_made) × 100 (Round offer_accept_rate to 2 decimal places. Return 0 if no offers were made.) **Note:** * Count each student only once per company in unique_students_interviewed, even if the student attended multiple interview rounds for that company. * Count each student only once per company in unique_students_reached_hr_round, even if the student attended multiple HR interview rounds for that company. * Count all offers linked to the company's interviews in offers_made. * Count only offers with offer_status = 'ACCEPTED' in offers_accepted. * Include all companies, even if they have no interviews or no offers."