Postgres SQL query group by get most recent record instead of an aggregate

Question

This is a current postgres query I have:

    sql = """
    SELECT
        vms.campaign_id,
        avg(vms.open_rate_uplift) as open_rate_average,
        avg(vms.click_rate_uplift) as click_rate_average,
        avg(vms.conversion_rate_uplift) as conversion_rate_average,
        avg(cms.incremental_opens),
        avg(cms.incremental_clicks),
        avg(cms.incremental_conversions)
    FROM
        experiments.variant_metric_snapshot vms
    INNER JOIN experiments.campaign_metric_snapshot cms ON vms.campaign_id = cms.campaign_id 
    WHERE 
        vms.campaign_id IN %(campaign_ids)s 
    GROUP BY
        vms.campaign_id
    """

whereby I get the average incremental_opens, incremental_clicks, and incremental_conversions per campaign group from the cms table. However, instead of the average, I want the most recent values for the 3 fields. See the cms table screenshot below - I want the values from the record with the greatest (i.e. most recent) event_id (instead of an average for all records) for a given group).

How can I do this? Thanks

Answer 1

It sounds like you want a lateral join.

FROM
experiments.variant_metric_snapshot vms
CROSS JOIN LATERAL (select * from experiments.campaign_metric_snapshot cms where vms.campaign_id = cms.campaign_id order by event_id desc LIMIT 1) cms
WHERE...

Answer 2

If you are after a quick and dirty solution you can use array_agg function with minimal change to your query.

SELECT
        vms.campaign_id,
        avg(vms.open_rate_uplift) as open_rate_average,
        avg(vms.click_rate_uplift) as click_rate_average,
        avg(vms.conversion_rate_uplift) as conversion_rate_average,
        (array_agg(cms.incremental_opens ORDER BY cms.event_id DESC))[1] AS incremental_opens,
        ..
FROM
        experiments.variant_metric_snapshot vms
INNER JOIN experiments.campaign_metric_snapshot cms ON vms.campaign_id = cms.campaign_id 
WHERE 
        vms.campaign_id IN %(campaign_ids)s 
GROUP BY
        vms.campaign_id;