Post Success By Age Group
Medium
You are given the following tables:
Sample Data
post table:
post_user table:
Examples
Output: post_month | ya_sc_rate | non_ya_sc_rate | diff -----------|------------|----------------|------ 1 | 1.00 | 0.00 | 1.00 2 | 0.00 | 1.00 | -1.00
Explanation:
- Month 1: Young adults (users 1, 3 — ages 16 and 12) had 2 posts, both successful →
ya_sc_rate= 1.00. Non-young adult (user 2, age 34) had 1 post, unsuccessful →non_ya_sc_rate= 0.00.diff= 1.00 - 0.00 = 1.00 - Month 2: Young adult (user 1, age 16) had 1 post, unsuccessful →
ya_sc_rate= 0.00. Non-young adults (users 4, 5) had 2 posts, both successful →non_ya_sc_rate= 1.00.diff= 0.00 - 1.00 = -1.00
Results are ordered by ascending month.
Write a SQL that shows the difference in success rate of posting dropoff between young adults (age 0-18) and non young adults by each week.
The output should look like per month, the young adult success rate for posting, non-young adult success rate for posting and dropoff between the two.
Your output should contain the following columns: post_month, ya_sc_rate (young adults success rate), non_ya_sc_rate (non young adults success rates), diff (difference between the ya_sc_rate and non_ya_sc_rate rounded to 2 decimal place). Order by ascending month.
WITH agg_metrics_segment AS (
SELECT
-- For MySQL
-- MONTH(p.post_date) AS post_month
strftime('%m', p.post_date) AS post_month,
(CASE WHEN u.age <= 18 THEN 'YA' ELSE 'Non-YA' END) AS age_bracket,
SUM(p.is_successful_post) AS post_success,
COUNT(p.is_successful_post) AS post_attempt,
SUM(p.is_successful_post) * 1.0 / COUNT(p.is_successful_post) AS post_success_rate
FROM post AS p
JOIN post_user AS u
ON p.user_id = u.user_id
GROUP BY 1,2
)
,ya AS
(SELECT * FROM agg_metrics_segment WHERE age_bracket = 'YA')
,non_ya AS
(SELECT * FROM agg_metrics_segment WHERE age_bracket = 'Non-YA')
SELECT
t1.post_month,
t1.post_success_rate AS ya_sc_rate,
t2.post_success_rate AS non_ya_sc_rate,
ROUND(t1.post_success_rate - t2.post_success_rate, 2) AS diff
FROM ya AS t1
JOIN non_ya AS t2
ON t1.post_month = t2.post_month
ORDER BY t1.post_month ASC
with young_succ_rate as( select strftime('%m', post_date) AS post_month, round(sum(is_successful_post)*1.0/count(is_successful_post),2)as ya_sc_rate from post where user_id in (select user_id from post_user where age between 0 and 18) group by post_month ), non_young_succ_rate as( select strftime('%m', post_date) AS post_month, round(sum(is_successful_post)*1.0/count(is_successful_post),2)as non_ya_sc_rate from post where user_id in (select user_id from post_user where age>18) group by post_month ) select a.*, b.non_ya_sc_rate, round(a.ya_sc_rate - b.non_ya_sc_rate,2) as diff from young_succ_rate a left join non_young_succ_rate b on a.post_month = b.post_month order by a.post_month