CODEWITHSUNDEEP
mysqlsql
NullHypothesis
NullHypothesis

Reputation: 4506

Growth for each quarter+year in SQL over my user table

I am using MYSQL and I have a User database table where my registered users are stored. I'd love to see how many users have registered on an increasing timeline for each quarter. So maybe Q1 2016 I had 1000 users total, then in Q2 2016 I had 2000 users register, in Q3 2016 4000 total users registered, etc (so I want to see the increase, not just how many registered in each quarter)

From another Stack Overflow post, I was able to create a query to see it by each day:

select u.created, count(*)
from (select distinct date(DateCreated) created from `Users`) u
join `Users` u2 on u.created >= date(u2.DateCreated)
group by u.created

and this works for each day, but I'd like to now group it by quarter and year. I tried using the QUARTER(d) function in mysql and even QUARTER(d) + YEAR(d) to concat it but I still can't get the data right (The count(*) ends up producing incredibly high values).

Would anyone be able to help me get my data grouped by quarter/year? My timestamp column is called DateCreated (it's a unix timestamp in milliseconds, so I have to divide by 1000 too)

Thanks so much

Upvotes: 1

Views: 284

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I would suggest using a correlated subquery -- this allows you to easily define each row in the result set. I think this is the logic that you want:

select dates.yyyy, dates.q,
       (select count(*)
        from Users u
        where u.DateCreated < dates.mindc + interval 3 month
       ) as cnt
from (select year(DateCreated) as yyyy, quarter(DateCreated) as q 
             min(DateCreated) as mindc
      from Users u
      group by year(DateCreated), quarter(DateCreated)
     ) dates;

Upvotes: 2

Related Questions