Description

Table: Actions

• This table may have duplicate rows.
• The action column is an ENUM (category) type of (‘view’, ’like’, ‘reaction’, ‘comment’, ‘report’, ‘share’).
• The extra column has optional information about the action, such as a reason for the report or a type of reaction.

Table: Removals

• post_id is the primary key (column with unique values) of this table.
• Each row in this table indicates that some post was removed due to being reported or as a result of an admin review.

Problem Statement

Write a solution to find the average daily percentage of posts that got removed after being reported as spam, rounded to 2 decimal places.

The result format is in the following example.

Example 1:

Input:

• Actions table:

• Removals table:

Output:

Explanation:

• The percentage for 2019-07-04 is 50% because only one post of two spam reported posts were removed.
• The percentage for 2019-07-02 is 100% because one post was reported as spam and it was removed.
• The other days had no spam reports so the average is (50 + 100) / 2 = 75% Note that the output is only one number and that we do not care about the remove dates.

Solution

The solution starts by finding the posts which have been reported as spam. For this you can use the WHERE clause.

1SELECT *
2    FROM Action
3    WHERE extra = 'spam'

Next, you need to find average daily percentage of posts which have been removed after being reported as ‘spam’. In order to find average daily percentage, you first need daily percentage and then you can use AVG() function to aggregate them across history. To find daily percentage of posts being removed, you need number of posts reported as spam on a daily basis and the number of posts removed on a daily basis. These two information are stored in two separate tables Actions and Removals tables. So, you will have to join them together. It’s very likely that some of the posts were reported but never removed. So, you need to perform LEFT OUTER JOIN with Removals table. This way the posts which were not removed will have post_id as NULL.

1SELECT a.action_date, a.extra, a.post_id
2    FROM Actions a
3    LEFT JOIN Removals r
4    ON a.post_id = r.post_id
5    WHERE a.extra = 'spam'

Now, to calculate daily number of posts removed and number of posts reported, you can use a.post_id and r.post_id. For removed posts, you will have a value for r.post_id whereas for those which were reported will have a.post_id as non-null value. You also need to make sure that you count distinct post_id because the same post might be reported multiple times.

 1WITH daily_average AS (
 2    SELECT a.action_date,
 3        COUNT(DISTINCT a.post_id) AS daily_reported,
 4        COUNT(DISTINCT CASE WHEN r.post_id IS NOT NULL THEN r.post_id END) AS daily_removed
 5        FROM Actions a
 6        LEFT JOIN Removals r
 7        ON a.post_id = r.post_id
 8        WHERE a.extra = 'spam'
 9        GROUP BY a.action_date
10) SELECT * FROM daily_average;

The above query calculates daily number of posts reported and the number of posts removed on a daily basis.

The last step is to calculate average daily percentage of posts removed. For this, you first need daily percentage, so above query needs little bit tweaking. Although above query calculates the number of posts, it doesn’t calculate the average posts removed. So, that part needs to change as shown below.

1WITH daily_average AS (
2    SELECT a.action_date,
3        COUNT(DISTINCT CASE WHEN r.post_id IS NOT NULL THEN r.post_id END) / COUNT(DISTINCT a.post_id) AS daily_percentage
4        FROM Actions a
5        LEFT JOIN Removals r
6        ON a.post_id = r.post_id
7        WHERE a.extra = 'spam'
8        GROUP BY a.action_date
9) SELECT * FROM daily_average;

This produces daily percetage as shown below.

The final step is to calculate average daily percentage and rename the column to the correct header. You also need to report this number as percentage with only 2 decimal places using ROUND() SQL function.

 1WITH daily_average AS (
 2    SELECT a.action_date,
 3        COUNT(DISTINCT CASE WHEN r.post_id IS NOT NULL THEN r.post_id END) / COUNT(DISTINCT a.post_id) AS daily_percentage
 4        FROM Actions a
 5        LEFT JOIN Removals r
 6        ON a.post_id = r.post_id
 7        WHERE a.extra = 'spam'
 8        GROUP BY a.action_date
 9) SELECT ROUND(AVG(daily_percentage) * 100, 2) AS average_daily_percent
10    FROM daily_average;