The "Viewers Turned Streamers" interview question is a database/SQL problem. You are given a table of sessions (viewer/streamer IDs, session types, and timestamps). You need to find the users whose first ever session was as a "viewer" and who later had a session as a "streamer." You then return the count of their streaming sessions, sorted by count in descending order.
PhonePe and Google use the "Viewers Turned Streamers" coding problem to test a candidate's SQL skills, specifically their ability to use "Window Functions" and subqueries. It assesses how you handle user lifecycle analysis and temporal data in a relational database.
The primary pattern is the "Database interview pattern" using RANK() or ROW_NUMBER(). First, you identify each user's first session using a window function partitioned by user_id and ordered by timestamp. Filter for those whose first session type is 'viewer'. Then, join this list back to the original table to count their sessions where the type is 'streamer'.
Sessions:
A frequent mistake is not correctly identifying the "first" session and instead just checking if a user has both types, which is incorrect. Another error is forgetting to filter for sessions that happened after they became a streamer, or not sorting the final output as requested. Candidates also often miss users who might have multiple viewer sessions before their first streamer session.
For the "Viewers Turned Streamers" coding problem, familiarize yourself with Common Table Expressions (CTEs). Using CTEs to break down the problem into "Find First Session," "Filter Users," and "Count Stream Sessions" makes your SQL query much more maintainable and easier to explain.
| Title | Difficulty | Topics | LeetCode |
|---|---|---|---|
| Find the Subtasks That Did Not Execute | Hard | Solve | |
| Median Employee Salary | Hard | Solve | |
| Top Three Wineries | Hard | Solve | |
| Trips and Users | Hard | Solve | |
| Department Top Three Salaries | Hard | Solve |
