How to split the sample by periods?

Question

PostgreSQL

I need a query that will allow me to get the number of documents owned by a counterparty by period: for 7 days, for 14 days, for a month, two

. At the moment, the following works for one period:

SELECT
  COUNT(dow.document_id) as "7 days",
  c.short_name
  FROM documents_owners dow
INNER JOIN contractors_main c ON dow.contractor_id = c.id
INNER JOIN documents_main d ON dow.document_id = d.id
WHERE d.created_at > NOW() - interval '7 days'
GROUP BY c.short_name
ORDER BY COUNT(dow.document_id) DESC

But I don’t want to write such a sheet for each period and then somehow combine them.
Tell me, how is this solved?

Answer 1

SELECT
  COUNT(case when d.created_at > NOW() - interval '7 days' then 1 else null end) as "7 days",
  COUNT(case when d.created_at > NOW() - interval '14 days' then 1 else null end) as "14 days",
  COUNT(case when d.created_at > NOW() - interval '1 month' then 1 else null end) as "1 month"
  c.short_name
  FROM documents_owners dow
INNER JOIN contractors_main c ON dow.contractor_id = c.id
INNER JOIN documents_main d ON dow.document_id = d.id
GROUP BY c.short_name
ORDER BY COUNT(dow.document_id) DESC