user3094909 user3094909 - 1 month ago 17
MySQL Question

Cohort analysis by weeknumber

I want to perform a last year's cohort analysis by weeknumber

The output would be:

weeknumber session_count acq_count

37 5 3

43 2 2


...

MySql thus far:

SELECT WEEK(datetime) AS weeknumber, COUNT(*) AS session_count,
acquisition as acq_count=(SELECT COUNT(*) FROM sessions WHERE acquisition=1 )
FROM sessions WHERE `datetime` >= NOW()- INTERVAL 365 DAY
GROUP BY weeknumber


Challenge: how to also get the count of rows matching acquisition=1 in the same sql query?

Thanks for your thoughts!
Fred

Answer

I think you want this query

SELECT WEEK(datetime) AS weeknumber, COUNT(*) AS session_count, 
   SUM(acquisition=1) as acq_count 
FROM sessions WHERE `datetime` >= NOW()- INTERVAL 365 DAY 
GROUP BY weeknumber