Getting the number of users for this year and last year in SQL query

Question

My table structure like this

root_tstamp	userId
2022-01-26T00:13:24.725+00:00	d2212
2022-01-26T00:13:24.669+00:00	ad323
2022-01-26T00:13:24.629+00:00	adfae
2022-01-26T00:13:24.573+00:00	adfa3
2022-01-26T00:13:24.552+00:00	adfef
...	...
2021-01-26T00:12:24.725+00:00	d2212
2021-01-26T00:15:24.669+00:00	daddfe
2021-01-26T00:14:24.629+00:00	adfda
2021-01-26T00:12:24.573+00:00	466eff
2021-01-26T00:12:24.552+00:00	adfafe

I want to get the number of users in the current year and in previous year like below using SQL.

Date	Users	previous_year
2022-01-01	10	5
2022-01-02	20	15

and the query I have used is:

with base as (
  select 
    date(root_tstamp) as current_date
    , count(distinct userid) as signup_counts
  from table1
  group by 1
)
select 
  t1.current_date
  , t1.signup_counts as signups_this_year
  , t2.signup_counts as signups_last_year
  , t1.signup_counts - t2.signups_counts as difference
  from base t1 
  left join base t2 on t1.current_date = t2.current_date + interval '1 year'
    group by t1.current_date
    order by t1.current_date Desc  

But I getting this error: ERROR: column t2.signups_counts does not exist

Answer 1

It's because you have t2.signup_counts is misspelled as t2.signups_counts.

Another note is that your query only has a GROUP BY on current_date and since the other columns are not aggregates you've to include these columns too.

Here is the modified query:

    with base as (
  select 
    date(root_tstamp) as current_date
    , count(distinct userid) as signup_counts
  from table1
  group by 1
)
select 
  t1.current_date
  , t1.signup_counts as signups_this_year
  , t2.signup_counts as signups_last_year
  , t1.signup_counts - t2.signup_counts as difference
  from base t1 
  left join base t2 on t1.current_date = t2.current_date - interval '1 year'
    group by t1.current_date, t2.signup_counts, t1.signup_counts
    order by t1.current_date Desc