Autocorrect Enablement Rate by Keyboard Language for One Day
Company: Apple
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: hard
Interview Round: Technical Screen
A device maker's keyboard team logs daily keyboard usage and each device's keyboard settings. For the day 2025-06-02, compute the autocorrection enablement rate for each keyboard language: among the devices that typed in that language on that day, the share that have autocorrection turned on.
The report asks for this rate for one day, per language, by joining a usage table with a settings table. This version fixes the day, the rule for which devices count, and the handling of devices without a settings row, so that the answer is unique.
### Input Table(s)
**`keyboard_usage`**: one row per day, device and keyboard language.
| Column | Type | Meaning |
| --- | --- | --- |
| `usage_date` | DATE | Day of the usage |
| `device_id` | INTEGER | Device that typed |
| `platform` | TEXT | Device platform, such as `phone`, `tablet` or `laptop` |
| `language` | TEXT | Keyboard language code, such as `en_US` |
| `chars_typed` | INTEGER | Characters typed with that language's keyboard on that day |
Primary key: (`usage_date`, `device_id`, `language`).
**`keyboard_settings`**: one row per device, holding the device's keyboard settings.
| Column | Type | Meaning |
| --- | --- | --- |
| `device_id` | INTEGER | Primary key |
| `predictive_bar_enabled` | BOOLEAN | Whether the word-suggestion bar above the keyboard is on |
| `autocorrect_enabled` | BOOLEAN | Whether autocorrection is on |
### Output Contract
Write one read-only PostgreSQL query (`SELECT`, optionally with `WITH`) that returns one row per language that has at least one qualifying device, with these columns in this order:
| Column | Type | Meaning |
| --- | --- | --- |
| `language` | text | Keyboard language code |
| `devices` | integer | Number of qualifying devices for the language |
| `enabled_devices` | integer | Qualifying devices whose `autocorrect_enabled` is true |
| `enablement_rate` | numeric | `enabled_devices / devices`, computed as an exact decimal division and then rounded to 4 decimal places |
A device qualifies for a language when it has a `keyboard_usage` row for that language with `usage_date = 2025-06-02` and `chars_typed > 0`, and it also has a row in `keyboard_settings`.
Order the rows by `language` ascending. Each language appears once, so the order is fully determined.
### Example
`keyboard_usage`:
| usage_date | device_id | platform | language | chars_typed |
| --- | --- | --- | --- | --- |
| 2025-06-02 | 101 | phone | en_US | 540 |
| 2025-06-02 | 101 | phone | es_MX | 120 |
| 2025-06-02 | 102 | phone | en_US | 80 |
| 2025-06-02 | 103 | tablet | en_US | 0 |
| 2025-06-02 | 104 | tablet | es_MX | 300 |
| 2025-06-02 | 105 | laptop | en_US | 45 |
| 2025-06-01 | 106 | phone | fr_FR | 200 |
| 2025-06-02 | 107 | phone | fr_FR | 60 |
| 2025-06-02 | 108 | laptop | en_US | 15 |
`keyboard_settings`:
| device_id | predictive_bar_enabled | autocorrect_enabled |
| --- | --- | --- |
| 101 | true | true |
| 102 | true | false |
| 103 | true | true |
| 104 | false | false |
| 105 | false | true |
| 106 | true | true |
| 107 | true | false |
Expected result:
| language | devices | enabled_devices | enablement_rate |
| --- | --- | --- | --- |
| en_US | 3 | 2 | 0.6667 |
| es_MX | 2 | 1 | 0.5000 |
| fr_FR | 1 | 0 | 0.0000 |
- `en_US`: devices 101, 102 and 105 qualify. Device 103 typed 0 characters, and device 108 has no settings row. Devices 101 and 105 have autocorrection on, so the rate is 2/3.
- `es_MX`: devices 101 and 104 qualify; only 101 has autocorrection on. Device 101 counts for both `en_US` and `es_MX`.
- `fr_FR`: device 106's usage is from 2025-06-01, so only device 107 qualifies, and its autocorrection is off.
### Constraints and Clarifications
- No column in either table contains NULL. `chars_typed` is never negative.
- A device that typed in several languages on the day counts once for each of those languages.
- Rows with `chars_typed = 0` do not count as typing in that language.
- A device with qualifying usage but no `keyboard_settings` row is excluded from both `devices` and `enabled_devices`. Settings rows for devices with no qualifying usage are ignored.
- Treat `keyboard_settings` as the settings in effect on 2025-06-02.
- `platform` does not affect the result; all platforms are counted together.
- Languages with no qualifying device do not appear in the output.
- Language codes all follow one format of letters and an underscore, such as `en_US`, so ascending order is plain string order.
```hint Who belongs in the denominator
Decide which rows define the population for each language before you choose how to join the two tables.
```
Overview: A SQL question on keyboard telemetry: join daily usage by device and language with each device's settings to compute, for one day, the share of devices typing in each language that have autocorrection turned on. It tests defining the right population, choosing the join, and counting devices that type in several languages.
Read the full Apple Data Scientist interview experience this question came from