Post Success After Failure

“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 by post_date, post_id.

• Use LAG(is_successful_post) to fetch the previous post’s outcome as prev_success.

• Output per row: user_id, is_successful_post (this post), and prev_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_id to get one rate per user; ORDER BY to sort ascending.