SQL Funnel Analysis Interview Questions

Funnel questions test whether you can measure users or sessions at several conversion stages without double-counting event rows. The key move is often count(distinct ...), because one session can produce many event records. If the business question requires a strict sequence, you also need logic that verifies stage order rather than merely counting stage reach.

Before writing SQL, clarify the entity being measured: sessions, users, accounts, orders, or visitors. The same event table can answer different business questions depending on that grain.

Common funnel interview prompts

Answer each one yourself before you look: write your query or your reasoning in the box, or open the playground and run it. Then reveal the model answer and compare. Nothing you type is saved or sent anywhere.

1. Count how many sessions reached each funnel stage.

   Your answer Try it in the playground →
   Reveal a model answer

   select event_name,
          count(distinct session_id) as sessions
   from events
   group by event_name
   order by sessions desc, event_name

   count(distinct session_id), not count(*): a session that fires add_to_cart twice reached the stage once.

2. Calculate purchase rate by marketing channel.

   Your answer Try it in the playground →
   Reveal a model answer

   select s.channel,
          count(distinct s.session_id) as sessions,
          count(distinct case when e.event_name = 'purchase' then s.session_id end) as purchasing_sessions,
          round(100.0 * count(distinct case when e.event_name = 'purchase' then s.session_id end)
                / count(distinct s.session_id), 1) as purchase_rate
   from sessions as s
   left join events as e on e.session_id = s.session_id
   group by s.channel
   order by purchase_rate desc, s.channel

   Sessions are the base, so start there and LEFT JOIN events; channels with no purchases then show 0 instead of vanishing. 100.0 keeps the division fractional on engines that truncate integers.

3. Find the largest drop-off between signup, checkout, and purchase.

   Your answer Try it in the playground →
   Reveal a model answer

   with reach as (
     select event_name,
            count(distinct session_id) as sessions
     from events
     where event_name in ('signup', 'checkout', 'purchase')
     group by event_name
   ),
   ordered as (
     select event_name,
            sessions,
            lag(sessions) over (
              order by case event_name
                         when 'signup' then 1
                         when 'checkout' then 2
                         else 3
                       end
            ) as previous_stage
     from reach
   )
   select event_name,
          previous_stage,
          sessions,
          previous_stage - sessions as drop_off
   from ordered
   where previous_stage is not null
   order by drop_off desc

   Put the stages in funnel order with a CASE, then lag() compares each stage with the one before. Point out that this is stage reach, not a strict funnel: a session can check out without a recorded signup, so a later stage can even be larger.

4. Compare conversion for logged-in versus anonymous sessions.

   Your answer Try it in the playground →
   Reveal a model answer

   select case when s.user_id is null then 'anonymous' else 'logged in' end as visitor,
          count(distinct s.session_id) as sessions,
          count(distinct case when e.event_name = 'purchase' then s.session_id end) as purchasing_sessions
   from sessions as s
   left join events as e on e.session_id = s.session_id
   group by visitor
   order by visitor

   Segment with a CASE on user_id being NULL, keep sessions as the base, and count purchasing sessions per segment. Add the rate once both counts look right.

5. Explain why counting event rows can overstate conversion.

   Your answer
   Reveal a model answer

   One session can log the same event several times: a double click, a retried checkout. count(*) counts those records, while the question is how many sessions or users reached the stage. Count distinct entities, and state which entity the denominator uses.

select event_name,
       count(distinct session_id) as sessions,
       round(
         100.0 * count(distinct session_id) /
         (select count(*) from sessions),
         1
       ) as pct_of_sessions
from events
group by event_name
order by sessions desc, event_name

What matters

A stage-reach metric usually asks "did the entity record this event at least once?" That means count(distinct session_id), not count(*). Counting rows answers "how many event records exist", which can overstate reach when sessions repeat actions. A true sequential funnel needs additional ordering or timestamp logic.

For segmented conversion, keep the base table on the left side of the join. Starting from sessions preserves channels with no purchases, while starting from purchase events drops those zero-conversion groups.

select s.channel,
       count(distinct s.session_id) as sessions,
       count(distinct e.session_id) filter (
         where e.event_name = 'purchase'
       ) as purchasing_sessions
from sessions as s
left join events as e on e.session_id = s.session_id
group by s.channel
order by purchasing_sessions desc, s.channel

How to talk it through

Say: "The result is one row per channel. Sessions are the base population, so I will start from the sessions table, left join events, count distinct sessions overall, and count distinct sessions that reached purchase."

Call out the denominator. A percentage can use all sessions, sessions within a channel, or sessions that reached the previous funnel step. Those are different metrics.

Practice next

Work through the conversion stage reach, channel conversion mission, and retention cohort mission. Review integer division before writing conversion rates.