Device Info Schema
Device Info
The device_info table contains device specifications and market information, derived from device model identifiers.
device_info
| Name | Type | Description | Example |
|---|---|---|---|
| sanitized_key | string | normalized device identifier used for joining with other tables (matches LOWER(device)) | iphone12,1 |
| brand | string | brand name of the device | Apple |
| cpu | string | name of the CPU for the device model | Apple A14 |
| display_resolution | string | resolution of the device's display, in widthxheight format | 1170x2532 |
| display_size | float | diagonal display size of the device's screen, measured in inches | 6.1 |
| estimated_value_usd | integer | estimated market value of the device model (in USD), considering its age and depreciation | 699 |
| gpu | string | name of the GPU for the device model | Apple A14 |
| max_ram_mb | integer | maximum RAM (in megabytes) available for this device model | 4096 |
| max_storage_gb | integer | maximum storage capacity (in gigabytes) available for this device model | 256 |
| min_ram_mb | integer | minimum RAM (in megabytes) available for this device model | 4096 |
| min_storage_gb | integer | minimum storage capacity (in gigabytes) available for this device model | 64 |
| model | string | specific device model name | iPhone12,1 |
| release_date | date | official public release date of the device model | 2020-09-15 |
The estimated_value_usd is a dynamic value calculated to reflect market depreciation. This value is derived from recent publicly available market data for the device model. If new pricing information has been gathered today and it has been less than one year since the model was first listed, the value will be updated to reflect the average price. Otherwise, the value is depreciated daily based on a fixed rate: 99.95% for Apple models and 99.975% for all other models.
Joining with Other Tables
The device_info table can be joined with other tables using the sanitized_key field, which matches the lowercase version of the device field. For example:
SELECT
p.user_id,
p.player_id,
p.device,
d.brand,
d.cpu,
d.gpu,
d.estimated_value_usd,
d.display_size
FROM
`checkpoint.player_checkpoint` p
LEFT JOIN
`device_info.device_info` d
ON
LOWER(p.device) = d.sanitized_key
WHERE
p.checkpoint = CURRENT_DATE() - 1
This allows you to enrich player data with detailed device specifications for deeper analysis.