CODEWITHSUNDEEP
mysqlsql
Brett Wyker
Brett Wyker

Reputation: 15

Can I count across months for only new individuals?

I'm trying to get a count of the individuals who have attended events across the months of the year. Each individual has a unique ID in a MariaDB and I can get a total count for each month using COUNT(DISTINCT MEMBER_ID) and grouping by the event month. However, I would like to know how many people in each month's total are new (e.g., first event attended) and how many are returning.

For example, if have a table like this...

EVENT_ID    MEMBER_ID    EVENT_DATE

1001        100          1/1/2019
1001        123          1/1/2019
1001        222          1/1/2019
1002        100          1/4/2019
1002        123          1/4/2019
1002        333          1/4/2019
1003        100          2/12/2019
1003        444          2/12/2019
1004        123          2/20/2019
1004        555          2/20/2019

...this code will give me a count of the individuals who have attended each month.

SELECT MONTH(EVENT_DATE) as EVENT_MONTH, COUNT(DISTINCT MEMBER_ID) as ID_COUNT FROM `table` 

WHERE YEAR(EVENT_DATE) = YEAR(CURRENT_TIMESTAMP)

GROUP BY MONTH(EVENT_DATE)

Like this...

EVENT_MONTH    ID_COUNT 
1              4
2              4

At the very least, what I would like to know is how many people are new each month, so 

EVENT_MONTH    ID_COUNT 
1              4
2              2

But, if possible, I would love to get a count of the people who came in January continued to attend in Feb, and how many people who attended in Feb attended in March, etc.

Any help would be much appreciated!

Upvotes: 0

Views: 56

Answers (2)

Carsten Massmann
Carsten Massmann

Reputation: 28206

You could select the individual monthly events into a temporary table (the closest you can get to a CTE), together with the member ids and a flag (frst) that tells you, whether this was a "first" visit:

CREATE TEMPORARY TABLE IF NOT EXISTS tmp AS( 
  select member_id mid ,year(event_date)*100+month(event_date) yemo,
  CASE WHEN (select min(event_date) 
             from tbl where member_id=t1.member_id)=event_date 
       THEN 1 END frst
  FROM tbl t1
);

Once you have this (temporary) table you can then group by month and count the (distinct!) members that have a "first" visit in that month:

SELECT count(distinct mid) cnt,yemo from tmp where frst=1 group by yemo

Please note that I combine year()and month() of each date into a single value yemo, as a month alone would only be unique within a single calender year.

You can see a working demo here: https://rextester.com/GNBG69033

The result you get is the following:

    yemo    cnt
1   201901  4
2   201902  2

Edit:

If we want to know the count of members re-visiting in the month after their first visit we need a slightly more refined approach:

CREATE TABLE tmp AS( 
  select member_id mid ,year(event_date) ye,month(event_date) mo,
         (year(event_date)-2000)*12+month(event_date) yemo,
         CASE WHEN (select min(event_date) from tbl
                    where member_id=t1.member_id)=event_date 
              THEN 1 END frst
  from tbl t1
);

select ye,mo, sum(cfrst) firsts, sum(pfrst) seconds FROM (
 SELECT c.ye, c.mo, c.yemo cyemo, c.mid, max(c.frst) cfrst, max(p.frst) pfrst
 FROM tmp c 
 LEFT JOIN tmp p ON p.mid=c.mid and p.yemo=c.yemo-1 
 group by c.yemo, c.mid 
) t 
group by ye,mo
order by ye,mo

Instead of a temporary table we now need a "proper" table, as it needs to be referred to several times. The yemo column is defined differently now, so we can link to a "previous month" more easily.

The result is the following:

    ye      mo  firsts  seconds
1   2019    1   4       NULL
2   2019    2   2       2

https://rextester.com/CFNT26170

Upvotes: 0

alexherm
alexherm

Reputation: 1362

Select member ID with min(eventDate) into a new table. Then you can use another query with new table to identify whether or not it is the members first event.

Upvotes: 1

Related Questions