Posgresql reporting query

With PostgreSQL when doing with datetime for reporting, it realy help if the DB can aggregate your needed data rather than process it yourself.

I found this interesting and useful as i start needed query for reporting from my dataset.

For example i want to have a list of records which user register per month. With postgresql here is the query :

SELECT date_trunc('month', created_at),
       count(*)
FROM users
GROUP BY 1
ORDER BY 1 DESC;

With date_trunc PostgreqSQL do its magic and return you the needed data without doing a query per month. really not so effective query. To get per week activity :

with months as (
  select month
  from generate_series('2018-01-01'::date, now()::date, '1 month'::interval) month
)

SELECT months.month,
       count(pmactivitylogs.id)
FROM months,
left join pmactivitylogs on date_trunc('month', pmactivitylogs.created_at) = months.month

GROUP BY 1
ORDER BY 1 DESC;

We will get all the monthly count of records for our reporting.

For more details get the docs on postgresql web site.

Posted in: postgresql