Spreading the values to other rows but still respecting the total sum-CodePudding

I have a dataset as below:

date        employee     products     sales
20210101       ben          5         laptop
20210101       ben         10         monitor
20210201       tim         15         laptop
20210301       tim         10         monitor

What I would like to do is to add another field/column as the working hours for these employees. Depending on how many rows the employee have on that particular day (it could be up to 5-10 rows), the number of working hours should be spread equally, but the total should always be a maximum of 6 hours per day.

The desired output should be:

date        employee     products     sales        hours
20210101       ben          5         laptop         3
20210101       ben         10         monitor        3
20210201       tim         15         laptop         6
20210301       tim         10         monitor        6

I don't have any good idea to perform this query. If anyone could give me a hint on a method or an approach to tackle this, I would really appreciate.

CodePudding user response：

Pretty sure redshift supports window functions..

SELECT *,
  6.0/COUNT(*) OVER(PARTITION BY date, employee) as hours 
FROM dataset