0/304

00:00:00

Description

Editorial

Review Leader and February Top Movie

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

users =

[{"user_id":1,"name":"Damon"},{"user_id":2,"name":"Monica"},{"user_id":3,"name":"Maris"},{"user_id":4,"name":"Jules"}]

movies =

[{"movie_id":1,"title":"Nebula Run"},{"movie_id":2,"title":"Arctic Tale 2"},{"movie_id":3,"title":"Night Jester"}]

movie_rating =

[{"movie_id":1,"user_id":1,"rating":3,"created_at":"2020-01-12"},{"movie_id":1,"user_id":2,"rating":4,"created_at":"2020-02-11"},{"movie_id":1,"user_id":3,"rating":2,"created_at":"2020-02-12"},{"movie_id":1,"user_id":4,"rating":1,"created_at":"2020-01-01"},{"movie_id":2,"user_id":1,"rating":5,"created_at":"2020-02-17"},{"movie_id":2,"user_id":2,"rating":2,"created_at":"2020-02-01"},{"movie_id":2,"user_id":3,"rating":2,"created_at":"2020-03-01"},{"movie_id":3,"user_id":1,"rating":3,"created_at":"2020-02-22"},{"movie_id":3,"user_id":2,"rating":4,"created_at":"2020-02-25"}]

Review Leader and February Top Movie

Hints

Review Leader and February Top Movie

Hints