Zoom's Growth team logs webinar registrations as messy raw text strings pulled from a third-party form tool, and wants to know which company email domains are driving the most webinar sign-ups — a proxy for enterprise interest. Each row in `webinar_registrations` has a `raw_registration_data` string that embeds an email address somewhere inside angle brackets, e.g. `'Renata Silva | source=webinar_ad'`. Some rows are malformed and contain no valid email address at all. Write a query that: 1. Extracts the email address from `raw_registration_data` (rows with no valid `<...@...>` email pattern should be excluded entirely). 2. Extracts the domain portion of that email (everything after the `@`), normalized to lowercase. 3. Excludes registrations from generic public email providers: `gmail.com`, `yahoo.com`, `hotmail.com`, and `outlook.com`. 4. Counts the number of **distinct registrant email addresses** per remaining domain (a person registering twice with the same email should only count once). Output the `domain` and its distinct registrant count, ordered from most registrants to fewest. If two or more domains tie on registrant count, break the tie by sorting those domains in **descending** alphabetical order. ### Table Schema | Table | Column | Type | |---|---|---| | `webinar_registrations` | `registration_id` | integer | | `webinar_registrations` | `raw_registration_data` | string | ### Example Input: `webinar_registrations` | registration_id | raw_registration_data | |---|---| | 1 | Renata Silva \| source=webinar_ad | | 2 | Kofi Mensah \| source=organic | | 3 | Devon Ashworth \| source=webinar_ad | | 4 | Corrupted entry, no email captured \| source=unknown | ### Example Output Explanation Row 1 and row 2 both extract to the domain `brex.com` (from `renata.silva@brex.com` and `kofi.mensah@brex.com`), giving `brex.com` a count of 2 distinct registrants. Row 3 extracts to `gmail.com`, which is a generic public provider and gets excluded entirely. Row 4 has no valid email pattern at all and is excluded. The final output is just one row: `brex.com` with a count of 2.
DROP TABLE IF EXISTS webinar_registrations; CREATE TABLE webinar_registrations (registration_id INT, raw_registration_data TEXT); INSERT INTO webinar_registrations VALUES (1, 'Renata Silva | source=webinar_ad'), (2, 'Kofi Mensah | source=organic'), (3, 'Devon Ashworth | source=webinar_ad'), (4, 'Corrupted entry, no email captured | source=unknown');