Write a SQL query to analyze the B Lab Global certification pipeline by joining application, review, and outcome tables and calculating throughput by region.
Use applications submitted during 2024. Throughput is the percentage of applications with a Certified outcome, and applications without reviews or outcomes must remain included.
region, application_count, reviewed_application_count, certified_count, and throughput_pctthroughput_pct descending, then region ascending| Column | Type | Description |
|---|---|---|
| application_idPK | INT | Unique certification application identifier |
| organization_name | VARCHAR(150) | Applicant organization name |
| region | VARCHAR(60) | Organization region |
| submitted_at | DATE | Date the application was submitted |
| Column | Type | Description |
|---|---|---|
| review_idPK | INT | Unique review identifier |
| application_id | INT | Application being reviewed |
| review_status | VARCHAR(40) | Current review status |
| completed_at | DATE | Date the review was completed |
| Column | Type | Description |
|---|---|---|
| outcome_idPK | INT | Unique outcome identifier |
| application_id | INT | Application receiving the outcome |
| outcome_status | VARCHAR(40) | Certification outcome status |
| outcome_date | DATE | Date the outcome was recorded |