Interview Query

Self-Upvotes

0
Have you seen this question before?

We’re given three tables representing a forum of users and their comments on posts.

Write a query to get the percentage of comments by each user where that user also upvoted their own comment.

Note: A user that doesn’t make a comment should have a 0 percent self-upvoted.

Example:

Input:

users table

Columns Type
id INTEGER
created_at DATETIME
username VARCHAR

comments table

Columns Type
id INTEGER
created_at DATETIME
post_id INTEGER
user_id INTEGER

comment_votes table

Columns Type
id INTEGER
created_at DATETIME
user_id INTEGER
comment_id INTEGER
is_upvote BOOLEAN

Output

Columns Type
username VARCHAR
total_comments INTEGER
percentage_self_voted FLOAT
Next question: Comments Histogram
.....
MySQL 8.0.17
Loading editor
Use Shift + Enter to run query