Idea for postgres query

Question

I have a postgres query that needs to get some some facts for each ID in a defined 6 month period starting with the last month they were active (which I did with the use of 2 sub-queries in the where clause).

I have done the query bellow that works for 1 id input, but not sure how to do it for multiple id's at the same time so that its going to get the max period and max-6 for each id as each ID has different max periods.

    SELECT 
       key_id,
       period_id,
       value1,
       run_version,
       gs.run_vs = MAX(gs.run_vs) OVER(PARTITION BY key_id, run_id) AS max_version
       FROM gs
       WHERE key_id=23 
    AND gs.period_id <= (SELECT 
                        MAX(period_id)
                        FROM gs
                        WHERE key_id=23
                        )
   AND gs.period_id >(SELECT
                      MAX(period_id)-7
                      FROM gs 
                      WHERE key_id=23
                      )

This query is part of a broader one in which one of the requirements was to have the values for the last 6 months of the id and id's have different end dates hence why they could differ.

Answer 1

You could join with an aggregate query that computes the max period for all keys at once:

SELECT 
    g.key_id,
    g.period_id,
    g.value1,
    g.run_version,
    MAX(g.run_vs) OVER(PARTITION BY g.key_id, g.run_id) AS max_version
FROM gs g
INNER JOIN (SELECT key_id, MAX(period_id) max_period_id FROM gs GROUP BY key_id) k 
    ON  g.key_id = k.key_id
    AND g.period_id >= k.max_period_id - 7 
    AND g.period_id < k.max_period_id

But window functions are probably more efficient here:

SELECT 
    key_id,
    period_id,
    value1,
    run_version,
    MAX(run_vs) OVER(PARTITION BY key_id, run_id) AS max_version
FROM (
    SELECT gs.*,
        MAX(max_period_id) OVER(PARTITION BY key_id) AS max_period_id
    FROM gs
) g
WHERE period_id >= max_period_id - 7 AND period_id < max_period_id