Microsoft: OneDrive Files Whose Full Path Is Too Long to Sync

Hard
792.

Microsoft: OneDrive Files Whose Full Path Is Too Long to Sync

OneDrive's sync client cannot sync a file whose full path is too long. A OneDrive support engineer is building a report of the files that will fail, using a pilot limit of 60 characters.

Folders form a tree: a top-level folder has parent_id NULL. A file's full path is a /, then the names of every folder from the top level down to the file's folder, each followed by /, then the file name — for example /Documents/Projects/Plan.pptx. Return file_id, full_path, depth (the number of folders in the path) and path_length (characters in the full path) for every file whose path is longer than 60 characters. A path of exactly 60 syncs. Return the rows in any order.

A practice scenario set at Microsoft: the tables and data are invented, and this is not a question from, or endorsed by, Microsoft.

Tables

Table: OneDriveFolder

ColumnType
folder_idint
parent_idint
folder_namevarchar

Primary key: folder_id.

One row per folder; parent_id is the folder it sits in, or NULL at the top level. The tree never loops, and it can be any number of levels deep.

Table: OneDriveFile

ColumnType
file_idint
folder_idint
file_namevarchar

Primary key: file_id.

One row per file; every file sits in a folder.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

OneDriveFolder

folder_idparent_idfolder_name
1NULLDocuments
21Projects
32Falcon Launch
43Customer Interviews
5NULLPictures
65Holi 2025
71Archive

OneDriveFile

file_idfolder_idfile_name
104Retail Interview Notes.docx
113Plan.pptx
126Family Celebration in Jaipur.jpg
133Launch Budget FY25 v2.xlsx
143Launch Budget v3 Final.xlsx
152Quarterly Partner Review With Fabrikam.pptx

Output

file_idfull_pathdepthpath_length
10/Documents/Projects/Falcon Launch/Customer Interviews/Retail Interview Notes.docx481
14/Documents/Projects/Falcon Launch/Launch Budget v3 Final.xlsx361
15/Documents/Projects/Quarterly Partner Review With Fabrikam.pptx263
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.

Microsoft: OneDrive Files Whose Full Path Is Too Long to Sync

OneDrive's sync client cannot sync a file whose full path is too long.

  • Difficulty: Hard
  • Topics: Subqueries, Strings, Joins
  • Dialect: MySQL
  • Problem: #792

Problem statement

OneDrive's sync client cannot sync a file whose full path is too long. A OneDrive support engineer is building a report of the files that will fail, using a pilot limit of 60 characters.

Folders form a tree: a top-level folder has parent_id NULL. A file's full path is a /, then the names of every folder from the top level down to the file's folder, each followed by /, then the file name — for example /Documents/Projects/Plan.pptx. Return file_id, full_path, depth (the number of folders in the path) and path_length (characters in the full path) for every file whose path is longer than 60 characters. A path of exactly 60 syncs. Return the rows in any order.

A practice scenario set at Microsoft: the tables and data are invented, and this is not a question from, or endorsed by, Microsoft.

Tables

Table: OneDriveFolder

ColumnType
folder_idint
parent_idint
folder_namevarchar

Primary key: folder_id.

One row per folder; parent_id is the folder it sits in, or NULL at the top level. The tree never loops, and it can be any number of levels deep.

Table: OneDriveFile

ColumnType
file_idint
folder_idint
file_namevarchar

Primary key: file_id.

One row per file; every file sits in a folder.

Examples

Example 1

OneDriveFolder

folder_idparent_idfolder_name
1NULLDocuments
21Projects
32Falcon Launch
43Customer Interviews
5NULLPictures
65Holi 2025
71Archive

OneDriveFile

file_idfolder_idfile_name
104Retail Interview Notes.docx
113Plan.pptx
126Family Celebration in Jaipur.jpg
133Launch Budget FY25 v2.xlsx
143Launch Budget v3 Final.xlsx
152Quarterly Partner Review With Fabrikam.pptx

Output

file_idfull_pathdepthpath_length
10/Documents/Projects/Falcon Launch/Customer Interviews/Retail Interview Notes.docx481
14/Documents/Projects/Falcon Launch/Launch Budget v3 Final.xlsx361
15/Documents/Projects/Quarterly Partner Review With Fabrikam.pptx263

How to solve Microsoft: OneDrive Files Whose Full Path Is Too Long to Sync

A path is a breadcrumb: the names along the route from the root to the node, in order. The depth is not fixed, so a fixed chain of self-joins will always miss the deepest folders; a recursive CTE builds every folder's path one level at a time. The anchor takes the top-level folders, with path /Documents/; each step joins a folder to its parent's row and appends name/, so Falcon Launch becomes /Documents/Projects/Falcon Launch/ at depth 3. A file's full path is its folder's path followed by the file name.

One MySQL detail decides whether this works at all: a recursive CTE's column types come from the anchor member. If the anchor were just CONCAT('/', folder_name, '/'), the column would be as wide as the longest top-level name, and the deeper paths would be cut off, or the query would fail with "Data too long". Casting the anchor to CHAR(1000) gives the recursive rows room. The filter is strict, so Launch Budget FY25 v2.xlsx, at exactly 60 characters, is left out and the 61-character v3 Final is in.

The alternative walks upwards from each file, putting the next folder name in front of the path until the parent is NULL. Top-down builds each folder's path once and shares it among that folder's files. Bottom-up repeats the work for every file, but it would stop early if you only needed some of the files.

Reference solution (MySQL)

WITH RECURSIVE folder_path AS (
  SELECT folder_id, CAST(CONCAT('/', folder_name, '/') AS CHAR(1000)) AS path, 1 AS depth
  FROM OneDriveFolder
  WHERE parent_id IS NULL
  UNION ALL
  SELECT f.folder_id, CONCAT(p.path, f.folder_name, '/'), p.depth + 1
  FROM folder_path p
  JOIN OneDriveFolder f ON f.parent_id = p.folder_id
)
SELECT d.file_id, CONCAT(p.path, d.file_name) AS full_path, p.depth,
       CHAR_LENGTH(CONCAT(p.path, d.file_name)) AS path_length
FROM OneDriveFile d
JOIN folder_path p ON p.folder_id = d.folder_id
WHERE CHAR_LENGTH(CONCAT(p.path, d.file_name)) > 60

Another way

WITH RECURSIVE climb AS (
  SELECT file_id, folder_id AS at_folder, CAST(CONCAT('/', file_name) AS CHAR(1000)) AS tail, 0 AS depth
  FROM OneDriveFile
  UNION ALL
  SELECT c.file_id, f.parent_id, CONCAT('/', f.folder_name, c.tail), c.depth + 1
  FROM climb c
  JOIN OneDriveFolder f ON f.folder_id = c.at_folder
)
SELECT file_id, tail AS full_path, depth, CHAR_LENGTH(tail) AS path_length
FROM climb
WHERE at_folder IS NULL AND CHAR_LENGTH(tail) > 60

← Microsoft: Teams Attendees Present for at Least 75% of a Meeting · Microsoft: Management Groups Whose Rolled-Up Azure Spend Tops Budget →