Write a query that returns the updated product table with all the category values filled in, taking into consideration the assumption that the first product in each category will always have a defined category value.

Assumptions:

  • Each category is expected to be listed only once in the column and products within the same category should be grouped together based on sequential product IDs.
  • The first product in each category will always have a defined category value.
  • For instance, the category for product ID 1 is 'Shoes', then the subsequent product IDs 2 and 3 will be categorised as 'Shoes'.
  • Similarly, product ID 4 is 'Jeans', then the following product ID 5 is categorised as 'Jeans' category, and so forth.
None
None

First impressions:

  • Filling in missing data using conditions from another column!

Approach:

  1. Since product_id is in ascending order, I can go through each product ID and think about how the following missing rows have the same category as the one filled in before
  2. I can group these by using a counter which counts how many categories there are so far
  3. Then group by the counters, and assign category to the MAX(category) since the others are all NULL

Solution:

WITH counting AS (
SELECT
  *,
  COUNT(category) OVER (ORDER BY product_id) AS counter
FROM products
)

SELECT
  product_id,
  MAX(category) OVER (PARTITION BY counter),
  name
FROM (
SELECT
  *,
  COUNT(category) OVER (ORDER BY product_id) AS counter
FROM products
) counting_table

Takeaways: