another day, another query! few different ways to do this one, the tidiest version I could come up with is:
WITHloc_cat(city,product_line)AS(VALUES('Mandalay','Home and lifestyle')),dates(sale_date)AS(SELECTgenerate_series('2019-01-01',CURRENT_DATE,'1 day')::DATE)SELECTcity,product_line,sale_date,COALESCE(total,0)AStotalFROM(loc_catCROSSJOINdates)LEFTJOINsupermarketUSING(city,product_line,sale_date)ORDERBY1,2,3
it's not the shortest, but means the location and category values only need to be written once and it's easy to extend to multiple combinations
edit: have now read the "solution" and think I misinterpreted the task! the single city/product was just an example, you actually wanted the cartesian product of all cities, products and the whole date range. I still think I'd do something similar to what I had, e.g:
another day, another query! few different ways to do this one, the tidiest version I could come up with is:
it's not the shortest, but means the location and category values only need to be written once and it's easy to extend to multiple combinations
edit: have now read the "solution" and think I misinterpreted the task! the single city/product was just an example, you actually wanted the cartesian product of all cities, products and the whole date range. I still think I'd do something similar to what I had, e.g:
which is pretty similar to your solution, but less spaced out