MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE visitors(

visitor_id INTEGER NOT NULL PRIMARY KEY,

country_of_birth VARCHAR(7) NOT NULL

);

INSERT INTO visitors(visitor_id, country_of_birth) VALUES

(1, 'Bolivia'),

(2, 'UK'),

(3, 'UK'),

(4, 'Bolivia'),

(5, 'UK'),

(6, 'UK'),

(7, 'France'),

(8, 'USA'),

(9, 'UK'),

(10, 'France');

-- activity_log

CREATE TABLE activity_log(

visitor_id INTEGER NOT NULL,

FOREIGN KEY (visitor_id) REFERENCES visitors(visitor_id),

activity_time DATETIME NOT NULL,

activity_name VARCHAR(14) NOT NULL,

what_was_purchased VARCHAR(8)

);

INSERT INTO activity_log(visitor_id, activity_time, activity_name, what_was_purchased) VALUES

(1, '2020-09-03 11:15:00', 'visit lions', NULL),

(1, '2020-09-03 10:30:00', 'use restroom', NULL),

(1, '2020-09-03 10:10:00', 'visit reptiles', NULL),

(1, '2020-09-03 10:45:00', 'purchase', 'coffee'),

(2, '2021-02-10 15:30:00', 'visit giftshop', NULL),

(2, '2021-02-10 15:02:00', 'visit zebras', NULL),

(2, '2021-02-10 15:45:00', 'visit giraffes', NULL),

(3, '2021-07-07 13:04:00', 'visit reptiles', NULL),

(3, '2021-07-07 13:50:00', 'visit bears', NULL),

Records: 10  Duplicates: 0  Warnings: 0

Records: 29  Duplicates: 0  Warnings: 0

with l as (

select l.visitor_id, l.what_was_purchased,

Row_Number() over(partition by l.visitor_id order by l.activity_time desc) rn

from activity_log l

join visitors v on v.visitor_id = l.visitor_id and v.country_of_birth = 'UK'

where l.activity_name = 'purchase'

)

select visitor_id, what_was_purchased

from l

where rn = 1;