FORUM SQL Habit query in lesson 130

query in lesson 130

Hi Anatoli!

I have a question about the funnel analysis query in lesson 129, namely this one:

SELECT
  COUNT(h.visitor_id) AS homepage_pvs,
  COUNT(b.visitor_id) AS book_page_pvs
FROM web_analytics.pageviews h
LEFT JOIN web_analytics.pageviews b
  ON h.visitor_id = b.visitor_id
    AND b.url LIKE '%/books/%'
    AND (
      b.referer_url = 'https://www.bindle.com/'
      OR b.referer_url LIKE 'https://www.bindle.com/?%'
    )  
    AND b.created_at BETWEEN h.created_at AND h.created_at + '30 minutes'::interval
WHERE
  h.url = 'https://www.bindle.com/'
  OR h.url LIKE 'https://www.bindle.com/?%'

My question is: Is the

AND (
  b.referer_url = 'https://www.bindle.com/'
  OR b.referer_url LIKE 'https://www.bindle.com/?%'
)

bit necessary, given that we filter using WHERE further down? Is there a difference? They seem to give the same results.

REPLIES

Hi Erik ☀

That’s a really great question! TL;DR – yes, it’s absolutely necessary.

Funnel pageviews

In the lesson we’re working with the funnel where users go to the homepage and then go to the book page (by clicking a link, that’s why the referrer_url will be present for such pageviews).

Our goal is to calculate the percentage of book pageviews that came from the homepage (that’s why we need that additional referrer_url filter).

Why answers are the same? 🤔

Short answer: because of the dataset.

It’s unlikely, of course, but you can imagine that no one visited book pages directly yet (maybe they weren’t index by search engines properly).

Hope that helps 🍻

WRITE A REPLY