Practice Interview Questions
| Question | Status |
|---|---|
Write a SQL query to obtain a breakdown by age group of the time spent sending vs. opening snaps as a % of total time spent. The total time spent should only include these 2 activities (sending and opening).
For instance, If a user group spends 5 minutes opening snaps and 10 minutes sending snaps, the query should calculate the % of time opening snaps as 5 divided by 15 (10+5) and the % of time sending snaps as 10 divided by 15.
Round the percentage to 2 decimal places in the output.
activities table:
| Column Name | Description |
|---|---|
| activity_id | Unique identifier for each activity |
| user_id | Identifier for the user |
| activity_type | Type of activity (e.g. sending, opening) |
| time_spent | Time spent on the activity |
| activity_date | Timestamp of the activity |
age_breakdown table:
| Column Name | Description |
|---|---|
| user_id | Identifier for the user |
| age_bucket | Age group of the user |
| age_bucket | send_perc | open_perc |
|---|---|---|
| 26-30 | 0.30 | 0.70 |
| 31-35 | 0.65 | 0.35 |
Write a SQL query to obtain a breakdown by age group of the time spent sending vs. opening snaps as a % of total time spent. The total time spent should only include these 2 activities (sending and opening).
For instance, If a user group spends 5 minutes opening snaps and 10 minutes sending snaps, the query should calculate the % of time opening snaps as 5 divided by 15 (10+5) and the % of time sending snaps as 10 divided by 15.
Round the percentage to 2 decimal places in the output.
activities table:
| Column Name | Description |
|---|---|
| activity_id | Unique identifier for each activity |
| user_id | Identifier for the user |
| activity_type | Type of activity (e.g. sending, opening) |
| time_spent | Time spent on the activity |
| activity_date | Timestamp of the activity |
age_breakdown table:
| Column Name | Description |
|---|---|
| user_id | Identifier for the user |
| age_bucket | Age group of the user |
| age_bucket | send_perc | open_perc |
|---|---|---|
| 26-30 | 0.30 | 0.70 |
| 31-35 | 0.65 | 0.35 |