Google: Chromebooks Not Synced in 90 Days — SQL Problem
A Google for Education admin audits school Chromebooks on 2025-06-30: a device that has not synced with the admin console for a long time is probably lost…
- Difficulty: Easy
- Topics: Dates, Basics
- Dialect: MySQL
- Problem: #680
Problem statement
A Google for Education admin audits school Chromebooks on 2025-06-30: a device that has not synced with the admin console for a long time is probably lost or broken.
Return device_id, school and days_since_sync — the number of days from the date part of last_sync to 2025-06-30 — for every device with days_since_sync of 90 or more, plus every device that never synced (last_sync is NULL; its days_since_sync is NULL). Order by device_id.
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: Chromebook
| Column | Type |
|---|---|
| device_id | int |
| school | varchar |
| serial_no | varchar |
| last_sync | datetime |
Primary key: device_id.
One enrolled device. last_sync is its latest check-in (never after 2025-06-30), or NULL if it has never checked in.
Examples
Example 1
Chromebook
| device_id | school | serial_no | last_sync |
|---|---|---|---|
| 1 | DPS Pune | CB-7Q1 | 2025-06-29 14:05:00 |
| 2 | DPS Pune | CB-7Q2 | 2025-04-01 23:59:00 |
| 3 | Navodaya Jaipur | CB-8A4 | 2025-04-02 00:10:00 |
| 4 | Navodaya Jaipur | CB-8A5 | NULL |
| 5 | Ryan Hyderabad | CB-9Z0 | 2024-12-15 08:30:00 |
Output
| device_id | school | days_since_sync |
|---|---|---|
| 2 | DPS Pune | 90 |
| 4 | Navodaya Jaipur | NULL |
| 5 | Ryan Hyderabad | 197 |
How to solve Google: Chromebooks Not Synced in 90 Days
DATEDIFF('2025-06-30', last_sync) gives the whole days between the two dates and ignores the time part, so a device that synced at 23:59 on 2025-04-01 is exactly 90 days stale and qualifies, while one that synced just after midnight on 2025-04-02 is 89 days and does not. The boundary is inclusive (>= 90).
Never-synced devices have last_sync NULL. Any comparison with NULL is unknown, which WHERE treats as false, so DATEDIFF(...) >= 90 alone would silently drop exactly the devices most likely to be missing. An explicit last_sync IS NULL OR … brings them back, and selecting the same DATEDIFF expression returns NULL for them, as asked.
The alternative turns the day count into a date cut-off: 90 days or more before 2025-06-30 means a sync before 2025-04-02, and COALESCE maps a missing sync to a far-past date so the same test catches it. Either way it is one scan.
Reference solution (MySQL)
SELECT device_id, school, DATEDIFF('2025-06-30', last_sync) AS days_since_sync
FROM Chromebook
WHERE last_sync IS NULL
OR DATEDIFF('2025-06-30', last_sync) >= 90
ORDER BY device_idAnother way
SELECT device_id, school, DATEDIFF('2025-06-30', last_sync) AS days_since_sync FROM Chromebook WHERE COALESCE(last_sync, '2000-01-01') < '2025-04-02' ORDER BY device_id← Google: Read Match Type From Ad Keyword Syntax · Google: Click-Through Rate at Every Organic Position →