Get User count based on status and different date columns-CodePudding

I have to get count of users date wise based on the status and different date columns, Below is my table.

topic_id| user_id | status |  created_at| updated_at | completed_at |
176     | 1       | 0      |2022-11-20  | NULL       | NULL         |
176     | 2       | 1      |2022-11-20  | 2022-11-21 | 2022-11-21   |
176     | 3       | 0      |2022-11-20  | NULL       | NULL         |
176     | 4       | 2      |2022-11-20  | 2022-11-21 | NULL         |
176     | 5       | 2      |2022-11-20  | 2022-11-21 | NULL         |
176     | 6       | 1      |2022-11-20  | 2022-11-22 | 2022-11-22   |

I want output like

Date       | Total_Inactive | Total_Scheduled | Total_Active
2022-11-20 | 2              | 0               | 0
2022-11-21 | 0              | 2               | 1 
2022-11-22 | 0              | 0               | 1

total_Inactive count will be based on status = 0 and created_at column, total_Scheduled count will be based on status = 2 and updated_at column, total_Active count will be based on status = 1 and completed_at column

How can I get this using in single query?

CodePudding user response：

You can combine a CASE expression to pick the correct date with a filtered aggregation:

select case status
          when 0 then created_at
          when 1 then completed_at
          when 2 then updated_at
       end as "date",
       count(*) filter (where status = 0) as total_inactive,
       count(*) filter (where status = 2) as total_scheduled,
       count(*) filter (where status = 1) as total_active
from the_table
group by "date"      
order by "date"

Online example