Query does not show current month's data

Question

SELECT TO_CHAR(months.month, 'YYYY-MM') AS month,
       COALESCE(SUM(dm.revenue)::FLOAT, 0.0) AS total_amount,
       COALESCE(COUNT(dm.id), 0) AS value,
       array_agg(dm.id) AS lost_deal_ids
FROM (
    SELECT generate_series(DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '4 months', DATE_TRUNC('month', CURRENT_DATE), INTERVAL '1 month')::DATE AS month
) months
LEFT JOIN deal_management dm ON TO_CHAR(dm.closing_date, 'YYYY-MM') = TO_CHAR(months.month, 'YYYY-MM') AND dm.employee_id = 'sojdosdj2920320323'
LEFT JOIN lookup_data ld ON ld.id = dm.pipeline_id
WHERE (TO_CHAR(months.month, 'YYYY-MM') BETWEEN TO_CHAR(CURRENT_DATE - INTERVAL '4 months', 'YYYY-MM') AND TO_CHAR(CURRENT_DATE, 'YYYY-MM')
    OR TO_CHAR(months.month, 'YYYY-MM') = TO_CHAR(CURRENT_DATE, 'YYYY-MM'))
    AND (ld.level_data = 'Lost' OR ld.id IS NULL)
    AND dm.is_evaluate IS NOT TRUE
GROUP BY TO_CHAR(months.month, 'YYYY-MM')
ORDER BY TO_CHAR(months.month, 'YYYY-MM');

demo result

I want to see current month's data as

 2024-01 | 0 | {null}

But if missing want to see '0'.

Answer 1

Pretty much every aspect of your query needs attention. This might work, and a lot faster, too:

SELECT to_char(m.mon, 'YYYY-MM') AS month
     , coalesce(sum(dm.revenue)::float, 0.0) AS total_amount
     , count(dm.id) AS count_deals
     , coalesce(string_agg(dm.id::text, ', '), '0') AS lost_deal_ids
FROM   generate_series(date_trunc('month', LOCALTIMESTAMP) - interval '4 mon'
                     , date_trunc('month', LOCALTIMESTAMP)
                     , interval '1 month') m(mon)
LEFT   JOIN deal_management dm ON dm.closing_date >= m.mon
                              AND dm.closing_date <  m.mon + interval '1 mon'
                              AND dm.employee_id = 'sojdosdj2920320323'
                              AND dm.is_evaluate IS NOT TRUE
LEFT   JOIN lookup_data     ld ON ld.id = dm.pipeline_id
                              AND ld.level_data = 'Lost'
GROUP  BY m.mon
ORDER  BY m.mon;

If you want to see '0' instead of null, you can't use an array type for the output column lost_deal_ids. Use string_agg() instead of array_agg(). (Why bother, though? You count is clear anyway.)

The table-function generate_series() can stand on its own in the FROM list after we remove the unhelpful cast to date.

Use LOCALTIMESTAMP instead of CURRENT_DATE. This way, date_trunc() produces timestamp instead of timestamptz. See:

• Generating time series between two dates in PostgreSQL

To put it bluntly, none of your WHERE conditions make sense.

• This is 100 % redundant. Twice redundant actually. The 1st boolean expressions is always true, the 2nd is implied by the first:

    WHERE (to_char(months.month, 'YYYY-MM') BETWEEN to_char(CURRENT_DATE - INTERVAL '4 months', 'YYYY-MM') AND to_char(CURRENT_DATE, 'YYYY-MM')
    OR to_char(months.month, 'YYYY-MM') = to_char(CURRENT_DATE, 'YYYY-MM'))
  

• This is an awkward, inferior way of adding ld.level_data = 'Lost' as join condition to the LEFT JOIN:

    WHERE (ld.level_data = 'Lost' OR ld.id IS NULL)
  

• This would contradict the LEFT JOIN. (I resolved by moving it into the join condition):

    WHERE  dm.is_evaluate IS NOT TRUE
  

count() never returns null. So coalesce(count( ... never does anything useful.

Understand the different role of conditions in WHERE vs. LEFT JOIN:

• Query with LEFT JOIN not returning rows for count of 0

Your join condition ON to_char(dm.closing_date, 'YYYY-MM') = to_char(months.month, 'YYYY-MM') was needlessly expensive. And not saragable.

Use the sargable expression ON dm.closing_date >= m.mon AND dm.closing_date < m.mon + interval '1 mon' instead.

Use the timestamp value, not the text generated with to_char() in GROUP BY and ORDER BY. Cheaper, and less error prone.

Don't use a non-descriptive column alias like "value".

Consider hiring a consultant.