Calculate Weekly, Monthly Watch Hours for Paid Users

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Amazon.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Amazon during technical interviews.

Question

video_view_logs

+---------+----------+----------------+------------+------------+
| user_id | video_id | watched_seconds| watch_date | device_type|
+---------+----------+----------------+------------+------------+
| 101     |   55     | 360            | 2023-08-14 | mobile     |
| 102     |   60     | 240            | 2023-08-15 | desktop    |
| 101     |   58     | 1800           | 2023-08-20 | tablet     |
| 103     |   55     | 600            | 2023-08-21 | mobile     |
| 104     |   72     | 900            | 2023-08-22 | smart_tv   |

##### Scenario

You have a raw event table storing every video view. Product managers need a report of total hours watched per week and per month, segmented by device_type and filtered to paid users only.

##### Question

Write an efficient SQL query (or equivalent pandas code) that returns weekly and monthly aggregated watch hours meeting the above conditions. Explain any windowing or date-trunc techniques you use.

##### Hints

Convert seconds to hours, DATE_TRUNC or to_period for grouping, filter before aggregation to minimize scan size.

PracHub · Accepted Answer

This question evaluates a data scientist's ability to perform time-based aggregations, unit conversion, filtering by user segment, and dimensional segmentation (e.g., device_type) on raw event tables.

Quick Overview

Quick Overview