Interview Question Bank · Funnel per user · bool_or · COUNT FILTER
5. View-to-purchase funnel for two products
MediumSocial mediaQuestion #255
Concept: Funnel per user · bool_or · COUNT FILTERshow
A funnel counts people, not events, and a person counts at a step only if they also passed the steps before it. Collapse the log to one row per user (and product) with a flag per step, such as bool_or(action = 'view'), then count users whose flags are all true up to that step. Conversion is the last step's users divided by the first step's users, in decimal math.
Example (different data):
WITH v AS (
SELECT visitor_id,
bool_or(page = 'pricing') AS saw_pricing,
bool_or(page = 'signup') AS signed_up
FROM web_visits GROUP BY visitor_id
)
SELECT COUNT(*) FILTER (WHERE saw_pricing) AS pricing,
COUNT(*) FILTER (WHERE saw_pricing AND signed_up) AS signups
FROM v;Your task
Loopline, a photo-sharing app, opened an in-app shop with two products in May 2025. The commerce team wants a view, add to cart, purchase funnel for each one. Some people buy from a saved link without viewing the product page; they skipped the funnel and don't count in it.
For each product, return product, viewers (users who viewed it), carted (viewers who also added it to cart), purchasers (carted users who also bought it) and conversion_pct (purchasers as a % of viewers, rounded to 1 decimal). Count each user once per product. Sort by product.
Starting Postgres in your browser…
Checking your access…