How to combine two different counts from two different date ranges sql

Question

I'll try to keep this simple.

I have two queries that work just fine, they both count how many users signed up that day between a specific date range.

Query 1 - gets a list of users that signed up for each day a year from today. Here is a picture of the outcome.

SELECT users.created::date,
       count(users.id)
FROM users
WHERE users.created::date < now() - interval '12 month'
  AND users.created::date > now() - interval '13 month'
  AND users.thirdpartyid = 100
GROUP BY users.created::date
ORDER BY users.created::date

Query 2 - gets a list of users that signed up for each day a month ago from today. Here is a picture of this outcome.

SELECT users.created::date,
       count(users.id)
FROM users
WHERE users.created::date > now() - interval '1 month'
  AND users.thirdpartyid = 100
GROUP BY users.created::date
ORDER BY users.created::date

What I'm stuck on is how can I combine these two queries so that I could create a stack bar graph on my redash website. They are obviously both different years but I'd like my X axis to be the day of the month and the Y to be the number of users. Thank you.

Edit:

Here is an example output that I think would work perfectly for me.

| Day of the month  | Users signed up December 2017 | Users signed up December 2018
|------------------ | ----------------------------- | -----------------------------|
|         01                       45                                56
| ----------------- | ----------------------------  | -----------------------------|
|         02                       47                                32
| ----------------- | ----------------------------  | -----------------------------|

etc...

Answer 1

You could try using filters. I took the liberty to select the day of month as you seem to want that rather than the full date.

SELECT date_part('day', users.created::date) as day_of_month,
       count(users.id) FILTER (
           WHERE users.created::date < now() - interval '12 month'
           AND users.created::date > now() - interval '13 month') AS month_12,
       count(users.id) FILTER (
           WHERE users.created::date > now() - interval '1 month') AS month_1
FROM users
WHERE (
       (
            users.created::date < now() - interval '12 month'
        AND users.created::date > now() - interval '13 month'
       ) OR users.created::date > now() - interval '1 month'
      )
  AND users.thirdpartyid = 100
GROUP BY day_of_month
ORDER BY day_of_month