Google: Chromebooks Not Synced in 90 Days

Easy
680.

Google: Chromebooks Not Synced in 90 Days

EasyDatesBasics

MySQL

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

ColumnType
device_idint
schoolvarchar
serial_novarchar
last_syncdatetime

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.

Example

The rows must come back in the order shown.

Chromebook

device_idschoolserial_nolast_sync
1DPS PuneCB-7Q12025-06-29 14:05:00
2DPS PuneCB-7Q22025-04-01 23:59:00
3Navodaya JaipurCB-8A42025-04-02 00:10:00
4Navodaya JaipurCB-8A5NULL
5Ryan HyderabadCB-9Z02024-12-15 08:30:00

Output

device_idschooldays_since_sync
2DPS Pune90
4Navodaya JaipurNULL
5Ryan Hyderabad197
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: 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

ColumnType
device_idint
schoolvarchar
serial_novarchar
last_syncdatetime

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_idschoolserial_nolast_sync
1DPS PuneCB-7Q12025-06-29 14:05:00
2DPS PuneCB-7Q22025-04-01 23:59:00
3Navodaya JaipurCB-8A42025-04-02 00:10:00
4Navodaya JaipurCB-8A5NULL
5Ryan HyderabadCB-9Z02024-12-15 08:30:00

Output

device_idschooldays_since_sync
2DPS Pune90
4Navodaya JaipurNULL
5Ryan Hyderabad197

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_id

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