Top Company Domains from Webinar RegistrationsCompany: Zoom | Difficulty: medium

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 <renata.silva@brex.com> | source=webinar_ad'. Some rows are malformed and contain no valid email address at all.

Write a query that:

Extracts the email address from raw_registration_data (rows with no valid <...@...> email pattern should be excluded entirely).
Extracts the domain portion of that email (everything after the @), normalized to lowercase.
Excludes registrations from generic public email providers: gmail.com, yahoo.com, hotmail.com, and outlook.com.
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 renata.silva@brex.com \| source=webinar_ad
2	Kofi Mensah kofi.mensah@brex.com \| source=organic
3	Devon Ashworth devon.ashworth@gmail.com \| 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.

SchemaDROP TABLE IF EXISTS webinar_registrations; CREATE TABLE webinar_registrations (registration_id INT, raw_registration_data TEXT); INSERT INTO webinar_registrations VALUES (1, 'Renata Silva <renata.silva@brex.com> | source=webinar_ad'), (2, 'Kofi Mensah <kofi.mensah@brex.com> | source=organic'), (3, 'Devon Ashworth <devon.ashworth@gmail.com> | source=webinar_ad'), (4, 'Corrupted entry, no email captured | source=unknown');← All SQL Challenges · Zoom Questions · Learn SQL Free