Instruction Write a query to return top 2 films based on their rental revenues in their category. A film can only belong to one category. The order of your results doesn't matter. If there are ties, return just one of them. Return the following columns: category, film_id…

Prev Discuss

Question 64

Top 2 films by category

Unsolved Hard Lecture

Premium Access

Unlock this premium interview question.

230+ interview questions
Official solutions
SQL, Python, and R tracks

***********

***** * ***** ** ****** *** 2 ***** ***** ** ***** ****** ******** ** ***** ********.
* **** *** **** ****** ** *** ********.
*** ***** ** **** ******* *****'* ******.
** ***** *** ****, ****** **** *** ** ****.
****** *** ********* *******: ********, ****_**, *******, ***_***

Table 1: category

***** **********.

  ***_****   | ***_****
-------------+--------------------------
 ********_** | *******
 ****        | ****

Table 2: film

       ***_****       |  ***_****
----------------------+--------------------------
 ****_**              | *******
 *****                | ****
 ***********          | ****
 *******_****         | *******
 ********_**          | ********
 ********_********_** | ********
 ******_********      | ********
 ******_****          | *******
 ******               | ********
 ***********_****     | *******
 ******               | ****

Table 3: film_category

* **** *** **** ****** ** *** ********

  ***_****   | ***_****
-------------+--------------------------
 ****_**     | ********
 ********_** | ********

Table 4: inventory

**** *** ** ******, ********_** ** *** ******* *** ** **** *****.

   ***_****   | ***_****
--------------+--------------------------
 *********_** | *******
 ****_**      | ********
 *****_**     | ********

Table 5: payment

***** ****** ******* ************ *****

   ***_****   | ***_****
--------------+--------------------------
 *******_**   | *******
 ********_**  | ********
 *****_**     | ********
 ******_**    | *******
 ******       | *******
 *******_**   | ********* **** **** ****

Table 6: rental

   ***_****   | ***_****
--------------+--------------------------
 ******_**    | *******
 ******_**    | ********* **** **** ****
 *********_** | *******
 ********_**  | ********
 ******_**    | ********* **** **** ****
 *****_**     | ********

Sample results

Last Submission

postgres

No submission yet for this engine. Run and submit your query to save it here.

Expected results

Submit a query to compare against expected output.

Engine

Interview timer

Recommended interview pacing

Easy: 5 min for direct warm-up style questions.

Medium: 10 min for multi-step interview queries.

Hard: 15 min for layered questions with tighter time pressure.

A common bar is solving about 2 medium-or-harder questions in a 30 minute interview.

15:00

Timer

Your results

Run your query to preview results here.

More ROW_NUMBER, RANK, DENSE_RANK Questions

Browse Topic Set

Question	Level	Company	FTPR
#212. Highest spender's issuers	Hard	visa	0%
#211. Top issuer by category	Medium	visa	30%
#208. Top 3 urls by testing groups	Hard	apple	10%
#203. Top product by country by month	Hard	apple	100%
#201. Top 3 students for each subject	Hard	snap	67%
#188. Top 1 popular question by department	Hard	google	14%
#166. Top 10 customers based on spend growth	Medium	walmart	11%
#165. Session stitching	Hard	walmart	17%
#158. First trip completion rate	Medium	lyft	22%
#157. Number of trips before a driver got banned	Medium	lyft	10%
#148. Most popular video category	Medium	google	13%
#145. Returning customers after first buy	Hard	afterpay	16%
#144. Third order	Medium	afterpay	9%
#142. First order date	Easy	afterpay	40%
#139. Poor first delivery experience	Medium	doordash	17%
#137. Extremely late first orders	Medium	doordash	9%
#135. Unlucky employees	Easy	robinhood	16%
#127. Average rating after 10th trip	Hard	uber	11%
#123. Top listing in the United States, United Kingdom and Canada	Medium	airbnb	10%
#122. Top country by wow growth	Hard	airbnb	13%
#120. First ever booking	Hard	airbnb	10%
#119. Top 2 countries by bookings	Hard	airbnb	10%
#116. Top answers day by device	Easy	amazon	14%
#110. Most popular product by category	Medium	amazon	12%