Apple-style Company ChallengeEasyVerified answerSQLite live

Average Screen Time by Device

What is the average daily screen time and app opens for each Apple device type?

  • Aggregation
  • Numeric functions
  • Sorting

Challenge brief

Understand the request

Health & Wellness Team is building the Screen Time Insights dashboard and needs average usage metrics per device type.

Show average screen time and app opens per device type from device usage records.

Return

  • device_type
  • records (usage row count)
  • avg_screen_time_mins (rounded 1)
  • avg_app_opens (rounded 1)

Constraints

  • Aggregate all daily device-usage records by device type
  • Show the highest average screen time first and resolve ties by device type

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

device_usage

  • device_typeVARCHAR(50)
  • screen_time_minutesINTEGER
  • app_opensINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

All data is in device_usage. Group by device_type and average the numeric columns. No JOIN needed.

Hint 2

GROUP BY device_type. COUNT(*) for record count. ROUND(AVG(screen_time_minutes), 1) and ROUND(AVG(app_opens), 1).

Hint 3

Build question 14 from its business grain: identify the driving rows, add only valid relationships, then apply the required filtering, aggregation, and deterministic ordering.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT device_type, COUNT(*) AS records, ROUND(AVG(screen_time_minutes), 1) AS avg_screen_time_mins, ROUND(AVG(app_opens), 1) AS avg_app_opens FROM device_usage GROUP BY device_type ORDER BY avg_screen_time_mins DESC, device_type

Why this works

Single-table GROUP BY. AVG(app_opens) for iPhone rounds to 38.3 because there are 3 iPhone rows (45+32+38 = 115 / 3 = 38.33...). MacBook has the highest screen time but lowest app_opens per session — consistent with desktop work patterns.

Success check

4 device types — MacBook leads (450 min avg), Apple Watch lowest (60 min avg)

Expected result

Use this output to verify values, aliases, ordering, and row count.

device_typerecordsavg_screen_time_minsavg_app_opens
MacBook245015
iPad228025
iPhone324038.3
Apple Watch16015

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.