logo

Back to questions

Sending vs. Opening Snaps

Medium

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

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 of the different age groups.

Output the age bucket and percentage of sending and opening snaps. Round the percentages 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:

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

For the age bucket 26-30, the time spent sending snaps was 5.67 and opening 3. The percent of time sending snaps was 5.67/(5.67+3)=65.4%, and the percent of time opening snaps was 3/(5.67+3)=34.6%.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.