Post Success After Failure
You are given the following tables:
post table:
post_user table
Write a SQL that shows the success rate of post (%) when the user's previous post had failed.
Your output should have the following columns: user_id and next_post_sc_rate (success rate of post when user’s previous post had failed). Order results by increasing next_post_sc_rate.
“For each user, among posts that immediately follow a failed post, what percentage of those posts are successful?”
Formula (per user)
Q = the set of that user’s posts whose previous post (same user) failed
Successes = count of posts in Q that are successful
next_post_sc_rate = Successes/Q * 100
If a user ends with a failure and has no next post, that failure does not enter the denominator (because there is no “next post” to evaluate).
Build “previous outcome” per user (CTE)
-
Partition rows by
user_id;order each user’s posts bypost_date, post_id. -
Use
LAG(is_successful_post)to fetch the previous post’s outcome asprev_success. -
Output per row:
user_id,is_successful_post(this post), andprev_success(prior post). -
Result: each post knows whether the immediately prior post succeeded/failed.
Filter to next-after-failure & compute rate
-
Keep only rows
where prev_success = FALSE(posts that follow a failure). -
Convert success to 1, failure to 0 with
CASE; take AVG(...)to get successes ÷ qualifying posts. -
Multiply by 100 and ROUND(..., 2) to report a percentage as
next_post_sc_rate. -
GROUP BY user_idto get one rate per user;ORDER BYto sort ascending.
WITH ordered AS (
SELECT
p.user_id,
p.is_successful_post,
LAG(p.is_successful_post) OVER (
PARTITION BY p.user_id
ORDER BY p.post_date, p.post_id
) AS prev_success
FROM post p
)
SELECT
user_id,
ROUND(AVG(CASE WHEN is_successful_post THEN 1.0 ELSE 0.0 END) * 100, 2) AS next_post_sc_rate
FROM ordered
WHERE prev_success = FALSE
GROUP BY user_id
ORDER BY next_post_sc_rate ASC;
I might be missing something but the solution, seems to be incorrect.
... , post_pairings AS ( SELECT ps.user_id, ps.post_seq_id AS fail_post_id, ps.post_seq_id + 1 AS next_post_id FROM post_seq AS ps WHERE ps.is_successful_post IS TRUE ) -- here ps.is_successful_post IS TRUE the condition should be FALSE -- in that way ps.post_seq_id is the actual failed post(fail_post_id) -- Additionally, at the end the join is assumming that the sequence id is going to match the post_id, which does not make sense given the partition of the window function is user_id SELECT pp.user_id, ROUND(SUM(CASE WHEN p2.is_successful_post THEN 1 ELSE 0 END) * 1.0 / COUNT(p2.is_successful_post), 2) AS next_post_sc_rate FROM post_pairings AS pp JOIN post AS p2 ON pp.next_post_id = p2.post_id GROUP BY 1 ORDER BY next_post_sc_rate ASC; -- in this case JOIN post_pairing AS pp2 ON pp.next_post_id = pp2.post_seq_id -- makes more sense to me