如何获取首次访问后60天内多次访问的用户？SQL自连接问题排查

Let's walk through what's not working with your current query and how to adjust it to get exactly what you need.

Issues with Your Current Query #

Your self-join approach is on the right track, but there are a few key problems:

• No filtering for first visit: The a.visit_dt in your select isn't guaranteed to be the user's first visit date—you could end up with arbitrary visit dates instead of the initial one.
• Duplicate rows: The self-join will create multiple rows for the same user (one for every pair of visits within 60 days), leading to redundant data.
• Unrestricted date range: Using abs(datediff(...)) includes cases where b.visit_dt is before a.visit_dt, which isn't relevant since you care about visits after the first one.

Solution 1: Using Window Functions (Cleaner Approach) #

This method first calculates each user's first visit date, then checks if they have additional visits within the 60-day window:

WITH user_visits AS (
    SELECT
        user_id,
        visit_dt,
        -- Get the first visit date for each user
        MIN(visit_dt) OVER (PARTITION BY user_id) AS first_visit_dt
    FROM dataset1
)
-- Select users who have multiple visits in the 60-day window after their first visit
SELECT DISTINCT
    user_id,
    first_visit_dt AS visit_dt
FROM user_visits
WHERE
    -- Ensure we're looking at visits on or after the first visit, within 59 days (since 0 = same day, 59 = day 60)
    DATEDIFF(day, first_visit_dt, visit_dt) BETWEEN 0 AND 59
GROUP BY user_id, first_visit_dt
HAVING COUNT(DISTINCT visit_dt) > 1;

Solution 2: Self-Join with First Visit Filter #

If you prefer sticking with a self-join, add a filter to ensure you're only using the first visit as the starting point:

SELECT DISTINCT
    a.user_id,
    a.visit_dt AS visit_dt
FROM dataset1 a
JOIN dataset1 b
    ON a.user_id = b.user_id
    -- Only join visits that happen after the first visit, within 60 days
    AND b.visit_dt > a.visit_dt
    AND DATEDIFF(day, a.visit_dt, b.visit_dt) < 60
-- Ensure a is the user's first visit
WHERE a.visit_dt = (SELECT MIN(visit_dt) FROM dataset1 WHERE user_id = a.user_id);

How These Work #

• Both solutions first identify the user's initial visit date.
• They then check if there's at least one other visit that falls within 60 days of that first visit.
• The DISTINCT or GROUP BY ensures you only get one row per user, with their first visit date as required.

内容的提问来源于stack exchange，提问作者bree