Google: Drive Files Shared Outside the Owner's Domain

Medium
682.

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 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

ColumnType
file_idint
owner_emailvarchar
titlevarchar

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

ColumnTypeValues
share_idint
file_idint
shared_withvarchar
roleenumviewer, commenter, editor

Primary key: share_id.

One grant of access to one address; a file is shared with an address at most once.

Example

The rows must come back in the order shown.

DriveFile

file_idowner_emailtitle
1asha@zentrix.ioQ3 roadmap
2ravi@zentrix.ioPayroll July
3meera@quillpad.inLaunch plan
4kabir@quillpad.inTeam offsite
5zara@ledgerly.comAudit notes

FileShare

share_idfile_idshared_withrole
11ravi@zentrix.ioeditor
21partner@agency.comviewer
32ca@taxfirm.incommenter
42partner@agency.comviewer
53dev@mail.quillpad.ineditor
64meera@quillpad.ineditor
75auditor@kpmg.exampleviewer

Output

owner_domainexternal_filesexternal_people
zentrix.io22
ledgerly.com11
quillpad.in11
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
file_idint
owner_emailvarchar
titlevarchar

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

ColumnType
share_idint
file_idint
shared_withvarchar
roleenum(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_idowner_emailtitle
1asha@zentrix.ioQ3 roadmap
2ravi@zentrix.ioPayroll July
3meera@quillpad.inLaunch plan
4kabir@quillpad.inTeam offsite
5zara@ledgerly.comAudit notes

FileShare

share_idfile_idshared_withrole
11ravi@zentrix.ioeditor
21partner@agency.comviewer
32ca@taxfirm.incommenter
42partner@agency.comviewer
53dev@mail.quillpad.ineditor
64meera@quillpad.ineditor
75auditor@kpmg.exampleviewer

Output

owner_domainexternal_filesexternal_people
zentrix.io22
ledgerly.com11
quillpad.in11

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_domain

Another 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 →