combining multiple count queries in sql

Question

i have a user's table with 3 columns that i'm trying to do counts on and then group by created_at date.

the columns: 'created_at', 'answers_count', 'questions_count'

how do i combine these 3 queries into one and have all the counts grouped by the same created_at date?

here're the 3 separate queries:

-- daily new signups
SELECT date_trunc('day', users.created_at) AS signup_date, count(*) AS new_users
FROM users
GROUP BY signup_date
ORDER BY signup_date DESC;

-- new user answers_count by signup date
SELECT date_trunc('day', users.created_at) AS signup_date, count(*) AS answers_count
FROM users
WHERE users.answers_count > 0
GROUP BY signup_date
ORDER BY signup_date DESC;

-- new user questions_count by signup date 
SELECT date_trunc('day', users.created_at) AS signup_date, count(*) AS qs_received
FROM users
WHERE users.questions_count > 0
GROUP BY signup_date
ORDER BY signup_date DESC;

Answer 1

You should try using SUM() with CASE to accomplish this.

Try something like this:

SELECT date_trunc('day', users.created_at) AS signup_date, 
  count(*) AS new_users,
  sum(case when answers_count > 0 then 1 else 0 end) as answers_count,
  sum(case when questions_count > 0 then 1 else 0 end) as qs_received,
FROM users
GROUP BY signup_date
ORDER BY signup_date DESC;

Answer 2

You can try using count with FILTER to do this.

SELECT date_trunc('day', users.created_at) AS signup_date, 
  count(*) AS new_users,
  count(*) FILTER (WHERE answers_count > 0) as answers_count,
  count(*) FILTER (WHERE questions_count > 0) as qs_received,
FROM users
GROUP BY signup_date
ORDER BY signup_date DESC;