TV Shows Genre Query via REST | Dataford Interview Questions

REST API: use HTTP GET to query a HackerRank TV-series endpoint and find the best TV show in a given genre (Java).

Asked in the Online assessment stage. OA question 1, linked as 'REST API best TV shows in genre'. Write a PostgreSQL query using the supplied genre parameter, \$1.

Output

Return at most one row with show_id, title, and average_rating.
Include only shows in the requested genre with at least one non-NULL rating. Rows with a NULL rating are ignored.
Sort by highest average rating, then title ascending, then show_id ascending to break ties.

Schema

tv_shows

Column	Type	Description
show_idPK	INTEGER	Unique identifier for a TV show
title	VARCHAR(200)	TV show title

show_genres

Column	Type	Description
show_id	INTEGER	Referenced TV show identifier
genre	VARCHAR(50)	Genre assigned to the show

show_ratings

Column	Type	Description
rating_idPK	INTEGER	Unique rating identifier
show_id	INTEGER	Referenced TV show identifier
rating	NUMERIC(4,2)	Rating value, where NULL means no usable rating

Your question is TV Shows Genre Query via REST. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem