• sqlfiddle.com

CREATE TABLE page_hierarchy (
“page_id” INTEGER,
“page_name” VARCHAR(14),
“product_category” VARCHAR(9),
“product_id” INTEGER
);

insert into page_hierarchy values
(‘1’, ‘Home Page’, null, null),
(‘2’, ‘All Products’, null, null),
(‘3’, ‘Salmon’, ‘Fish’, ‘1’),
(‘4’, ‘Kingfish’, ‘Fish’, ‘2’),
(‘5’, ‘Tuna’, ‘Fish’, ‘3’),
(‘6’, ‘Russian Caviar’, ‘Luxury’, ‘4’),
(‘7’, ‘Black Truffle’, ‘Luxury’, ‘5’);

CREATE TABLE events (
“visit_id” VARCHAR(6),
“cookie_id” VARCHAR(6),
“page_id” INTEGER,
“event_type” INTEGER,
“sequence_number” INTEGER,
“event_time” TIMESTAMP
);

insert into events values
(‘ccf365’, ‘c4ca42’, ‘1’, ‘1’, ‘1’, ‘2020-02-04 19:16:09.182546’),
(‘ccf365’, ‘c4ca42’, ‘2’, ‘1’, ‘2’, ‘2020-02-04 19:16:17.358191’),
(‘ccf365’, ‘c4ca42’, ‘6’, ‘1’, ‘3’, ‘2020-02-04 19:16:58.454669’),
(‘ccf365’, ‘c4ca42’, ‘9’, ‘1’, ‘4’, ‘2020-02-04 19:16:58.609142’),
(‘ccf365’, ‘c4ca42’, ‘9’, ‘2’, ‘5’, ‘2020-02-04 19:17:51.72942’),
(‘ccf365’, ‘c4ca42′, ’10’, ‘1’, ‘6’, ‘2020-02-04 19:18:11.605815’),
(‘ccf365’, ‘c4ca42′, ’10’, ‘2’, ‘7’, ‘2020-02-04 19:19:10.570786’),
(‘ccf365’, ‘c4ca42′, ’11’, ‘1’, ‘8’, ‘2020-02-04 19:19:46.911728’),
(‘ccf365’, ‘c4ca42′, ’11’, ‘2’, ‘9’, ‘2020-02-04 19:20:45.27469’),
(‘ccf365’, ‘c4ca42′, ’12’, ‘1’, ’10’, ‘2020-02-04 19:20:52.307244’),
(‘ccf365’, ‘c4ca42′, ’13’, ‘3’, ’11’, ‘2020-02-04 19:21:26.242563’),
(‘d58cbd’, ‘c81e72’, ‘1’, ‘1’, ‘1’, ‘2020-01-18 23:40:54.761906’),
(‘d58cbd’, ‘c81e72’, ‘2’, ‘1’, ‘2’, ‘2020-01-18 23:41:06.391027’),
(‘d58cbd’, ‘c81e72’, ‘4’, ‘1’, ‘3’, ‘2020-01-18 23:42:02.213001’),
(‘d58cbd’, ‘c81e72’, ‘4’, ‘2’, ‘4’, ‘2020-01-18 23:42:02.370046’),
(‘d58cbd’, ‘c81e72’, ‘5’, ‘1’, ‘5’, ‘2020-01-18 23:42:44.717024’),
(‘d58cbd’, ‘c81e72’, ‘5’, ‘2’, ‘6’, ‘2020-01-18 23:43:11.121855’),
(‘d58cbd’, ‘c81e72’, ‘7’, ‘1’, ‘7’, ‘2020-01-18 23:43:25.806239’),
(‘d58cbd’, ‘c81e72’, ‘8’, ‘1’, ‘8’, ‘2020-01-18 23:43:40.537995’),
(‘d58cbd’, ‘c81e72’, ‘8’, ‘2’, ‘9’, ‘2020-01-18 23:44:14.026393’),
(‘d58cbd’, ‘c81e72′, ’10’, ‘1’, ’10’, ‘2020-01-18 23:44:22.103768’),
(‘d58cbd’, ‘c81e72′, ’10’, ‘2’, ’11’, ‘2020-01-18 23:45:00.004781’),
(‘d58cbd’, ‘c81e72′, ’12’, ‘1’, ’12’, ‘2020-01-18 23:45:38.186554’);

CREATE TABLE event_identifier (
“event_type” INTEGER,
“event_name” VARCHAR(13)
);
insert into event_identifier values
(‘1’, ‘Page View’),
(‘2’, ‘Add to Cart’),
(‘3’, ‘Purchase’),
(‘4’, ‘Ad Impression’),
(‘5’, ‘Ad Click’);

select visit_id, string_agg(page_name, ‘,’ order by sequence_number) as cart_items
from events e
join page_hierarchy ph on e.page_id = ph.page_id
join event_identifier ei on ei.event_type = e.event_type
group by visit_id