SQL query to filter visit having cart addition, & all pages viewed in a visit | Community
Skip to main content
DineshRkumar
DineshRkumar
Level 3
November 20, 2025
Solved

SQL query to filter visit having cart addition, & all pages viewed in a visit

  • November 20, 2025
  • 1 reply
  • 97 views

Hi Team,,  
Looking for SQL query to filter visit having cart addition, & all pages viewed in a visit.. thanks in advance

    Best answer by SamuelPaulPeter

    Hi @dineshrkumar 

     

    Use the below SQL to execute your requirement:

    Please do not forget to replace your_experienceevents_table, visitorId, sessionId, eventTimestamp, pageName, eventType, and cartAction with the exact field names in your dataset.

    SELECT visitorId, sessionId, MIN(eventTimestamp) AS visit_start, MAX(eventTimestamp) AS visit_end, array_join( array_distinct(array_agg(pageName ORDER BY eventTimestamp)), ' | ' ) AS pages_viewed, SUM(CASE WHEN eventType = 'cart' AND cartAction = 'add' THEN 1 ELSE 0 END) AS cart_add_count FROM your_experienceevents_table WHERE eventTimestamp BETWEEN TIMESTAMP '2025-01-01 00:00:00' AND TIMESTAMP '2025-12-31 23:59:59' GROUP BY visitorId, sessionId HAVING SUM(CASE WHEN eventType = 'cart' AND cartAction = 'add' THEN 1 ELSE 0 END) > 0 ORDER BY visit_start DESC LIMIT 1000;

    • If your site stores page path instead of pageName, use that field.

    • If cart-add is recorded as an event name ("productAddToCart"), adjust the CASE condition.

    • Remove or adjust the WHERE date window to control runtime.

    If you still find it difficult, paste the exact table/field names (or the sample file) and I’ll adapt the query to your schema as much as I can.

    1 reply

    SamuelPaulPeter
    SamuelPaulPeterAccepted solution
    Level 3
    November 21, 2025

    Hi @dineshrkumar 

     

    Use the below SQL to execute your requirement:

    Please do not forget to replace your_experienceevents_table, visitorId, sessionId, eventTimestamp, pageName, eventType, and cartAction with the exact field names in your dataset.

    SELECT visitorId, sessionId, MIN(eventTimestamp) AS visit_start, MAX(eventTimestamp) AS visit_end, array_join( array_distinct(array_agg(pageName ORDER BY eventTimestamp)), ' | ' ) AS pages_viewed, SUM(CASE WHEN eventType = 'cart' AND cartAction = 'add' THEN 1 ELSE 0 END) AS cart_add_count FROM your_experienceevents_table WHERE eventTimestamp BETWEEN TIMESTAMP '2025-01-01 00:00:00' AND TIMESTAMP '2025-12-31 23:59:59' GROUP BY visitorId, sessionId HAVING SUM(CASE WHEN eventType = 'cart' AND cartAction = 'add' THEN 1 ELSE 0 END) > 0 ORDER BY visit_start DESC LIMIT 1000;

    • If your site stores page path instead of pageName, use that field.

    • If cart-add is recorded as an event name ("productAddToCart"), adjust the CASE condition.

    • Remove or adjust the WHERE date window to control runtime.

    If you still find it difficult, paste the exact table/field names (or the sample file) and I’ll adapt the query to your schema as much as I can.

      Terms & ConditionsAccessibility statement

      Loading footer...