Data Engineering Movie Success Pipeline

DataEng phone screens combine SQL, pipeline design, production data-quality concerns, and a short event-stream coding exercise around movie launch success. Requirements First 30 minutes: SQL over movie and user tables to measure success of a newly launched movie. Discuss what tables and metrics are needed. Queries use common constructs: GROUP BY, SUM, CTEs, filters, and joins. Production pipeline discussion: data checks, partition key, data skew, scheduling, and failure handling. Final coding: classify user-movie event streams by completion percentage, such as 30 / 50 / 80 percent watched, and return sorted results. Metrics To Prepare Starts, completes, completion rate, watch time, unique viewers, repeat views. Funnel by day since launch. Segmentation by geography, device, acquisition channel, and recommendation surface. Data-quality checks: null IDs, duplicate events, late events, event-time vs processing-time drift. Notes The round is fast. Write simple SQL first, then refine. Partition by event date for common launch-window analysis; consider movie ID bucketing if one title is extremely hot. Data skew needs an answer: salting hot keys, two-stage aggregation, or separate heavy-hitter path. For event-stream coding, decide whether multiple events per user/movie should keep max progress or latest progress. Preparation Write SQL for top movies by complete views, completion rate, and next-movie transition. Practice describing a production DAG: ingestion, validation, transform, aggregate, publish, alert. Implement a small stream classifier and sort by count descending.