Return counted users based on a status

Question

I have the following PostgreSQL table:

CREATE TABLE users
(
    id uuid NOT NULL,
    login_name character varying(255),
    status character varying(10), // it can be enabled/disabled
    created_at timestamp(6)
)

I want to create SQL query to get summary statistics. For example for August how many enabled and disabled users I have in total:

I tried:

SELECT ID, 
  DATE(CREATED_AT) AS date, 
  COUNT(*) AS enabled, 
  COUNT(*) AS disabled 
FROM users 
WHERE (created_at BETWEEN '2022-05-02 06:58:24.620396' AND '2023-05-02 06:58:24.620396') 
GROUP BY DATE(created_at), id

But I get this result:

I want to get the data like this:

Do you know how I can implement this query properly?

Answer 1

If you are looking to group your users by the month they were created, and count how many are currently enabled/disabled, then you can just use conditional aggregation:

select date_trunc('month', created_at) created_month,
    count(*) filter (where status = 'enabled' ) cnt_enabled,
    count(*) filter (where status = 'disabled') cnt_disabled
from mytable
group by date_trunc('month', created_at)