Discussion on: #SQL30 Day 6: Supermarket Sales

View post

another day, another query! few different ways to do this one, the tidiest version I could come up with is:

WITH loc_cat(city, product_line) AS (
  VALUES ('Mandalay', 'Home and lifestyle')
), dates(sale_date) AS (
  SELECT generate_series('2019-01-01', CURRENT_DATE, '1 day')::DATE
)
SELECT city, product_line, sale_date, COALESCE(total, 0) AS total
FROM (loc_cat CROSS JOIN dates)
  LEFT JOIN supermarket USING (city, product_line, sale_date)
ORDER BY 1, 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:

WITH loc AS (
  SELECT DISTINCT city FROM supermarket
), cat AS (
  SELECT DISTINCT product_line FROM supermarket
), dates AS (
  SELECT generate_series(dstart, dend, '1 day')::DATE AS sale_date FROM (
    SELECT MIN(sale_date) AS dstart, MAX(sale_date) AS dend FROM supermarket
  ) x
)
SELECT city, product_line, sale_date, COALESCE(total, 0) AS total
FROM (loc CROSS JOIN cat CROSS JOIN dates)
  LEFT JOIN supermarket USING (city, product_line, sale_date)
ORDER BY 1, 2, 3;

which is pretty similar to your solution, but less spaced out