Dataford
Popular roles
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Browse
Browse All RolesEvery role hub, from analyst to MLBrowse All CompaniesCompany-specific interview loopsAll Interview GuidesThe full guide library
Top questions by role
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Top questions by skill
SQLPythonStatisticsMachine LearningA/B TestingSystem DesignGenerative AIProduct SenseMetricsBehavioral
Browse all questions →Try a mock interview
ExperiencesCoaching
New
Practice
Mock InterviewsTimed interview simulations with feedbackSuccess PathYour 6-week structured planModulesCurated lessons by topicWebinarsTalks from ex-Big Tech data leadsPlaygroundA free-form scratch editor
Learn
BlogInterview strategy and career adviceTech Job Market ReportHiring trends across data and AI rolesFor UniversitiesDataford for career centersAbout DatafordWho we are and how we build
Pricing
Get started
Top Content by Watch Time
00:00
5 left

Top Content by Watch Time

MediumSQL · PostgreSQL

Problem

Disney+ Hotstar's Product Analytics team wants to identify the strongest-performing content in January 2026. Write a PostgreSQL query that ranks content within each content type using watch-time metrics.

Requirements

  1. Join the content catalog to viewing sessions and include only sessions from January 2026.
  2. Calculate total watch minutes and average completion percentage for each content item. Treat NULL watch minutes as zero.
  3. Use a window function to rank content separately within each content_type.
  4. Return the top two content items per content type, ordered by content type and rank.

Output details

  • Return columns in this order: content_type, title, total_watch_minutes, avg_completion_pct, performance_rank.
  • Include sessions from DATE '2026-01-01' inclusively through DATE '2026-02-01' exclusively.
  • Round avg_completion_pct to two decimal places and return total_watch_minutes as an integer.
  • Exclude content whose total watch minutes are zero.
  • Rank by total_watch_minutes descending, then avg_completion_pct descending with NULLs last, then title ascending.
  • Order results by content_type ascending, then performance_rank ascending.

Schema

content
ColumnTypeDescription
content_idPKINTEGERUnique content identifier
titleVARCHAR(150)Content title
content_typeVARCHAR(30)Content category
viewing_sessions
ColumnTypeDescription
session_idPKINTEGERUnique viewing session identifier
content_idINTEGERWatched content identifier
watched_atDATEDate of the viewing session
watch_minutesINTEGERMinutes watched in the session
completion_pctINTEGERPercentage of content completed
Tablescontentviewing_sessions
Interviewer

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the two tables are in the Question tab.

You need to log in / sign up to run or submit.
CodePostgreSQL
Sign up free to run your codeLog inLn 1
Run your query to see results here.