Quick Overview

This question evaluates relational database design and data engineering competencies—including schema modeling, many-to-many relationships, idempotent ingest, indexing and partitioning, OLTP versus analytics integration, GDPR-compliant deletion strategies, and query formulation—within the Data Manipulation (SQL/Python) domain for a Data Scientist role. It is commonly asked to assess both conceptual understanding and practical application of scalable data architectures, performance tuning, and compliance trade-offs, focusing on the ability to reason about schema choices, read/write optimization, and analytics integration without implementation details.

Design a scalable video platform database

Company: Google

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

Design the relational database for a YouTube-like video company. Deliverables: 1) list the core tables with key columns, types, and constraints (users, channels, videos, video_transcodes/qualities, captions, tags, video_tags, views, likes, comments, subscriptions, playlists, playlist_videos, ad_impressions, daily_video_metrics); 2) define primary/foreign keys, uniqueness, and soft-delete and GDPR-compliant deletion strategies; 3) model many-to-many relationships (e.g., videos↔tags, playlists↔videos) and idempotent ingest (avoid duplicate views/likes); 4) include indexing/partitioning (e.g., views partitioned by event_date, video_id; clustered indexes for hot queries), and how you’d support both OLTP and analytics (star schema or read-optimized warehouse tables) without blocking writes; 5) show sample CREATE TABLE DDL for 3–4 critical tables (videos, views, comments, ad_impressions) and explain how you’d query: a) watch-time per video per day, b) top N videos by unique viewers in the last 7 days, c) comments pagination with anti-abuse flags; 6) describe how you’d store multiple renditions (1080p, 4K, HDR) and A/B test assignments for thumbnails.

Overview: This question evaluates relational database design and data engineering competencies—including schema modeling, many-to-many relationships, idempotent ingest, indexing and partitioning, OLTP versus analytics integration, GDPR-compliant deletion strategies, and query formulation—within the Data Manipulation (SQL/Python) domain for a Data Scientist role. It is commonly asked to assess both conceptual understanding and practical application of scalable data architectures, performance tuning, and compliance trade-offs, focusing on the ability to reason about schema choices, read/write optimization, and analytics integration without implementation details.

Daily watch-time per video

You are given tables for a YouTube-like platform: users, videos, and views. Each row in views represents a (potentially idempotent) view event with a watch duration in seconds. Write a SQL query to compute total watch-time per video per day for view events between 2025-05-26 and 2025-06-01 (inclusive). Return one row per (event_date, video) with: - event_date - video_id - title - total_watch_seconds Sort results by event_date ascending, then video_id ascending.

Tables

users(user_id BIGINT, username VARCHAR(50), created_at TIMESTAMP, is_deleted BOOLEAN)

videos(video_id BIGINT, uploader_user_id BIGINT, title VARCHAR(200), uploaded_at TIMESTAMP, is_deleted BOOLEAN)

views(view_id BIGINT, view_uuid VARCHAR(36), video_id BIGINT, viewer_user_id BIGINT, event_date DATE, viewed_at TIMESTAMP, watch_seconds INT, session_id VARCHAR(64))

comments(comment_id BIGINT, video_id BIGINT, user_id BIGINT, created_at TIMESTAMP, body VARCHAR(1000), is_deleted BOOLEAN, is_spam_flagged BOOLEAN, abuse_score INT)

Hints

  1. Aggregate watch_seconds grouped by event_date and video_id.
  2. Join to videos to return the video title.

Top N videos by unique viewers in a 7-day window

Using the same users, videos, and views tables, find the top 2 videos by unique viewers in the 7-day window FROM 2025-05-26 TO 2025-06-01 (inclusive). Unique viewers means COUNT(DISTINCT viewer_user_id) per video over the whole window. Return: - video_id - title - unique_viewers If there is a tie, break ties by video_id ascending. Sort by unique_viewers descending, then video_id ascending.

Tables

users(user_id BIGINT, username VARCHAR(50), created_at TIMESTAMP, is_deleted BOOLEAN)

videos(video_id BIGINT, uploader_user_id BIGINT, title VARCHAR(200), uploaded_at TIMESTAMP, is_deleted BOOLEAN)

views(view_id BIGINT, view_uuid VARCHAR(36), video_id BIGINT, viewer_user_id BIGINT, event_date DATE, viewed_at TIMESTAMP, watch_seconds INT, session_id VARCHAR(64))

comments(comment_id BIGINT, video_id BIGINT, user_id BIGINT, created_at TIMESTAMP, body VARCHAR(1000), is_deleted BOOLEAN, is_spam_flagged BOOLEAN, abuse_score INT)

Hints

  1. Use COUNT(DISTINCT viewer_user_id) grouped by video_id.
  2. Apply the explicit date filter and then LIMIT 2 with a deterministic tie-break.

Keyset pagination for comments with anti-abuse fields

Using the users and comments tables, fetch the NEXT page of comments for a given video using keyset pagination. For video_id = 102: - Only include comments where is_deleted = FALSE. - Order by created_at DESC, then comment_id DESC. - Page size is 3. Assume you already returned the first page, and the cursor points to the last row of that first page: - cursor_created_at = '2025-06-01 09:40:00' - cursor_comment_id = 203 Write a SQL query that returns the next page after this cursor. Return these columns: - comment_id - created_at - username - body - is_spam_flagged - abuse_score

Tables

users(user_id BIGINT, username VARCHAR(50), created_at TIMESTAMP, is_deleted BOOLEAN)

videos(video_id BIGINT, uploader_user_id BIGINT, title VARCHAR(200), uploaded_at TIMESTAMP, is_deleted BOOLEAN)

views(view_id BIGINT, view_uuid VARCHAR(36), video_id BIGINT, viewer_user_id BIGINT, event_date DATE, viewed_at TIMESTAMP, watch_seconds INT, session_id VARCHAR(64))

comments(comment_id BIGINT, video_id BIGINT, user_id BIGINT, created_at TIMESTAMP, body VARCHAR(1000), is_deleted BOOLEAN, is_spam_flagged BOOLEAN, abuse_score INT)

Hints

  1. Keyset pagination uses a strict 'less than cursor' condition on the (created_at, comment_id) sort key.
  2. Use the same ORDER BY as the keyset comparison, and LIMIT for page size.

Loading coding console...