01 · CoreFirsthand candidate report
Cumulative sum in SQL
Write SQL for a cumulative sum—the candidate was asked to implement the same operation in both SQL and Python.
A candidate for Apple’s AI & Data Platforms organization reported a 45-minute first round with cumulative sum in both languages. The table schema and partition key were not published.
Practice focus
- Use SUM() OVER with explicit ordering
- Choose ROWS versus RANGE deliberately
- Explain partitioning and deterministic ties
Evidence and sources
Role: Software Engineer, Data Solutions · Date: 2025 report · Apr 2026 follow-up
The candidate explicitly names cumulative sum in SQL and Python.
02 · CoreFirsthand report · schema included
Count cross-platform viewers
Using playback_events, count users who played video on at least two distinct device types.
playback_events(
user_id,
device_type,
video_id,
play_ts
)
A 1Point3Acres Apple Data Scientist screen publishes the tables and later clarifies all three SQL questions, including this cross-platform viewer count.
Practice focus
- Group at user grain before counting users
- Use COUNT(DISTINCT device_type)
- Avoid multiplying rows with unnecessary joins
Evidence and sources
Role: Data Scientist · Date: Apr 2022
The post includes the schema and the exact business condition.
03 · CoreFirsthand report · schema included
Visitors who never played
Count users who appear in page_events but never appear in playback_events.
page_events(user_id, device_type, page_id, visit_ts)
playback_events(user_id, device_type, video_id, play_ts)
This is the second clarified SQL problem in the same Apple Data Scientist screen report.
Practice focus
- Use NOT EXISTS or a null-safe anti-join
- Count distinct users, not page rows
- Explain the NOT IN + NULL trap
Evidence and sources
Role: Data Scientist · Date: Apr 2022
The post includes both source tables and the exact exclusion condition.
04 · CoreFirsthand report · schema included
Users whose first play was April 2021
Count users whose earliest playback event occurred during April 2021.
playback_events(user_id, device_type, video_id, play_ts)
This is the third clarified SQL problem in the Apple Data Scientist screen report. The table supplies play_ts at event grain.
Practice focus
- Reduce to MIN(play_ts) per user
- Use a half-open date range
- Keep timestamp timezone assumptions explicit
Evidence and sources
Role: Data Scientist · Date: Apr 2022
The report supplies the month, year, event table, and first-event condition.
05 · CoreTwo candidate reports
Daily active users by Apple platform
Given user-login data with platform information, calculate daily active users separately for iPad, iPhone, and Mac.
A first-person 1Point3Acres Apple Data Scientist report names this task. An independent IC4 Siri Data Scientist write-up records the same prompt family.
Practice focus
- Normalize timestamp to the intended day
- Count distinct users at day/platform grain
- Clarify whether output is rows or pivoted columns
Evidence and sources
Role: Data Scientist / Senior IC4, Siri · Date: Dec 2021 · corroborated later
Two Apple interview accounts independently report DAU by platform.
06 · StretchVerified candidate-report guide
Rolling 7-day active users
Given user events, calculate rolling 7-day active users using a CTE.
Exponent’s verified Apple Data Engineer guide attributes this prompt to recent candidate reports and explicitly names the CTE requirement.
Practice focus
- Build a date spine so zero-activity days survive
- Distinguish rolling events from rolling distinct users
- Avoid summing daily distinct counts
Evidence and sources
Role: Data Engineer · Date: Recent candidate report
Presented as a question reported by recent Apple candidates.
07 · CoreFirsthand senior-role report
A/B result-shown rate
Using search and results tables, compute the rate at which a result was shown for each A/B test bucket.
search(search_id, ab_testing_group /* 0 or 1 */)
results(search_id, ..., is_shown)
A 1Point3Acres Apple AI/ML Senior Data Scientist technical-screen report publishes the table fragments and asks for result-shown rate in each ab_bucket.
Practice focus
- Define numerator and denominator at the right grain
- Prevent one-to-many join inflation
- Cast before division and handle zero denominators
Evidence and sources
Role: AI/ML Senior Data Scientist · Date: Apr 2022
The rate, grouping key, and table relationship are visible in the report.
08 · CoreRepeated across two reports
RANK vs DENSE_RANK
Explain the difference between RANK and DENSE_RANK, then write a query that demonstrates their behavior with ties.
The Apple AI/ML Senior Data Scientist screen explicitly asked the difference. The Apple Cloud Data Engineer report also asked window functions and RANK versus DENSE_RANK with a query.
Practice focus
- Show skipped versus consecutive ranks
- Include PARTITION BY and deterministic ordering
- Contrast with ROW_NUMBER
Evidence and sources
Role: Senior Data Scientist / Cloud Data Engineer · Date: 2022 · 2023
The same comparison appears in two Apple interview accounts.
09 · CoreOriginal report + detailed repost
Unique origin–destination routes
From a flight-passenger table, return unique origin–destination routes.
The Apple Cloud Data Engineer interview recap names this SQL task. The public summary does not say whether A→B and B→A should collapse, making that the key clarification.
Practice focus
- Clarify directed versus undirected routes
- Normalize pairs only if direction is irrelevant
- Handle null airports and duplicates
Evidence and sources
Role: Cloud Data Engineer · Date: 2023
The route task is preserved; directionality is not.
10 · StretchOriginal report + detailed repost
Nth highest or lowest joined value
Join Orders, Customers, and Products, then find the Nth highest or lowest requested value.
The Apple Cloud Data Engineer account describes this three-table SQL task. The public recap does not expose the precise selected measure, so do not assume revenue, price, or order total.
Practice focus
- Confirm the output grain and measure
- Handle ties with the requested ranking semantics
- Join without duplicating order-line values
Evidence and sources
Role: Cloud Data Engineer · Date: 2023
Table set and Nth-highest/lowest requirement are preserved; measure is hidden.
11 · StretchOriginal report + detailed repost
Clean missing, invalid, and duplicate rows
Write SQL to clean a messy dataset containing missing values, invalid values, and duplicate rows.
The Apple Cloud Data Engineer interview account lists this data-cleaning SQL task. The public recap does not disclose the validation rules or schema.
Practice focus
- Define validity and a deterministic survivor rule
- Use staged CTEs that make each repair auditable
- Preserve rejected records and quality metrics
Evidence and sources
Role: Cloud Data Engineer · Date: 2023
The three defect classes are explicit; business validation rules are not public.
12 · CoreFirsthand candidate report
Audit a dataset for duplicate keys
Given a SQL dataset, determine whether its supposed key contains duplicates and surface the offending keys.
A 1Point3Acres Apple Data Scientist onsite report explicitly describes checking a SQL dataset for duplicate keys.
Practice focus
- Group by the full candidate key
- Return counts and representative duplicate rows
- Decide whether NULL participates in key equality
Evidence and sources
Role: Data Scientist · Date: 2018
The original report names duplicate-key detection directly.
13 · StretchFirsthand report · partial schema
Expand subscriptions into active months
Given subscription IDs, user IDs, and subscription start/end timestamps, map each subscription interval to its corresponding year-month rows.
A 1Point3Acres Apple interview report describes subscription ranges and asks to convert each time span into its corresponding years/months. Some field names are hidden behind the forum preview.
Practice focus
- Generate or join to a month spine
- Choose inclusive/exclusive interval boundaries
- Avoid recursive blowups on long subscriptions
Evidence and sources
Role: Data / Analytics role · Date: 2020
The interval-to-month transformation is public; the full schema is not.
14 · StretchFirsthand report · partial schema
Top 5 content by month and country
Return the top five content items for each month and country, restricted to paid plans.
The same Apple subscription interview report names this problem directly. The public search excerpt does not expose the underlying content and plan table schemas.
Practice focus
- Aggregate before ranking
- Partition rank by month and country
- Clarify metric and tie behavior at rank five
Evidence and sources
Role: Data / Analytics role · Date: 2020
Dimensions, top-N value, and paid-plan filter are public; table details are not.
15 · CoreApple-tagged question bank
Top 5 actions on Apple platforms
For November 2020, rank the five most frequent actions performed on iPhone and iPad. Return action and rank; ties share a rank.
events(user_id, created_at, action, platform)
InterviewQuery publishes this as “Popular Apple Actions” with an events schema and date/platform filters. It is a company-tagged real-interview question bank, not a first-person narrative.
Practice focus
- Filter dates with a half-open interval
- Aggregate action counts before ranking
- Honor shared ranks and decide whether more than five rows may return
Evidence and sources
Role: Apple data interview question bank · Date: Published question
Exact prompt and schema are public; provenance is a real-interview question bank.
16 · StretchApple-tagged question bank
Most popular client among call-heavy users
Find the most popular client_id among users for whom at least 50% of events are calls. Call events are video-call received/sent and voice-call received/sent.
A public post labels this an Apple SQL interview question and publishes the 50% eligibility rule plus the four call-event types. This is question-bank evidence rather than a firsthand recap.
Practice focus
- Compute user eligibility before client popularity
- Use conditional counts with floating-point division
- Clarify client counting grain and ties
Evidence and sources
Role: Apple SQL interview question · Date: Jul 2025 post
The cohort definition and requested dimension are fully stated.
17 · CoreFirsthand report · partial wording
Daily average session time
Given date, session_id, user_id, and time_spent, write a query for daily average time spent.
sessions(date, session_id, user_id, time_spent)
A first-person Apple Data Scientist phone-screen report publishes this first SQL prompt and its four columns. The remainder of the wording is paywalled, so the exact averaging grain beyond “daily” is not public.
Practice focus
- Clarify average per event, session, or user-day
- Deduplicate session rows before averaging if necessary
- State how null and incomplete sessions are treated
Evidence and sources
Role: Data Scientist · Date: Oct 2019
The schema and daily-average request are public; the hidden suffix is not reconstructed.
18 · CoreOriginal report + detailed repost
Join types, with working queries
Describe the SQL join types and write example queries that demonstrate each one.
The Apple Cloud Data Engineer candidate account reports being asked to explain all join types and provide code examples.
Practice focus
- Cover inner, left, right, full, cross, and self joins
- Show unmatched-row and NULL behavior
- Explain join cardinality and accidental row multiplication
Evidence and sources
Role: Cloud Data Engineer · Date: 2023
The candidate account explicitly requires both explanations and SQL examples.
19 · StretchOriginal report + detailed repost
Fact tables, dimensions, and normalization
Given a database model, identify fact and dimension tables, describe their relationships, and normalize the data.
The Apple Cloud Data Engineer account lists model relationships, identifying fact and dimension tables, and data normalization in the SQL/database round.
Practice focus
- State the business process and grain before naming facts
- Separate measures from descriptive dimensions
- Explain normalization tradeoffs for OLTP versus analytical workloads
Evidence and sources
Role: Cloud Data Engineer · Date: 2023
All three modeling tasks are preserved in the detailed recap of the original account.
20 · StretchApple-tagged question bank
Apple-product users vs all device users
By language, return the number of Apple-product users and the total number of users with any device; sort by total users descending. Apple products are MacBook Pro, iPhone 5s, and iPad Air.
A public Apple “Product Counts” question publishes the full rule and expected output columns. It is preserved as a company-tagged question-bank artifact.
Practice focus
- Count distinct users after device joins
- Preserve languages with device users but no Apple users
- Avoid double-counting users with multiple devices
Evidence and sources
Role: Apple data interview question bank · Date: Published question
Product set, dimensions, metrics, and sort order are all public.