The code you provided can be improved. Here’s an updated version:

SELECT 
    user_id,
    date,
    day_hours_worked AS current_hourly_rate,
    LAG(day_hours_worked, 1) OVER (PARTITION BY user_id ORDER BY date) AS previous_hourly_rate,
    LAG(day_hours_worked, 2) OVER (PARTITION BY user_id ORDER BY date) AS hourly_rate_2_days_ago,
    LAG(day_hours_worked, 3) OVER (PARTITION BY user_id ORDER BY date) AS hourly_rate_3_days_ago,
    LAG(day_hours_worked, 4) OVER (PARTITION BY user_id ORDER BY date) AS hourly_rate_4_days_ago,
    LAG(day_hours_worked, 5) OVER (PARTITION BY user_id ORDER BY date) AS hourly_rate_5_days_ago,
    LAG(day_hours_worked, 6) OVER (PARTITION BY user_id ORDER BY date) AS hourly_rate_6_days_ago
FROM 
    data d
ORDER BY 
    user_id, date;

This query will get the previous n days of hourly rates for each user. You can adjust the value in the LAG function to change how many days you want to look back.

The query uses the LAG window function to access the previous row’s values based on a specific ordering (in this case, the date column). The first argument to LAG is the number of rows to go back, and the second argument is the column to retrieve from that row.

The result set will be:

| user_id | date | current_hourly_rate | previous_hourly_rate | hourly_rate_2_days_ago | … | | — | — | — | — | — | … |

This query assumes that there are no gaps in dates. If there are gaps, you might need to use a more complex query involving ROW_NUMBER or RANK window functions.

If you want the last row to be calculated from previous 7 days, you can’t use LAG as it would look at rows after it and not before.

However, if you want to keep all these values (previous hourly rate and next 6 hourly rates) in a single result set for each row, then it’s not possible with just SQL. You may need to create a view or use some other programming language like Python or Java to handle this.

Last modified on 2025-04-18