Expedition Recruit Source Distribution

An outdoor research organization records periodic roster snapshots for its expeditions. A person's role and recruitment source may change over time, so only the latest observation for each expedition-person pair should determine the current roster.

Table: expedition_roster

+--------------------+--------------+
| Column Name        | Type         |
+--------------------+--------------+
| expedition_id      | integer      |
| person_id          | integer      |
| observed_on        | date         |
| is_leader          | integer      |
| recruitment_source | varchar(255) |
+--------------------+--------------+
+--------------------+--------------+
| Column Name        | Type         |
+--------------------+--------------+
| expedition_id      | integer      |
| person_id          | integer      |
| observed_on        | date         |
| is_leader          | integer      |
| recruitment_source | varchar(255) |
+--------------------+--------------+

The combination of expedition_id, person_id, and observed_on is the primary key. Each row is one date-level roster observation. is_leader is either 1 for a leader or 0 for a recruit. Therefore, a person has at most one observation per expedition on a given date.

For each expedition_id and person_id pair, keep only the row with the latest observed_on. From those latest rows, include only recruits whose is_leader value is 0.

Group the recruits by recruitment_source. Replace a NULL source with Unknown; a literal source value of Unknown belongs to the same group.

Return:

• recruitment_source
• recruit_count
• recruit_pct, the group's percentage of all included recruits, rounded to 2 decimal places on a 0-to-100 scale

Order the results by recruit_count descending, then recruitment_source ascending. If there are no recruits after keeping the latest observations, return no rows.

The same person may appear in multiple expeditions; each distinct expedition-person pair counts separately.

Example

Input:

expedition_roster table:
+---------------+-----------+-------------+-----------+--------------------+
| expedition_id | person_id | observed_on | is_leader | recruitment_source |
+---------------+-----------+-------------+-----------+--------------------+
| 1             | 10        | 2026-01-01  | 1         | NULL               |
| 1             | 20        | 2026-01-01  | 0         | referral           |
| 2             | 30        | 2026-01-02  | 0         | website            |
| 3             | 40        | 2026-01-03  | 0         | referral           |
+---------------+-----------+-------------+-----------+--------------------+
expedition_roster table:
+---------------+-----------+-------------+-----------+--------------------+
| expedition_id | person_id | observed_on | is_leader | recruitment_source |
+---------------+-----------+-------------+-----------+--------------------+
| 1             | 10        | 2026-01-01  | 1         | NULL               |
| 1             | 20        | 2026-01-01  | 0         | referral           |
| 2             | 30        | 2026-01-02  | 0         | website            |
| 3             | 40        | 2026-01-03  | 0         | referral           |
+---------------+-----------+-------------+-----------+--------------------+

Output:

+--------------------+---------------+-------------+
| recruitment_source | recruit_count | recruit_pct |
+--------------------+---------------+-------------+
| referral           | 2             | 66.67       |
| website            | 1             | 33.33       |
+--------------------+---------------+-------------+
+--------------------+---------------+-------------+
| recruitment_source | recruit_count | recruit_pct |
+--------------------+---------------+-------------+
| referral           | 2             | 66.67       |
| website            | 1             | 33.33       |
+--------------------+---------------+-------------+

Explanation:

The latest roster contains two recruits from referrals and one from the website. Their shares are 2 / 3 = 66.67% and 1 / 3 = 33.33%.