Sending vs. Opening Snaps

Assume you are given the tables below containing information on Snapchat users, their ages, and their time spent sending and opening snaps. Write a query to obtain a breakdown of the time spent sending vs. opening snaps (as a percentage of total time spent on these activities) for each age group.

 Output the age bucket and percentage of sending and opening snaps. Round the percentage to 2 decimal places.

Notes:

·       You should calculate these percentages:

·       time sending / (time sending + time opening)

·       time opening / (time sending + time opening)

·       To avoid integer division in percentages, multiply by 100.0 and not 100.

table name: activities

table name: age_breakdown

Solution:

with cte as
(
select a1.age_bucket,sum(case when activity_type='send' then time_spent else 0 end) as send1,
sum(case when activity_type='open' then time_spent else 0 end) as open1
 from activities as a
join age_breakdown as a1 on a.user_id = a1.user_id
where activity_type in ('send','open')
group by a1.age_bucket
)
select age_bucket,round((send1)/((send1)+(open1)) * 100.00,2) as sent_prec,
round((open1)/((send1)+(open1)) * 100.00,2) as open_prec
from cte

Output:

SQL Script:

CREATE TABLE [dbo].[activities](
       [activity_id]
[int] NULL,
       [user_id]
[int] NULL,
       [activity_type]
[nvarchar](50) NULL,
       [time_spent]
[float] NULL,
       [activity_date]
[datetime] NULL
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[age_breakdown](
       [user_id]
[int] NULL,
       [age_bucket]
[nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[activities] ([activity_id], [user_id], [activity_type], [time_spent],
[activity_date]) VALUES (7274, 123, N'open', 4.5, CAST(N'2022-06-22T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[activities] ([activity_id], [user_id], [activity_type], [time_spent],
[activity_date]) VALUES (2425, 123, N'send', 3.5, CAST(N'2022-06-22T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[activities] ([activity_id], [user_id], [activity_type], [time_spent],
[activity_date]) VALUES (1413, 456, N'send', 5.67, CAST(N'2022-06-23T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[activities] ([activity_id], [user_id], [activity_type], [time_spent],
[activity_date]) VALUES (1414, 789, N'chat', 11, CAST(N'2022-06-25T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[activities] ([activity_id], [user_id], [activity_type], [time_spent],
[activity_date]) VALUES (2536, 456, N'open', 3, CAST(N'2022-06-25T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[age_breakdown] ([user_id], [age_bucket]) VALUES (123, N'31-35')
GO
INSERT [dbo].[age_breakdown] ([user_id], [age_bucket]) VALUES (456, N'26-30')
GO
INSERT [dbo].[age_breakdown] ([user_id], [age_bucket]) VALUES (789, N'21-25')
GO