WITH CTE AS ( SELECT distinct visit_id
FROM events e1
JOIN event_identifier e2 ON e1.event_type = e2.event_type
WHERE event_name = 'Page View'),
VIEW_CTE AS ( SELECT page_name,
product_category,
count(page_name) AS total_view
FROM events e1
LEFT JOIN CTE c ON e1.visit_id = c.visit_id
JOIN page_hierarchy p ON e1.page_id = p.page_id
JOIN event_identifier e2 ON e1.event_type = e2.event_type
WHERE product_category IS NOT NULL AND event_name = 'Page View'
GROUP BY page_name,product_category),
ADD_CART_CTE AS (SELECT page_name,
product_category,
count(page_name) AS total_add
FROM events e1
LEFT JOIN CTE c ON e1.visit_id = c.visit_id
JOIN page_hierarchy p ON e1.page_id = p.page_id
JOIN event_identifier e2 ON e1.event_type = e2.event_type
WHERE product_category IS NOT NULL AND event_name = 'Add to Cart'
GROUP BY page_name,product_category),
PURCHASE_CTE AS (SELECT visit_id
FROM events e1
JOIN event_identifier e2 ON e1.event_type = e2.event_type
WHERE event_name = 'Purchase'),
PURCHASE_CTE_2 AS ( SELECT page_name,
product_category,
COUNT(e1.visit_id) as purchase_item
FROM events e1
RIGHT JOIN PURCHASE_CTE C on e1.visit_id = C.visit_id
JOIN page_hierarchy p ON e1.page_id = p.page_id
JOIN event_identifier e2 ON e1.event_type = e2.event_type
WHERE product_category IS NOT NULL AND event_name = 'Add to Cart'
GROUP BY page_name,product_category
)
SELECT v.page_name,
v.product_category,
total_view,
total_add,
purchase_item,
(total_add - purchase_item) as abadoned_item
INTO product_stats
FROM VIEW_CTE V
LEFT JOIN ADD_CART_CTE A ON V.page_name = A.page_name
LEFT JOIN PURCHASE_CTE_2 P ON V.page_name = P.page_name
ORDER BY v.page_name;
SELECT *
FROM product_stats;
Result
