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...

what is your expected result ? you should also show your 1st and 2nd query result. — Zakir Hossain, Jan 04 '19 at 23:38
*I'd like my X axis to be the day of the month and the Y to be the number of users.* : that's exactly what the first query seems to do... — GMB, Jan 04 '19 at 23:42
Hi Trevor. You tagged this question 'postgresql' and 'sql'. If you want to receive advice from users with db knowledge who are not familiar with redash, it would be useful to add how the output of your query should look like. — clamp, Jan 04 '19 at 23:50
And perhaps you are looking for something like date_part('day' , input) — clamp, Jan 04 '19 at 23:57
@ZakirHossain Good suggestions. I've updated my question for you guys to see. — , Jan 05 '19 at 01:35

score 1 · Accepted Answer · answered Jan 05 '19 at 01:57

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

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

1 Answers1