Spam Posts

StrataScratch

#Meta/Facebook

Calculate the percentage of spam posts in all viewed posts by day. A post is considered a spam if a string "spam" is inside keywords of the post. Note that the facebook_posts table stores all posts posted by users. The facebook_post_views table is an action table denoting if a user has viewed a post.

table name:facebook_posts


table name:facebook_post_views


Solution:

select 
(100* sum(case when p.post_keywords like '%spam%' then 1 else 0 end)/count(p.post_id)) 
as percentage,post_date 
from facebook_posts as p
join facebook_post_views as v
on p.post_id = v.post_id
group by post_date

Output:


SQL Script:

USE [StrataScratch]
Go
CREATE TABLE [dbo].[facebook_post_views](
	[post_id] [int] NULL,
	[viewer_id] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[facebook_posts](
	[post_id] [int] NOT NULL,
	[poster] [int] NULL,
	[post_text] [varchar](500) NULL,
	[post_keywords] [varchar](50) NULL,
	[post_date] [datetime] NULL,
 CONSTRAINT [PK_facebook_posts] PRIMARY KEY CLUSTERED 
(
	[post_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[facebook_post_views] ([post_id], [viewer_id]) VALUES (4, 0)
GO
INSERT [dbo].[facebook_post_views] ([post_id], [viewer_id]) VALUES (4, 1)
GO
INSERT [dbo].[facebook_post_views] ([post_id], [viewer_id]) VALUES (4, 2)
GO
INSERT [dbo].[facebook_post_views] ([post_id], [viewer_id]) VALUES (5, 0)
GO
INSERT [dbo].[facebook_post_views] ([post_id], [viewer_id]) VALUES (5, 1)
GO
INSERT [dbo].[facebook_post_views] ([post_id], [viewer_id]) VALUES (5, 2)
GO
INSERT [dbo].[facebook_post_views] ([post_id], [viewer_id]) VALUES (3, 1)
GO
INSERT [dbo].[facebook_post_views] ([post_id], [viewer_id]) VALUES (3, 2)
GO
INSERT [dbo].[facebook_post_views] ([post_id], [viewer_id]) VALUES (3, 3)
GO
INSERT [dbo].[facebook_posts] ([post_id], [poster], [post_text], [post_keywords], [post_date]) VALUES (0, 2, N'The Lakers game from last night was great.', N'[basketball,lakers,nba]', CAST(N'2019-01-01T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[facebook_posts] ([post_id], [poster], [post_text], [post_keywords], [post_date]) VALUES (1, 1, N'Lebron James is top class.', N'[basketball,lebron_james,nba]', CAST(N'2019-01-02T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[facebook_posts] ([post_id], [poster], [post_text], [post_keywords], [post_date]) VALUES (2, 2, N'Asparagus tastes OK.', N'[asparagus,food]', CAST(N'2019-01-01T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[facebook_posts] ([post_id], [poster], [post_text], [post_keywords], [post_date]) VALUES (3, 1, N'Spaghetti is an Italian food.', N'[spaghetti,food]', CAST(N'2019-01-02T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[facebook_posts] ([post_id], [poster], [post_text], [post_keywords], [post_date]) VALUES (4, 3, N'User 3 is not sharing interests', N'[#spam#]', CAST(N'2019-01-01T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[facebook_posts] ([post_id], [poster], [post_text], [post_keywords], [post_date]) VALUES (5, 3, N'User 3 posts SPAM content a lot', N'[#spam#]', CAST(N'2019-01-02T00:00:00.000' AS DateTime))
GO


Comments (0)
Questions List