Share of Active Users

#Meta/Facebook

Output share of US users that are active. Active users are the ones with an "open" status in the table.

table name: fb_active_users


Solution:

select 
convert(float,1.0*sum(case when status='open' then 1 else 0 end)/
count(*)) as active_users_share
from fb_active_users
where  country='USA'

Output:


SQL Script:

USE [StrataScratch]
GO
CREATE TABLE [dbo].[fb_active_users](
	[user_id] [int] NULL,
	[name] [varchar](50) NULL,
	[status] [varchar](50) NULL,
	[country] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (33, N'Amanda Leon', N'open', N'Australia')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (27, N'Jessica Farrell', N'open', N'Luxembourg')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (18, N'Wanda Ramirez', N'open', N'USA')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (50, N'Samuel Miller', N'closed', N'Brazil')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (16, N'Jacob York', N'open', N'Australia')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (25, N'Natasha Bradford', N'closed', N'USA')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (34, N'Donald Ross', N'closed', N'China')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (52, N'Michelle Jimenez', N'open', N'USA')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (11, N'Theresa John', N'open', N'China')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (37, N'Michael Turner', N'closed', N'Australia')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (32, N'Catherine Hurst', N'closed', N'Mali')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (61, N'Tina Turner', N'open', N'Luxembourg')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (4, N'Ashley Sparks', N'open', N'China')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (82, N'Jacob York', N'closed', N'USA')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (87, N'David Taylor', N'closed', N'USA')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (78, N'Zachary Anderson', N'open', N'China')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (5, N'Tiger Leon', N'closed', N'China')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (56, N'Theresa Weaver', N'closed', N'Brazil')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (21, N'Tonya Johnson', N'closed', N'Mali')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (89, N'Kyle Curry', N'closed', N'Mali')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (7, N'Donald Jim', N'open', N'USA')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (22, N'Michael Bone', N'open', N'Canada')
GO
INSERT [dbo].[fb_active_users] ([user_id], [name], [status], [country]) VALUES (31, N'Sara Michaels', N'open', N'Denmark')
GO


Comments (0)
Questions List