0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

WITH a AS (
select

DATEFROMPARTS(
  YEAR(CreationDate),
  MONTH(CreationDate), 1) AS date_
  ,
count (id) as num_of_new_users
from users
group by 
DATEFROMPARTS(YEAR(CreationDate),
MONTH(CreationDate),01)
)

SELECT a.*
, a2.num_of_new_users AS num_of_new_users_year_ago
, a3.num_of_new_users AS num_of_new_users_two_year_ago

FROM a
LEFT JOIN a a2 ON a.date_ = DATEADD(year, 1, a2.date_)
LEFT JOIN a a3 ON a.date_ = DATEADD(year, 2, a3.date_)

fork query

created jan 6 19 diklapinhasi