Given a table of Facebook posts, for each user
who posted at least twice in 2021, write a query to find the number of days
between each user's first post of the year and last post of the year in the
year 2021. Output the user and number of the days between each user's first and
last post.
table name: posts
Solution:
select user_id, DATEDIFF(day, min(post_date) ,max(post_date)) as days_between
from posts where year(post_date) = 2021 group by user_id having count(post_id)>1
Output:
SQL Script:
CREATE TABLE [dbo].[posts](
[user_id]
[int] NULL,
[post_id]
[int] NULL,
[post_date]
[date] NULL,
[post_comment]
[nvarchar](1050) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[posts] ([user_id], [post_id], [post_date], [post_comment]) VALUES (151652, 111766, CAST(N'2021-12-01' AS Date), N'it''s always
winter, but never Christmas.')
GO
INSERT [dbo].[posts] ([user_id], [post_id], [post_date], [post_comment]) VALUES (661093, 442560, CAST(N'2021-09-08' AS Date), N'Bed. Class
8-12. Work 12-3. Gym 3-5 or 6. Then class 6-10. Another day that''s gonna fly
by. I miss my girlfriend')
GO
INSERT [dbo].[posts] ([user_id], [post_id], [post_date], [post_comment]) VALUES (661093, 624356, CAST(N'2021-02-14' AS Date), N'Happy
valentines!')
GO
INSERT [dbo].[posts] ([user_id], [post_id], [post_date], [post_comment]) VALUES (151652, 599415, CAST(N'2021-01-28' AS Date), N'Need a hug')
GO
INSERT [dbo].[posts] ([user_id], [post_id], [post_date], [post_comment]) VALUES (178425, 157336, CAST(N'2021-03-24' AS Date), N'I''m so done
with these restrictions - I want to travel!!!')
GO
INSERT [dbo].[posts] ([user_id], [post_id], [post_date], [post_comment]) VALUES (423967, 784254, CAST(N'2021-05-05' AS Date), N'Just going to
cry myself to sleep after watching Marley and Me.')
GO
INSERT [dbo].[posts] ([user_id], [post_id], [post_date], [post_comment]) VALUES (151325, 613451, CAST(N'2022-01-01' AS Date), N'Happy new year
all my friends!')
GO
INSERT [dbo].[posts] ([user_id], [post_id], [post_date], [post_comment]) VALUES (151325, 987562, CAST(N'2022-01-07' AS Date), N'The global
surface temperature for June 2022 was the sixth-highest in the 143-year record.
This is definitely global warming happening.')
GO
INSERT [dbo].[posts] ([user_id], [post_id], [post_date], [post_comment]) VALUES (661093, 674356, CAST(N'2021-08-18' AS Date), N'Can''t wait to
start my freshman year - super excited!')
GO
INSERT [dbo].[posts] ([user_id], [post_id], [post_date], [post_comment]) VALUES (151325, 451464, CAST(N'2021-10-25' AS Date), N'25-10-2021')
GO
INSERT [dbo].[posts] ([user_id], [post_id], [post_date], [post_comment]) VALUES (151652, 994156, CAST(N'2021-04-01' AS Date), N'Does anyone
have an extra iPhone charger to sell?')
GO