aggregate.sql

SELECT q1.accommodation_provider_id,
       email,
       name
FROM
(SELECT accommodation_provider_id,
       field_value AS email
FROM
(SELECT id,
       accommodation_provider_id,
       event_name,
       field_value,
       created_at,
       ROW_NUMBER()
       OVER (PARTITION BY accommodation_provider_id
             ORDER BY created_at DESC)
FROM
(SELECT *
FROM    ap_domain_profile_events
WHERE   event_name = 'accommodation_provider_updated_email_address'))
WHERE   ROW_NUMBER = 1) AS q1
LEFT JOIN
(SELECT accommodation_provider_id,
       field_value AS name
FROM
(SELECT id,
       accommodation_provider_id,
       event_name,
       field_value,
       created_at,
       ROW_NUMBER()
       OVER (PARTITION BY accommodation_provider_id
             ORDER BY created_at DESC)
FROM
(SELECT *
FROM    ap_domain_profile_events
WHERE   event_name = 'accommodation_provider_updated_name'))
WHERE   ROW_NUMBER = 1) AS q2
ON q1.accommodation_provider_id = q2.accommodation_provider_id;