Google: Drive Files Shared Outside the Owner's Domain
A Google Workspace security admin reviews data leaving each company: a Drive file is shared externally when at least one address it is shared with has a…
- Difficulty: Medium
- Topics: Strings, Joins, Aggregation
- Dialect: MySQL
- Problem: #682
Problem statement
A Google Workspace security admin reviews data leaving each company: a Drive file is shared externally when at least one address it is shared with has a different email domain (the text after @) from its owner's.
For every owner domain with at least one externally shared file, return owner_domain, external_files (its distinct files shared externally) and external_people (the distinct outside addresses that can open any of those files). Domains must match exactly — mail.zentrix.io is not zentrix.io. Order by external_files descending, then owner_domain ascending.
A practice scenario set at Google: the tables and data are invented, and this is not a question from, or endorsed by, Google.
Tables
Table: DriveFile
| Column | Type |
|---|---|
| file_id | int |
| owner_email | varchar |
| title | varchar |
Primary key: file_id.
One Drive file and the account that owns it. Every address in the data is lower case with exactly one @.
Table: FileShare
| Column | Type |
|---|---|
| share_id | int |
| file_id | int |
| shared_with | varchar |
| role | enum(viewer, commenter, editor) |
Primary key: share_id.
One grant of access to one address; a file is shared with an address at most once.
Examples
Example 1
DriveFile
| file_id | owner_email | title |
|---|---|---|
| 1 | asha@zentrix.io | Q3 roadmap |
| 2 | ravi@zentrix.io | Payroll July |
| 3 | meera@quillpad.in | Launch plan |
| 4 | kabir@quillpad.in | Team offsite |
| 5 | zara@ledgerly.com | Audit notes |
FileShare
| share_id | file_id | shared_with | role |
|---|---|---|---|
| 1 | 1 | ravi@zentrix.io | editor |
| 2 | 1 | partner@agency.com | viewer |
| 3 | 2 | ca@taxfirm.in | commenter |
| 4 | 2 | partner@agency.com | viewer |
| 5 | 3 | dev@mail.quillpad.in | editor |
| 6 | 4 | meera@quillpad.in | editor |
| 7 | 5 | auditor@kpmg.example | viewer |
Output
| owner_domain | external_files | external_people |
|---|---|---|
| zentrix.io | 2 | 2 |
| ledgerly.com | 1 | 1 |
| quillpad.in | 1 | 1 |
How to solve Google: Drive Files Shared Outside the Owner's Domain
Both sides of the comparison are derived from strings: the owner's domain and the recipient's domain are the text after the @, which SUBSTRING_INDEX(email, '@', -1) returns (a negative count takes the pieces from the right). Joining files to their shares and keeping the rows where the two domains differ leaves exactly the external grants. Comparing whole domains for equality is what makes dev@mail.quillpad.in external to quillpad.in; a test like LIKE '%quillpad.in' would wrongly treat the subdomain as internal.
Grouped by the owner's domain, those rows give the two figures — but both need COUNT(DISTINCT …): a file with two outside recipients appears twice, and an agency address with access to two files appears twice. Files shared only inside the company have no external rows and do not count; a domain with none drops out.
The alternative cuts the domain with RIGHT/LOCATE and tests external with NOT LIKE CONCAT('%@', owner_domain) — the @ in the pattern is what keeps subdomains external. One join and one grouped pass.
Reference solution (MySQL)
SELECT SUBSTRING_INDEX(f.owner_email, '@', -1) AS owner_domain,
COUNT(DISTINCT f.file_id) AS external_files,
COUNT(DISTINCT s.shared_with) AS external_people
FROM DriveFile f
JOIN FileShare s ON s.file_id = f.file_id
WHERE SUBSTRING_INDEX(s.shared_with, '@', -1) <> SUBSTRING_INDEX(f.owner_email, '@', -1)
GROUP BY owner_domain
ORDER BY external_files DESC, owner_domainAnother way
SELECT owner_domain, COUNT(DISTINCT file_id) AS external_files, COUNT(DISTINCT shared_with) AS external_people FROM (SELECT f.file_id, RIGHT(f.owner_email, CHAR_LENGTH(f.owner_email) - LOCATE('@', f.owner_email)) AS owner_domain, s.shared_with FROM DriveFile f JOIN FileShare s ON s.file_id = f.file_id) x WHERE shared_with NOT LIKE CONCAT('%@', owner_domain) GROUP BY owner_domain ORDER BY external_files DESC, owner_domain← Google: Click-Through Rate at Every Organic Position · Google: Local Guides Whose Ratings Turned Harsher →