Showing a count of visitors who are in the database more than 3 times?

[clone45]

Hello!

I've got a large database (500,000+ rows) of website visitor data. Most of my queries have gone smoothly so far, but this one eludes me.

I've figured out how to display all the unique visitors who've visited the site more than 2 times. The query looks like this:

SELECT visitor_id, Count(visitor_id) as number_of_visits 
FROM entry_point_marketing_data
GROUP BY visitor_id
HAVING Count(visitor_id) > 2

visitor_id	number_of_visits

2697 		3
2700 		6
2707 		3
... etc...

My next goal is to count "The number of visitors who've visited the site more than 2 times." I could programmatically tally the rows, but I thought I'd try doing it in the query. My attempt looked like this:

 SELECT count(*)
 FROM entry_point_marketing_data
 WHERE visitor_id IN
 (
   SELECT visitor_id, Count(visitor_id) as number_of_visits 
   FROM entry_point_marketing_data
   GROUP BY visitor_id
   HAVING Count(visitor_id) > 2
 )

This seems like a good solution, but the query never finished. The database hung. I suspect that there's something very intensive about the second query that I'm not seeing.

Any ideas?

Thanks!
- Bret

 

[gmmastros]

Select Count(visitor_id) As VisitorCount
From   (
       SELECT visitor_id, 
              Count(visitor_id) as number_of_visits 
       FROM entry_point_marketing_data
       GROUP BY visitor_id
       HAVING Count(visitor_id) > 2
       ) As A

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[clone45]

Excellent! It works great! Thanks!

 

[gmmastros]

Glad to hear it. You could modify it to remove the inner count because it is not necessary for this query.

Select Count(visitor_id) As VisitorCount
From   (
       SELECT visitor_id,
              [s]Count(visitor_id) as number_of_visits [/s]
       FROM entry_point_marketing_data
       GROUP BY visitor_id
       HAVING Count(visitor_id) > 2
       ) As A

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom