Objective

Write a SQL query to calculate the percentage of time users spend sending messages versus opening messages, grouped by age bucket.

Table Schema:

• activities

• age_breakdown

Task Requirements:

• Join the activities and age_breakdown tables
• Calculate total time spent on 'send' and 'open' activities per age group
• Calculate the percentage of time spent on each activity type
• Round percentages to 2 decimal places
• Display age_bucket, send_perc, and open_perc
  
  

Output columns: age_bucket, send_perc, open_perc

SELECT 
    age_bucket,
    ROUND(100.0 * SUM(CASE WHEN activity_type = 'send' THEN time_spent ELSE 0 END) / 
          SUM(time_spent), 2) AS send_perc,
    ROUND(100.0 * SUM(CASE WHEN activity_type = 'open' THEN time_spent ELSE 0 END) / 
          SUM(time_spent), 2) AS open_perc
FROM activities a
INNER JOIN age_breakdown ab ON a.user_id = ab.user_id
GROUP BY age_bucket
ORDER BY age_bucket;

Explanation

Step 1: Joining the Tables

FROM activities a
INNER JOIN age_breakdown ab ON a.user_id = ab.user_id

Joins activities with age_breakdown on user_id to associate each activity with the user's age group.

Step 2: Grouping by Age Bucket

GROUP BY age_bucket

Groups activities by age group to calculate percentages per demographic segment.

Step 3: Calculating Send Time

SUM(CASE WHEN activity_type = 'send' THEN time_spent ELSE 0 END)

Sums time_spent only for 'send' activities, treating all other types as 0.

Step 4: Computing Send and Open Percentages

ROUND(100.0 * SUM(CASE WHEN activity_type = 'send' THEN time_spent ELSE 0 END) / SUM(time_spent), 2)

Divides send (or open) time by total time, multiplies by 100.0 for float division, and rounds to 2 decimal places.

Step 5: Sorting Results

ORDER BY age_bucket

Orders results by age group for easy comparison across demographics.