Given a table of therapist availability and patient searches, write a query to calculate the weekly conversion rate of searches to booked appointments. Count only searches made for a therapist marked available on the search date. A search is converted when it has an appointment with status booked.
week_start, search_count, booked_search_count, and conversion_rate.conversion_rate as a percentage rounded to two decimal places, ordered by week_start ascending.| Column | Type | Description |
|---|---|---|
| availability_idPK | INT | Unique availability record identifier |
| therapist_id | INT | Therapist offering availability |
| available_date | DATE | Date on which the therapist is available |
| Column | Type | Description |
|---|---|---|
| search_idPK | INT | Unique patient search identifier |
| patient_id | INT | Patient who performed the search |
| therapist_id | INT | Therapist viewed or selected by the patient |
| searched_at | TIMESTAMP | Timestamp when the search occurred |
| Column | Type | Description |
|---|---|---|
| appointment_idPK | INT | Unique appointment identifier |
| search_id | INT | Search that led to the appointment |
| appointment_status | VARCHAR(20) | Appointment outcome, such as booked or cancelled |
| appointment_date | DATE | Scheduled appointment date |