Design a scalable video platform database

Q: Design a scalable video platform database

This is a Data Manipulation (SQL/Python) interview question from Google for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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.

Design a scalable video platform database

Comments (0)