Ad
  • Custom User Avatar

    You could do a recursive CTE by passing the start date and stopping at the end date (with the where clause), with the other fields being null or zero (with their castings). Then, just do a union all with the results of this CTE and the original table in the target query. Bingo! The blank months will be there.

  • Default User Avatar

    Questionable statement about performance and memory consumption.
    In postgres WITH is always optimization fence. PG always materialize it.
    Here you basically told PG to make two temp tables out of payment table doing two scans (index or maybe full table ones as we don't have any idea if staff_id is indexed or not or anything about its cardinality and distribution).
    Sorry, but with all things being equal this query does two scans instead of one while AGGREGATE WITH FILTER approach gets all aggregates in a single pass over the table (using index or not). Not knowing table statistics we cannot simply say what will be faster.

  • Default User Avatar

    Passes tests but does not show months with no sales.
    Sounds a bit pedantic though, was not asked by instruction