Social Media Addiction can be a crippling disease affecting millions every year.
We need to identify people who may fall into that category.
Write a query to find the people who spent a higher than average amount of time on social media.
Provide just their first names alphabetically so we can reach out to them individually.
table name: users
table name: user_time
Solution 1:
STEP 0: Perform Inner join on tables users and user_time on column user_id.
STEP 1: Use Avg() function to calculate the average amount of time.
select avg(t.media_time_minutes) from users as u
join user_time as t on u.user_id = t.user_id
STEP 2: Use Step 2 query as a subquery for the main query and apply the condition.
where t.media_time_minutes > avg(t.media_time_minutes)
STEP 3: Order by u.first_name.
(Query cost (relative to batch) : 57%):
select u.first_name from users as u
join user_time as t on u.user_id = t.user_id
where t.media_time_minutes > (select avg(t.media_time_minutes) from users as u
join user_time as t on u.user_id = t.user_id)
order by u.first_name
Solution 2
STEP 0: Perform Inner join on tables users and user_time on column user_id
STEP 1: Use over clause to calculate the average amount of time. And there will be no parameters inside over clause as we want to find the avg total in respective of all users. Hence no partition by is required.
STEP 2: With the help of Common Expression Table (CTE) where we supply a query which results behaves as a virtual table.
STEP 3: We are going to select the required first_name column from the virtual table named cte with where clause as media_time_minutes>avgMin.
STEP 4: Order by first_name.
(Query cost (relative to batch) : 43%):
with cte as(
select u.user_id,u.first_name,t.media_time_minutes, avg(t.media_time_minutes) over () as avgMin
from users as u
join user_time as t on u.user_id = t.user_id
)
select first_name from cte where media_time_minutes > avgMin
order by first_name
Output:
SQL Script:
USE [AnalystBuilder]
GO
/****** Object: Table [dbo].[user_time] Script Date: 04-02-2024 19:29:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[user_time](
[user_id] [int] NULL,
[media_time_minutes] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[users] Script Date: 04-02-2024 19:29:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[users](
[user_id] [int] NULL,
[first_name] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[user_time] ([user_id], [media_time_minutes]) VALUES (1, 0)
GO
INSERT [dbo].[user_time] ([user_id], [media_time_minutes]) VALUES (2, 200)
GO
INSERT [dbo].[user_time] ([user_id], [media_time_minutes]) VALUES (3, 250)
GO
INSERT [dbo].[user_time] ([user_id], [media_time_minutes]) VALUES (4, 15)
GO
INSERT [dbo].[user_time] ([user_id], [media_time_minutes]) VALUES (5, 500)
GO
INSERT [dbo].[user_time] ([user_id], [media_time_minutes]) VALUES (6, 45)
GO
INSERT [dbo].[user_time] ([user_id], [media_time_minutes]) VALUES (7, 450)
GO
INSERT [dbo].[user_time] ([user_id], [media_time_minutes]) VALUES (8, 1000)
GO
INSERT [dbo].[user_time] ([user_id], [media_time_minutes]) VALUES (9, 300)
GO
INSERT [dbo].[user_time] ([user_id], [media_time_minutes]) VALUES (10, 60)
GO
INSERT [dbo].[users] ([user_id], [first_name]) VALUES (1, N'John')
GO
INSERT [dbo].[users] ([user_id], [first_name]) VALUES (2, N'Janice')
GO
INSERT [dbo].[users] ([user_id], [first_name]) VALUES (3, N'Michael')
GO
INSERT [dbo].[users] ([user_id], [first_name]) VALUES (4, N'Molly')
GO
INSERT [dbo].[users] ([user_id], [first_name]) VALUES (5, N'Adam')
GO
INSERT [dbo].[users] ([user_id], [first_name]) VALUES (6, N'Amanda')
GO
INSERT [dbo].[users] ([user_id], [first_name]) VALUES (7, N'Chris')
GO
INSERT [dbo].[users] ([user_id], [first_name]) VALUES (8, N'Christine')
GO
INSERT [dbo].[users] ([user_id], [first_name]) VALUES (9, N'Bella')
GO
INSERT [dbo].[users] ([user_id], [first_name]) VALUES (10, N'Brian')
GO