Lessons › SQL › Go further in SQL: Business questions

SQL lesson

Visit, cart, purchase: where do people drop off?

Visit → cart → purchase: how many people reached each step, and what share went on from the step before? Count people per step, put the steps in order (CASE gives each a number), then divide by the step before (LAG).

Example

A funnel: each step against the one before

WITH s AS (
  SELECT step, COUNT(DISTINCT user_id) AS people
  FROM site_events
  GROUP BY step
), o AS (
  SELECT step,
         people,
         CASE
           step
           WHEN 'visit' THEN 1
           WHEN 'cart' THEN 2
           ELSE 3
         END AS n
  FROM s
)
SELECT step,
       people,
       1.0 * people / LAG(people) OVER (ORDER BY n) AS rate
FROM o
ORDER BY n

Result:

Step by step

1. The first step has no step before it.

   WITH s AS (SELECT step, COUNT(DISTINCT user_id) AS people FROM site_events GROUP BY step), o AS (SELECT step, people, CASE step WHEN 'visit' THEN 1 WHEN 'cart' THEN 2 ELSE 3 END AS n FROM s) SELECT step, people, 1.0 * people / LAG(people) OVER (ORDER BY n) AS rate FROM o ORDER BY n

2. One row per person per step.

   WITH s AS (SELECT step, COUNT(DISTINCT user_id) AS people FROM site_events GROUP BY step), o AS (SELECT step, people, CASE step WHEN 'visit' THEN 1 WHEN 'cart' THEN 2 ELSE 3 END AS n FROM s) SELECT step, people, 1.0 * people / LAG(people) OVER (ORDER BY n) AS rate FROM o ORDER BY n

3. Every event counts.

   WITH s AS (SELECT step, COUNT(DISTINCT user_id) AS people FROM site_events GROUP BY step), o AS (SELECT step, people, CASE step WHEN 'visit' THEN 1 WHEN 'cart' THEN 2 ELSE 3 END AS n FROM s) SELECT step, people, 1.0 * people / LAG(people) OVER (ORDER BY n) AS rate FROM o ORDER BY n

4. COUNT(DISTINCT user_id), a step number with CASE, then LAG.

   WITH s AS (SELECT step, COUNT(DISTINCT user_id) AS people FROM site_events GROUP BY step), o AS (SELECT step, people, CASE step WHEN 'visit' THEN 1 WHEN 'cart' THEN 2 ELSE 3 END AS n FROM s) SELECT step, people, 1.0 * people / LAG(people) OVER (ORDER BY n) AS rate FROM o ORDER BY n

Practice

Try each one in your head first, then open the answer.

WITH s AS (SELECT step, COUNT(DISTINCT user_id) AS people FROM site_events GROUP BY step), o AS (SELECT step, people, CASE step WHEN 'visit' THEN 1 WHEN 'cart' THEN 2 ELSE 3 END AS n FROM s) SELECT step, people, 1.0 * people / LAG(people) OVER (ORDER BY n) AS rate FROM o ORDER BY n

WITH s AS (SELECT step, COUNT(DISTINCT user_id) AS people FROM site_events GROUP BY step), o AS (SELECT step, people, CASE step WHEN 'visit' THEN 1 WHEN 'cart' THEN 2 ELSE 3 END AS n FROM s) SELECT step, people, 1.0 * people / FIRST_VALUE(people) OVER (ORDER BY n) AS share_of_visits FROM o ORDER BY n

SELECT step, COUNT(DISTINCT user_id) AS people FROM site_events GROUP BY step

SELECT 1.0 * COUNT(DISTINCT CASE WHEN step = 'purchase' THEN user_id END) / COUNT(DISTINCT CASE WHEN step = 'visit' THEN user_id END) AS conversion FROM site_events

SELECT DISTINCT user_id FROM site_events WHERE step = 'cart' AND user_id NOT IN (SELECT user_id FROM site_events WHERE step = 'purchase')