Review Leader and February Top Movie

Medium

A streaming analytics platform stores films, reviewers, and scoring events in three relational tables: movies(movie_id, title) movie_id: unique movie identifier title: unique movie title users(user_id, name) user_id: unique reviewer identifier name: unique reviewer name movie_rating(movie_id, user_id, rating, created_at) one row per (movie_id, user_id) pair rating is an integer score created_at is the review date (YYYY-MM-DD) Your task is to return two leaderboard answers in one output column named results: Reviewer activity leader: Find the reviewer with the highest number of submitted ratings across all dates. If there is a tie, choose the lexicographically smaller reviewer name. February quality leader: Consider only rows where created_at is in February 2020. Find the movie with the highest average rating in that month. If there is a tie, choose the lexicographically smaller movie title. Output requirements: Return exactly one column named results. The conceptual output has two rows: row 1: reviewer activity leader name row 2: February quality leader movie title Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

movies:
| movie_id | title            |
|----------|------------------|
| 1        | Nebula Run       |
| 2        | Arctic Tale 2    |
| 3        | Night Jester     |

users:
| user_id | name   |
|---------|--------|
| 1       | Damon  |
| 2       | Monica |
| 3       | Maris  |
| 4       | Jules  |

movie_rating:
| movie_id | user_id | rating | created_at |
|----------|---------|--------|------------|
| 1        | 1       | 3      | 2020-01-12 |
| 1        | 2       | 4      | 2020-02-11 |
| 1        | 3       | 2      | 2020-02-12 |
| 1        | 4       | 1      | 2020-01-01 |
| 2        | 1       | 5      | 2020-02-17 |
| 2        | 2       | 2      | 2020-02-01 |
| 2        | 3       | 2      | 2020-03-01 |
| 3        | 1       | 3      | 2020-02-22 |
| 3        | 2       | 4      | 2020-02-25 |

Output

[
  {"results":"Damon"},
  {"results":"Arctic Tale 2"}
]

Explanation

Damon and Monica both submitted 3 ratings, so Damon wins by lexicographic tie-break. In February 2020, Arctic Tale 2 and Night Jester both average 3.5, so Arctic Tale 2 wins by title tie-break.

Example 2

Input

movies:
| movie_id | title      |
|----------|------------|
| 10       | Solo Cut   |

users:
| user_id | name |
|---------|------|
| 7       | Ari  |

movie_rating:
| movie_id | user_id | rating | created_at |
|----------|---------|--------|------------|
| 10       | 7       | 4      | 2020-02-10 |

Output

[
  {"results":"Ari"},
  {"results":"Solo Cut"}
]

Explanation

Only one reviewer and one February rating exist, so both leaders are uniquely determined.

Example 3

Input

movies:
| movie_id | title        |
|----------|--------------|
| 20       | Alpha Frame  |
| 21       | Beta Frame   |

users:
| user_id | name  |
|---------|-------|
| 30      | Alice |
| 31      | Aaron |
| 32      | Milo  |

movie_rating:
| movie_id | user_id | rating | created_at |
|----------|---------|--------|------------|
| 20       | 30      | 5      | 2020-02-01 |
| 21       | 30      | 1      | 2020-01-01 |
| 20       | 31      | 4      | 2020-02-02 |
| 21       | 31      | 2      | 2020-03-01 |
| 20       | 32      | 3      | 2020-02-03 |

Output

[
  {"results":"Aaron"},
  {"results":"Alpha Frame"}
]

Explanation

Alice and Aaron tie in total rating count, so Aaron wins by name ordering. In February, Alpha Frame has the highest average and is selected.

Constraints

1 <= number of rows in movies <= 5,000
1 <= number of rows in users <= 5,000
1 <= number of rows in movie_rating <= 200,000
movie_id is unique
title is unique
user_id is unique
name is unique
(movie_id, user_id) is unique in movie_rating
1 <= rating <= 5
created_at is a valid date string in YYYY-MM-DD format
Each movie_rating.movie_id exists in movies.movie_id
Each movie_rating.user_id exists in users.user_id
At least one row exists in movie_rating
At least one February 2020 row exists in movie_rating
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exactly one column named results

movies: | movie_id | title | |----------|------------------| | 1 | Nebula Run | | 2 | Arctic Tale 2 | | 3 | Night Jester | users: | user_id | name | |---------|--------| | 1 | Damon | | 2 | Monica | | 3 | Maris | | 4 | Jules | movie_rating: | movie_id | user_id | rating | created_at | |----------|---------|--------|------------| | 1 | 1 | 3 | 2020-01-12 | | 1 | 2 | 4 | 2020-02-11 | | 1 | 3 | 2 | 2020-02-12 | | 1 | 4 | 1 | 2020-01-01 | | 2 | 1 | 5 | 2020-02-17 | | 2 | 2 | 2 | 2020-02-01 | | 2 | 3 | 2 | 2020-03-01 | | 3 | 1 | 3 | 2020-02-22 | | 3 | 2 | 4 | 2020-02-25 |

movies: | movie_id | title | |----------|------------| | 10 | Solo Cut | users: | user_id | name | |---------|------| | 7 | Ari | movie_rating: | movie_id | user_id | rating | created_at | |----------|---------|--------|------------| | 10 | 7 | 4 | 2020-02-10 |

movies: | movie_id | title | |----------|--------------| | 20 | Alpha Frame | | 21 | Beta Frame | users: | user_id | name | |---------|-------| | 30 | Alice | | 31 | Aaron | | 32 | Milo | movie_rating: | movie_id | user_id | rating | created_at | |----------|---------|--------|------------| | 20 | 30 | 5 | 2020-02-01 | | 21 | 30 | 1 | 2020-01-01 | | 20 | 31 | 4 | 2020-02-02 | | 21 | 31 | 2 | 2020-03-01 | | 20 | 32 | 3 | 2020-02-03 |