Write a query to return every user appearing in fn_events with Boolean viewed, added_to_cart and purchased flags. Each flag is TRUE if the user has any corresponding view, add_to_cart or purchase event, otherwise FALSE.
Ignore event order and repeated occurrences when setting flags. Users with only unrelated event types remain with three FALSE values. Never return NULL flags.
Ordering: Sort by user_id ascending.
Returnuser_idviewedadded_to_cartpurchased
About this problem
You practise the pivot that nearly every funnel query starts from: an event stream in, one row per user out, with a column per step. Three aggregates with a filter each, and the result is a table you can count any way you like afterwards. The skill is keeping the group wide while the columns are narrow.
The two mistakes worth meeting are both about losing information. Filtering the event stream to the three funnel types before grouping removes any user who never touched the funnel, and those users are not noise – “how many people in the product never even looked at a listing” is a real question, and a query that drops them cannot answer it. The second is returning NULL where the answer is false. A missing step is a known fact, not an unknown one, and the difference matters as soon as anything downstream counts: count(*) FILTER (WHERE purchased) is right either way, but WHERE NOT purchased silently skips the NULL rows, and a three-way boolean in a report is a question waiting to be asked. The other thing to notice is that this problem deliberately ignores order, which the next few problems in the set do not. f04 bought before adding to cart, and here that is two true values; in problem 10 it is a user who never completed the funnel. Both readings are correct and they are answers to different questions, so the query has to say which one it means.
Read the lesson: Funnels & Conversion (P22) →