Post Success

In the table below (post_events table), column event_name represents either (‘enter’, ‘post’, ‘cancel’) for when a user starts a post (enter), ends up canceling it (cancel), or ends up posting it (post).

column_name type
user_id int
created_at datetime
event_name varchar

Write a query to get the post success rate for each day over the past week.

Sample data:

user_id created_at event_name
123 2019-01-01 enter
123 2019-01-01 post
456 2019-01-02 enter
456 2019-01-02 cancel

Solution

Let’s first define the post success rate on a day (SR_daily).

SR_daily = # users posting a post / # users starting a post (per day)

SELECT 
	created_at::date day
	, COUNT(DISTINCT CASE WHEN event_name = 'enter' THEN user_id END) post_start
	, COUNT(DISTINCT CASE WHEN event_name = 'post' THEN user_id END) post_complete
	, post_complete/post_start sr_daily
FROM post_events
WHERE created_at > DATEADD('d', -7, current_date)
GROUP BY 1