Snapchat mid JOINWHERE INGROUP BYCASE WHENSUMROUND

For each age group, calculate the percentage of time spent sending snaps vs opening snaps out of total send+open time. Round to 2 decimal places.

Snapchat · Engagement Analytics — practice this real-world SQL scenario live in your browser.

📖 Story Snapchat · Engagement Analytics

Snapchat HQ, product review Monday. The growth team pulls up the dashboard: 'We're seeing different engagement patterns across age groups — but are users spending more time sending snaps or opening them?' Analyst Priya has the activities log and user age data. Time to find out.

🎯 Your Mission

📋 Table Structure

🗂 activities

activity_id INTEGER 1001

user_id INTEGER 101

activity_type TEXT send

time_spent REAL 4.50

activity_date TEXT 2022-06-10

🗂 age_breakdown

user_id INTEGER 101

age_bucket TEXT 21-25

⚡ Step-by-Step Walkthrough

Join tables and filter out 'chat'

query.sql

SELECT a.user_id,
       ab.age_bucket,
       a.activity_type,
       a.time_spent
FROM activities a
JOIN age_breakdown ab ON a.user_id = ab.user_id
WHERE a.activity_type IN ('send', 'open');

💡 Explanation

Two separate tables hold the data we need: activities has every user action (send, open, chat) with time spent, while age_breakdown maps each user to their age group. Neither table alone can answer the question.
JOIN activities a JOIN age_breakdown ab ON a.user_id = ab.user_id — this links every activity row to the age bucket of the user who performed it. Alias a = activities, ab = age_breakdown.
WHERE a.activity_type IN ('send', 'open') — this removes all 'chat' rows entirely. The question only asks about sending vs opening, so chat time must not enter any calculation.
Why filter this early? If chat rows survive into the aggregation step, SUM(time_spent) will include chat time in the denominator — and the percentages will be completely wrong.
Result of this step: a clean joined dataset with only send and open rows, each tagged with the user's age bucket — ready for grouping.

Group by age and split send vs open time using CASE WHEN

query.sql

SELECT ab.age_bucket,
       SUM(CASE WHEN a.activity_type = 'send'
               THEN a.time_spent ELSE 0 END) AS send_time,
       SUM(CASE WHEN a.activity_type = 'open'
               THEN a.time_spent ELSE 0 END) AS open_time
FROM activities a
JOIN age_breakdown ab ON a.user_id = ab.user_id
WHERE a.activity_type IN ('send', 'open')
GROUP BY ab.age_bucket
ORDER BY ab.age_bucket;

💡 Explanation

GROUP BY ab.age_bucket collapses all rows of the same age group into a single row. All three 21-25 users become one row, all 26-30 become one row, and so on.
CASE WHEN a.activity_type = 'send' THEN a.time_spent ELSE 0 END — for each row, if it is a send activity, use its time_spent value; otherwise contribute 0. SUM() then adds all these values together per group.
This technique is called conditional aggregation — splitting a single column's values into multiple output columns based on a condition, all within one GROUP BY query. No subquery or JOIN needed.
The same logic applies for open: SUM(CASE WHEN activity_type = 'open' THEN time_spent ELSE 0 END) gives total open time per age group.
Run this step to verify: for 21-25 you should see send_time = 16.50, open_time = 15.10. For 26-30: send = 20.50, open = 7.80. For 31-35: send = 11.00, open = 20.30.
These are raw totals, not percentages yet. The next step converts them into rounded percentages.

Calculate percentages and round to 2 decimal places

query.sql

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

💡 Explanation

Percentage formula: send_perc = send_time / (send_time + open_time) × 100. Since we already filtered out chat with WHERE IN ('send','open'), SUM(a.time_spent) is automatically equal to send_time + open_time. No need to write them separately in the denominator.
Why multiply by 100.0 and NOT 100? SQL integer division truncates: 11 / 31 = 0, not 0.35. Writing 100.0 forces the division result to be a decimal (float). Without this, every percentage would come out as 0.
ROUND(expression, 2) wraps the entire percentage formula and trims the result to exactly 2 decimal places. The second argument is the precision — how many digits after the decimal point you want.
The same formula works for open_perc: SUM(CASE WHEN 'open') * 100.0 / SUM(time_spent). For every age group, send_perc + open_perc will always add up to exactly 100.00.
ORDER BY ab.age_bucket sorts the output alphabetically by age group — 21-25 first, then 26-30, then 31-35.
Expected result: 21-25 → send 52.22%, open 47.78% | 26-30 → send 72.44%, open 27.56% | 31-35 → send 35.14%, open 64.86%. Older users open more, younger users send more.

▶ Practice this live ← Browse all questions