Liked' Posts

#Meta/Facebook

Find the number of posts which were reacted to with a like.

table name: facebook_posts


table name: facebook_reactions


Solution:

select count(distinct post_id) as n_posts
from facebook_reactions
where reaction='like'

Output:


SQL Script:

USE [StrataScratch]
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
CREATE TABLE [dbo].[facebook_reactions](
	[poster] [int] NULL,
	[friend] [int] NULL,
	[reaction] [varchar](50) NULL,
	[date_day] [int] NULL,
	[post_id] [int] NULL
) ON [PRIMARY]
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
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (2, 1, N'like', 1, 0)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (2, 6, N'like', 1, 0)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (1, 2, N'like', 1, 1)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (1, 3, N'heart', 1, 1)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (1, 4, N'like', 1, 1)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (1, 5, N'heart', 1, 1)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (1, 6, N'like', 1, 1)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (2, 1, N'like', 2, 2)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (2, 6, N'like', 2, 2)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (1, 2, N'like', 2, 3)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (1, 3, N'like', 2, 3)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (1, 4, N'like', 2, 3)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (1, 5, N'like', 2, 3)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (1, 6, N'like', 2, 3)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (2, 1, N'laugh', 1, 0)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (2, 6, N'laugh', 1, 1)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (1, 2, N'laugh', 1, 0)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (1, 3, N'laugh', 1, 3)
GO
INSERT [dbo].[facebook_reactions] ([poster], [friend], [reaction], [date_day], [post_id]) VALUES (1, 4, N'laugh', 1, 4)
GO


Comments (0)
Questions List