MySQL query to find number of unique new visitors per week

5
votes

I have 3 tables in MySQL: 1) page (id, title) 2) visitor (id, name) 3) page_visit (page_id, visitor_id, timestamp_of_visit)

Visitors can visit pages multiple times, across several days. Hence, while we will have one row for a page, and one row for a visitor, we can have several page_visit rows, each with a timestamp of the visit.

I'm trying to find the number of unique visitors, by week. I know how to get the 'by week count' query for non-uniques (i.e. 'how many visitors did I see each week'). I'm not sure how to pick the unique visitors by week, though, with the visitor showing up on the list ONLY the first time they are ever seen.

-----------    -----------    ----------------------------
| page    |    | visitor |    | page_visit               |
-----------    -----------    ----------------------------
|id |title|    |id |name |    |pid|vid|timestamp of visit|
-----------    -----------    ----------------------------
| 1 | p1  |    | 1 | v1  |    | 1 | 1 | 02-18-2016:08:30 |
| 2 | p2  |    | 2 | v2  |    | 1 | 1 | 02-18-2016:10:00 |
| 3 | p3  |    | 3 | v3  |    | 1 | 3 | 02-20-2016:23:45 |
| 4 | p4  |    | 4 | v4  |    | 2 | 3 | 02-22-2016:07:30 |
| 5 | p5  |    | 5 | v5  |    | 3 | 1 | 02-23-2016:08:30 |
| 6 | p6  |    | 6 | v6  |    | 3 | 6 | 02-24-2016:09:30 |

What the result set should show:

------------------------
| results              |
------------------------
| Week of    | Net new |
------------------------
| 02-15-2016 | 2       |
| 02-22-2016 | 1       |

As mentioned, I can figure out how to show ALL visitors by week. I'm not sure how to get the unique visitors.

I tried doing a min(timestamp of visit), but, based on where I tried it, it returned the lowest timestamp across all rows (understandably...).

Any help would be much appreciated!

2
mysqlsql

2 Answers

4
votes

This is a tricky question when you first encounter it. It requires two levels of aggregation. The first gets the first visit for each visitor, the second summarizes by time. The following does the summary by day:

select date(minvd), count(*) as numvisitors
from (select vid, min(visitdate) as minvd
      from page_visit pv
      group by vid
     ) v
group by date(minvd)
order by date(minvd);

Translating to weeks is always a bit tricky -- do they begin on Mondays? End on Saturdays? On Fridays? (I've seen all of these.) However, the above is additive, so you can just add all the values for a given week to get your value.

2
votes

In case you want to do this without a subquery:

SELECT
    <week>,
    COUNT(DISTINCT PV.vid)
FROM
    Page_Visit PV
LEFT OUTER JOIN Page_Visit PV2 ON
    PV2.vid = PV.vid AND
    PV2.visit_date < PV.visit_date
WHERE
    PV2.vid IS NULL
GROUP BY
    <week>

As Gordon mentions, how you determine the week can be tricky. Just add in that calculation where you see <week>. Personally, I like to use a Calendar table for that kind of functionality, but it's up to you. You can run any expressions directly against PV.visit_date to determine it.