MySQL get number of retained users from previous month (issue with SUM)

Question

I'm trying to get the total number of users who transacted last month and have come back so far this month.

At the moment, my code works with the exception of not being able to sum up the count of unique user IDs. Here's what I have

SELECT * from
(
   SELECT tx_date, count(distinct user_id) 
   from (
      SELECT p.user_id, date(min(p.timestamp)) tx_date 
      from purchase p
      where date(timestamp) between '2018-04-01' and '2018-04-30'
      and user_id in (SELECT * from analytics_march_mau)
      group by 1) b
   group by 1) t1
JOIN (
   select distinct date(timestamp) dates 
   from purchase 
   where date(timestamp) between '2018-04-01' and '2018-04-30') a
on a.dates >= t1.tx_date
group by 1

Which gives me the below results

Problem now is that I need to change the viewed data to only return the tx_date and sum of the second column

This should change

SELECT * from

To something like

SELECT dates, SUM(count)

But when I try this I get an error "Unknown column 'count' in 'field list'" I have no idea what that column is called

PS: I got the idea of this query from a friend, if there's an easier way to get this done, I'd be happy to try that instead.

So what do you want your results to look like? Do you want a single date like April 2018 with a total count for that month? — mcv, Apr 09 '18 at 22:56
Why do you have the second query in there by using the `JOIN`? What relevance does it have to the final outcome? — mcv, Apr 10 '18 at 10:17

score 1 · Answer 1 · answered Apr 10 '18 at 10:53

1

I removed a lot of your nested queries. I have a SQLFiddle implemented without the use the arguement user_id in (SELECT * from analytics_march_mau)

SELECT COUNT(distinct user_id), DATE_FORMAT(timestamp, "%Y-%m") as date
FROM purchase
WHERE year(timestamp)=2018 AND month(timestamp)=4
AND user_id in (SELECT * FROM analytics_march_mau);

Without testing the part I left out, I believe this should assist you.

Areas you could improve upon your question is to format your query. Add a snippet of data not a photo. Also provide an SQLFiddle if possible.

answered Apr 10 '18 at 10:53

mcv

1,380
3
16
41

Thanks @mcv for the help. This gives me the total count of returning users in the current month as a total figure. How best can I modify your snippet to give me the cumulative count on a weekly or daily basis? – Osoba Osaze Apr 10 '18 at 14:45
that is outside the scope of your question. did you try searching for it? Here is one potential solution to your [new question](https://stackoverflow.com/questions/1736010/how-to-group-by-week-in-mysql) – mcv Apr 10 '18 at 16:20

Bohemian · Answer 2 · 2018-04-10T18:58:28.263

1

The simplest way I can think of is:

SELECT COUNT(distinct a.user_id)
FROM purchase a
WHERE date(a.timestamp) between '2018-03-01' and '2018-03-31'
JOIN purchase b on b.user_id = a.user_id
    AND date(b.timestamp) between '2018-04-01' and '2018-04-30'

This does an inner join of purchase with itself on user_id where each side of the join is each month.

There are smarter ways of getting the dates of the previous and current months, but the essence of the query will be the same.

edited Apr 10 '18 at 18:58

answered Apr 10 '18 at 11:04

Bohemian

412,405
93
575
722

Yeah, I had noticed those brackets, after the dates. I took them out when running the query but still got a syntax error. Wasn't sure what else to try to fix it – Osoba Osaze Apr 10 '18 at 19:29

MySQL get number of retained users from previous month (issue with SUM)

2 Answers2