SQL Renewal Completion Rates | Dataford Interview Questions

How would you write a SQL query to calculate monthly renewal completion rates by office and account executive at Lockton Companies?

Use the renewal due date to determine the month. A renewal is completed when completed_date is not null.

Output

One row per office, account executive, and renewal month.
Include office_name, account_executive_name, renewal_month, total_renewals, completed_renewals, and completion_rate_pct.
Include only records with matching office and account executive details. Sort by office, account executive, and month ascending.

offices

Column	Type	Description
office_idPK	INT	Unique office identifier
office_name	VARCHAR(100)	Lockton office name
region	VARCHAR(50)	Geographic region for the office

account_executives

Column	Type	Description
account_executive_idPK	INT	Unique account executive identifier
full_name	VARCHAR(120)	Account executive full name
office_id	INT	Assigned office identifier

renewals

Column	Type	Description
renewal_idPK	INT	Unique renewal identifier
client_name	VARCHAR(150)	Client associated with the renewal
office_id	INT	Office responsible for the renewal
account_executive_id	INT	Account executive responsible for the renewal
renewal_due_date	DATE	Date the renewal is due
completed_date	DATE	Date the renewal was completed

Your question is SQL Renewal Completion Rates. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.