2022-06-19

Group By for a Window function

Am stuck in a scenario where I have prices along with date and product id and am running windows functions on top of the prices.

What am looking for is that the output should be one row per day with the aggregation and the window functions. Because the source data has multiple rows per day and am using the aggregation and window function to clear that.

When am doing group by it says I have to add prices.

Why is that?

select
  product_id,
  DATE(fetch_datetime_utc) AS date,
  marketplace,
  source,
  avg(list_price) OVER(
            PARTITION by asin, date
            ORDER BY date
        ) AS list_price,
  avg(price) OVER(
            PARTITION by asin, date
            ORDER BY date
        ) AS price,
  min(price) OVER(
            PARTITION by asin, date
            ORDER BY date
        ) AS min_price,
  max(price) OVER(
            PARTITION by asin, date
            ORDER BY date
        ) AS max_price
from
  table1
group by
  productid,
  DATE(fetch_datetime_utc),
  marketplace


No comments:

Post a Comment