'Write Query to display look like in image

The table provided shows all new users signing up on a specific date in the format YYYY-MM-DD.

Your query should output the change from one month to the next. Because the first month has no preceding month, your output should skip that row. Your output should look like the following table.

My table data

Table data:

ID    DateJoined
1     2017-01-06
2     2017-01-12
3     2017-01-16
4     2017-01-25
5     2017-02-05
6     2017-02-07
7     2017-02-21
8     2017-03-05
9     2017-03-07
10    2017-03-14
11    2017-03-16
12    2017-03-25
13    2017-03-25
14    2017-03-25
15    2017-03-25
16    2017-03-26
17    2017-04-05
18    2017-04-14
19    2017-04-21
20    2017-05-07
23    2017-05-14
24    2017-05-16
25    2017-05-25
26    2017-05-25
27    2017-05-25
28    2017-05-25

Enter image description here

I want this output: count all records from every month and subtract it from the next month record.

This is my query:

SELECT
   MONTH(L.joindate),
   COUNT(L.joindate) -  COUNT(R.joindate),
   MONTH(R.joindate),
   COUNT(R.joindate)
FROM
   userlog       AS L
LEFT JOIN
   userlog       AS R
      ON MONTH(R.joindate)= (SELECT MIN(MONTH(joindate)) FROM userlog WHERE MONTH(joindate) < MONTH(L.joindate))
GROUP BY (MONTH(L.joindate)),(MONTH(R.joindate));

1

mysqlsqlcountsubqueryaggregate-functions


Solution 1:[1]

Use lag(), available in MySQL 8.0:

select date_format(joindate, '%Y-%m-01') joinmonth,
    count(*) - lag(count(*), 1, 0) over(order by date_format(joindate, '%Y-%m-01')) m2m
from userlog
group by joinmonth

Note that I changed the logic to truncate dates to the first of month to use date_format().

In earlier versions, you can use a correlated subquery:

select date_format(joindate, '%Y-%m-01') joinmonth,
    count(*) - (
        select count(*)
        from userlog l1
        where l1.joindate >= date_format(l.joindate, '%Y-%m-01') - interval 1 month
          and l1.joindate <  date_format(l.joindate, '%Y-%m-01')
    ) m2m
from userlog l
group by joinmonth
LIMIT 12 OFFSET 1

Solution 2:[2]

You need to use Lag. Also, since it says you need to skip the first row so I have used the not null condition. I believe this query should work.

select 
  Month, 
  MonthToMonthChange 
from 
  (
    select 
      m_name as Month, 
      (total_id - diff) as MonthToMonthChange 
    from 
      (
        select 
          total_id, 
          m_name, 
          Lag(total_id, 1) OVER(
            ORDER BY 
              m_num ASC
          ) AS diff 
        from 
          (
            select 
              MonthNAME(DateJoined) m_name, 
              Month(DateJoined) m_num, 
              count(*) total_id 
            from 
              maintable 
            Group by 
              m_name, 
              m_num
          ) as first_subquery
      ) as second_subquery
  ) as final_query 
where 
  MonthToMonthChange IS NOT NULL;

Solution 3:[3]

select MONTHNAME(UL1.DateJoined) as MONTH, count(UL1.DateJoined) - ( select count(UL2.DateJoined) from tablename UL2 where MONTH(UL2.DateJoined )=MONTH(UL1.DateJoined) -1 ) as MonthToMonthChange from tablename UL1 where Month(UL1.DateJoined)!=1 Group by MONTHNAME(UL1.DateJoined) order by UL1.DateJoined ASC;

   
  [1]: https://i.stack.imgur.com/BXXDb.png

Solution 4:[4]

//I tried this and it worked

select date_format(DateJoined, CONCAT('%M')) as Month,
count(*) - lag(count(*), 1, 0) over(order by date_format(DateJoined, CONCAT('%m'))) MonthToMonthChange
from maintable_OKLOT
group by Month
limit 12 offset 1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Chirag Jhawar
Solution 2 Denis Alimov
Solution 3
Solution 4

Related Questions