Age Cohort Message Action Time Share

MEDIUM20 pts

A messaging analytics team wants to understand behavior differences across age cohorts.

Table: Activities

activity_id: unique activity record identifier
user_id: user who generated the activity
activity_type: either "send" or "open"
time_spent: duration spent on this activity event

Table: Age

user_id: unique user identifier
age_bucket: cohort label, one of "21-25", "26-30", "31-35"

Task: For every age_bucket that has activity records, compute:

send_perc: percentage of total cohort time spent on send events
open_perc: percentage of total cohort time spent on open events

Formally, for each cohort:

send_perc = 100 * (sum of time_spent where activity_type = "send") / (sum of time_spent for all activities in cohort)
open_perc = 100 * (sum of time_spent where activity_type = "open") / (sum of time_spent for all activities in cohort)

Round both percentages to exactly 2 decimal places.

Output requirements:

Return columns in this exact order: age_bucket, send_perc, open_perc
Result order can be arbitrary

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Activities:
| activity_id | user_id | activity_type | time_spent |
|-------------|---------|---------------|------------|
| 7274        | 123     | open          | 4.50       |
| 2425        | 123     | send          | 3.50       |
| 1413        | 456     | send          | 5.67       |
| 2536        | 456     | open          | 3.00       |
| 8564        | 456     | send          | 8.24       |
| 5235        | 789     | send          | 6.24       |
| 4251        | 123     | open          | 1.25       |
| 1435        | 789     | open          | 5.25       |

Age:
| user_id | age_bucket |
|---------|------------|
| 123     | 31-35      |
| 789     | 21-25      |
| 456     | 26-30      |

Output

[
  {"age_bucket":"21-25","send_perc":54.31,"open_perc":45.69},
  {"age_bucket":"26-30","send_perc":82.26,"open_perc":17.74},
  {"age_bucket":"31-35","send_perc":37.84,"open_perc":62.16}
]

Explanation

Each cohort percentage is computed from its own total time, not from global totals.

Example

Input

Activities:
| activity_id | user_id | activity_type | time_spent |
|-------------|---------|---------------|------------|
| 1           | 10      | send          | 2.00       |
| 2           | 10      | send          | 3.00       |
| 3           | 11      | open          | 5.00       |
| 4           | 11      | open          | 5.00       |

Age:
| user_id | age_bucket |
|---------|------------|
| 10      | 21-25      |
| 11      | 26-30      |

Output

[
  {"age_bucket":"21-25","send_perc":100.0,"open_perc":0.0},
  {"age_bucket":"26-30","send_perc":0.0,"open_perc":100.0}
]

Explanation

A cohort can be fully dominated by one action type. The other percentage becomes 0.00.

Example

Input

Activities:
| activity_id | user_id | activity_type | time_spent |
|-------------|---------|---------------|------------|
| 91          | 501     | send          | 0.01       |
| 92          | 501     | open          | 0.02       |
| 93          | 502     | send          | 0.02       |
| 94          | 502     | open          | 0.01       |

Age:
| user_id | age_bucket |
|---------|------------|
| 501     | 31-35      |
| 502     | 31-35      |

Output

[
  {"age_bucket":"31-35","send_perc":50.0,"open_perc":50.0}
]

Explanation

Very small durations are still valid; aggregate first, then compute percentages.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Activities <= 200000
1 <= number of rows in Age <= 100000
Activities.activity_id is unique
Age.user_id is unique
activity_type is either 'send' or 'open'
time_spent is non-negative and may include decimal fractions
Output columns must be exactly: age_bucket, send_perc, open_perc
Percentages must be rounded to 2 decimal places
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns [age_bucket, send_perc, open_perc]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Age =

[{"user_id":123,"age_bucket":"31-35"},{"user_id":789,"age_bucket":"21-25"},{"user_id":456,"age_bucket":"26-30"}]

Activities =

[{"activity_id":7274,"user_id":123,"activity_type":"open","time_spent":4.5},{"activity_id":2425,"user_id":123,"activity_type":"send","time_spent":3.5},{"activity_id":1413,"user_id":456,"activity_type":"send","time_spent":5.67},{"activity_id":2536,"user_id":456,"activity_type":"open","time_spent":3},{"activity_id":8564,"user_id":456,"activity_type":"send","time_spent":8.24},{"activity_id":5235,"user_id":789,"activity_type":"send","time_spent":6.24},{"activity_id":4251,"user_id":123,"activity_type":"open","time_spent":1.25},{"activity_id":1435,"user_id":789,"activity_type":"open","time_spent":5.25}]

Age Cohort Message Action Time Share

Hints

Age Cohort Message Action Time Share

Hints