Average Attendees per Workshop

An events platform organizes workshops for internal teams and the public. It stores every workshop separately from the attendee check-ins recorded at the door.

Table: workshops

+---------------+--------------+
| Column Name   | Type         |
+---------------+--------------+
| workshop_id   | integer      |
| room_id       | integer      |
| audience_type | varchar(255) |
| workshop_date | date         |
+---------------+--------------+
+---------------+--------------+
| Column Name   | Type         |
+---------------+--------------+
| workshop_id   | integer      |
| room_id       | integer      |
| audience_type | varchar(255) |
| workshop_date | date         |
+---------------+--------------+

workshop_id is the primary key. audience_type may contain internal, public, or another value.

Table: workshop_checkins

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| checkin_id  | integer |
| workshop_id | integer |
| attendee_id | integer |
+-------------+---------+
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| checkin_id  | integer |
| workshop_id | integer |
| attendee_id | integer |
+-------------+---------+

checkin_id is the primary key. workshop_id references workshops.workshop_id. Each row represents one attendee check-in.

Consider workshops dated from 2026-09-07 through 2026-09-13, inclusive, whose audience_type is either internal or public.

First count the check-ins for each qualifying workshop. A workshop with no matching rows in workshop_checkins must have a count of 0. Then calculate the average check-in count per workshop for each audience_type.

Return audience_type and avg_attendees, rounded to 2 decimal places. Order the results by audience_type ascending. Do not return an audience type that has no qualifying workshops.

Example

Input:

workshops table:
+-------------+---------+---------------+---------------+
| workshop_id | room_id | audience_type | workshop_date |
+-------------+---------+---------------+---------------+
| 1           | 10      | internal      | 2026-09-10    |
| 2           | 11      | internal      | 2026-09-09    |
| 3           | 12      | internal      | 2026-09-08    |
| 4           | 13      | public        | 2026-09-11    |
| 5           | 14      | public        | 2026-09-12    |
+-------------+---------+---------------+---------------+
workshops table:
+-------------+---------+---------------+---------------+
| workshop_id | room_id | audience_type | workshop_date |
+-------------+---------+---------------+---------------+
| 1           | 10      | internal      | 2026-09-10    |
| 2           | 11      | internal      | 2026-09-09    |
| 3           | 12      | internal      | 2026-09-08    |
| 4           | 13      | public        | 2026-09-11    |
| 5           | 14      | public        | 2026-09-12    |
+-------------+---------+---------------+---------------+

workshop_checkins table:
+------------+-------------+-------------+
| checkin_id | workshop_id | attendee_id |
+------------+-------------+-------------+
| 1          | 1           | 101         |
| 2          | 1           | 102         |
| 3          | 2           | 103         |
| 4          | 3           | 104         |
| 5          | 3           | 105         |
| 6          | 3           | 106         |
| 7          | 4           | 107         |
| 8          | 4           | 108         |
| 9          | 4           | 109         |
| 10         | 4           | 110         |
+------------+-------------+-------------+
workshop_checkins table:
+------------+-------------+-------------+
| checkin_id | workshop_id | attendee_id |
+------------+-------------+-------------+
| 1          | 1           | 101         |
| 2          | 1           | 102         |
| 3          | 2           | 103         |
| 4          | 3           | 104         |
| 5          | 3           | 105         |
| 6          | 3           | 106         |
| 7          | 4           | 107         |
| 8          | 4           | 108         |
| 9          | 4           | 109         |
| 10         | 4           | 110         |
+------------+-------------+-------------+

Output:

+---------------+---------------+
| audience_type | avg_attendees |
+---------------+---------------+
| internal      | 2.00          |
| public        | 2.00          |
+---------------+---------------+
+---------------+---------------+
| audience_type | avg_attendees |
+---------------+---------------+
| internal      | 2.00          |
| public        | 2.00          |
+---------------+---------------+

Explanation:

The internal workshops have 2, 1, and 3 check-ins, for an average of 2.00. The public workshops have 4 and 0 check-ins, also averaging 2.00. Workshop 5 remains in the calculation because the left join preserves workshops without check-ins.