visit level Segment in creation using SQL query

[DineshRkumar]

Hi Team,, can you please help me to build visit level segment as shown below using SQL query?
V7 & event9 is firing together on Search page and "checkout start" is firing on checkout page. 

[SamuelPaulPeter]

What the segment means

Include visits where:

• Campaign Clicks – Others (v7) exists

• Search Campaign (e9) exists

• Checkout Start event exists
  …all within the same visit

SQL (Visit-level logic)

SELECT
visitor_id,
visit_id
FROM adobe_data_feed
GROUP BY visitor_id, visit_id
HAVING
SUM(CASE WHEN v7 IS NOT NULL THEN 1 ELSE 0 END) > 0 -- Campaign Clicks - Others
AND SUM(CASE WHEN event9 > 0 THEN 1 ELSE 0 END) > 0 -- Search Campaign
AND SUM(CASE WHEN checkout_start > 0 THEN 1 ELSE 0 END) > 0 -- Checkout Start
;

If you want the actual rows (hits) for those visits

SELECT *
FROM adobe_data_feed
WHERE (visitor_id, visit_id) IN (
SELECT
visitor_id,
visit_id
FROM adobe_data_feed
GROUP BY visitor_id, visit_id
HAVING
SUM(CASE WHEN v7 IS NOT NULL THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN event9 > 0 THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN checkout_start > 0 THEN 1 ELSE 0 END) > 0
);

Why this matches the Adobe segment

Adobe Visit-level segments work by checking “did this visit contain at least one hit with…”
That’s exactly what the HAVING SUM(CASE WHEN …) > 0 logic does.

[Pooja_Choudhary]

SELECT
    post_visid_high,
    post_visid_low,
    visit_num
FROM adobe_data_feed
GROUP BY
    post_visid_high,
    post_visid_low,
    visit_num
HAVING
    -- V7 exists AND event9 fired in the visit
    SUM(
        CASE
            WHEN post_evar7 IS NOT NULL
             AND post_event_list LIKE '%event9%'
            THEN 1 ELSE 0
        END
    ) > 0

    -- checkout start never fired in the visit
    AND
    SUM(
        CASE
            WHEN post_event_list LIKE '%checkout_start%'
            THEN 1 ELSE 0
        END
    ) = 0;