← Amazon Interview Insights

Amazon·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Jul 2026

Summary

Data Scientist round at Amazon that was pretty SQL-heavy. One question, clean schema, but the aggregation requirements had enough wrinkles to trip you up if you weren't careful with date functions.

Questions Asked (1)

Q1

Given a table of user watch events with columns for user ID, video ID, minutes watched, timestamp, and subscription type, write a SQL query that returns total watch time in hours per user, grouped by both week and month, for premium subscribers only.

Product Analytics & MetricsData Modeling
Author's notes

The filter part is easy, just a WHERE on subscription.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the table schema and the definition of 'week' and 'month' (e.g., ISO week, calendar month). Then write a SQL query that filters for premium subscribers, aggregates watch time in hours per user per week and per month, and groups by user, week, and month. Consider using date functions to extract week and month from the timestamp.

Pro tip: Mention that you would validate the query with sample data and consider edge cases like users with no watch events or timezone differences. Also, discuss how you might optimize the query for performance on large datasets, such as using appropriate indexes or partitioning.

1. Clarify requirements and schema

Confirm the table name, column names, and data types. Clarify what 'week' means (e.g., ISO week starting Monday) and how to handle time zones. Ensure 'premium' is a valid subscription type.

2. Filter and transform data

Filter rows where subscription_type = 'premium'. Convert minutes watched to hours by dividing by 60. Extract week and month from the timestamp using appropriate date functions.

3. Aggregate and group

Group by user_id, week, and month, and sum the hours watched. Use a single query with GROUP BY user_id, week, month, or use window functions if needed.

4. Handle edge cases and validate

Consider users with no watch events (may need LEFT JOIN from a users table). Validate results with sample data and check for anomalies like negative watch time.

5. Optimize and present

Discuss potential performance improvements (indexes on user_id, subscription_type, timestamp). Present the final query clearly and explain the logic.

Key Points to Mention

  • Use of DATE_TRUNC or EXTRACT functions to get week and month (e.g., DATE_TRUNC('week', timestamp)).
  • Conversion of minutes to hours: SUM(minutes_watched)/60.0.
  • Filtering with WHERE subscription_type = 'premium'.
  • Grouping by user_id, week, and month to get per-user totals.
  • Handling of time zones: ensure timestamps are in a consistent time zone before extracting week/month.
  • Consideration of performance: indexing on subscription_type and timestamp, and partitioning if the table is large.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.