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
Comments
Post a Comment