Age Cohort Message Action Time Share

Medium

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 1

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 2

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 3

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.

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]

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 |

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 |

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 |