Back to questions

# Sending vs. Opening Snaps [Snapchat SQL Interview Question]

Medium

This is the same question as problem #25 in the SQL Chapter of Ace the Data Science Interview!

Assume you're given tables with information on Snapchat users, including their ages and 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 grouped by age group. Round the percentage to 2 decimal places in the output.

Notes:

• Calculate the following percentages:
• time spent sending / (Time spent sending + Time spent opening)
• Time spent opening / (Time spent sending + Time spent opening)
• To avoid integer division in percentages, multiply by 100.0 and not 100.

Effective April 15th, 2023, the solution has been updated and optimised.

### Table

Column NameType
activity_idinteger
user_idinteger
activity_typestring ('send', 'open', 'chat')
time_spentfloat
activity_datedatetime

### Example Input

activity_iduser_idactivity_typetime_spentactivity_date
7274123open4.5006/22/2022 12:00:00
2425123send3.5006/22/2022 12:00:00
1413456send5.6706/23/2022 12:00:00
1414789chat11.0006/25/2022 12:00:00
2536456open3.0006/25/2022 12:00:00

### Table

Column NameType
user_idinteger
age_bucketstring ('21-25', '26-30', '31-25')

### Example Input

user_idage_bucket
12331-35
45626-30
78921-25

### Example Output

age_bucketsend_percopen_perc
26-3065.4034.60
31-3543.7556.25

### Explanation

Using the age bucket 26-30 as example, the time spent sending snaps was 5.67 and the time spent opening snaps was 3.

To calculate the percentage of time spent sending snaps, we divide the time spent sending snaps by the total time spent on sending and opening snaps, which is 5.67 + 3 = 8.67.

So, the percentage of time spent sending snaps is 5.67 / (5.67 + 3) = 65.4%, and the percentage of time spent opening snaps is 3 / (5.67 + 3) = 34.6%.

The dataset you are querying against may have different input & output - this is just an example!

PostgreSQL 14