如何在Snowflake中检测日期区间内是否存在节假日

Got it, let's tackle this problem where we need to flag rows in Table1 if there's any holiday from Table2 within their Date1-Date2 interval. Here are two efficient approaches tailored for Snowflake:

Approach 1: Using EXISTS Subquery #

This method is great for performance because EXISTS stops searching as soon as it finds a matching holiday, which can be faster than joining all rows upfront.

SELECT 
    t1.id,
    t1.Country,
    t1.Date1,
    t1.Date2,
    IFF(EXISTS (
        SELECT 1 
        FROM Table2 t2 
        WHERE t2.Country = t1.Country
          AND t2.Date BETWEEN t1.Date1 AND t1.Date2
    ), 'Yes', 'No') AS is_holiday
FROM Table1 t1;

How it works: #

• The EXISTS subquery checks if there's at least one holiday in Table2 for the same country that falls between the current row's Date1 and Date2.
• Snowflake's IFF function converts the boolean result of EXISTS into the "Yes"/"No" label we need.

Approach 2: Using LEFT JOIN + Aggregation #

If you prefer a join-based approach, this works by counting matching holidays and checking if the count is greater than 0.

SELECT 
    t1.id,
    t1.Country,
    t1.Date1,
    t1.Date2,
    IFF(COUNT(t2.Date) > 0, 'Yes', 'No') AS is_holiday
FROM Table1 t1
LEFT JOIN Table2 t2 
    ON t2.Country = t1.Country
    AND t2.Date BETWEEN t1.Date1 AND t1.Date2
GROUP BY t1.id, t1.Country, t1.Date1, t1.Date2;

How it works: #

• We perform a LEFT JOIN to keep all rows from Table1, even if there are no matching holidays.
• COUNT(t2.Date) counts how many holidays fall within the interval (NULL values from non-matching rows are ignored).
• Using IFF, we mark "Yes" if the count is positive, otherwise "No".

Verification with Your Sample Data #

Both queries will produce your desired result:

• Row 1 (DE, 2018-12-23 to 2018-12-30): Matches the Christmas holiday → is_holiday = Yes
• Row 2 (DE, 2019-08-01 to 2019-08-09): No matching holidays → is_holiday = No
• Row 3 (DE, 2019-04-28 to 2019-05-02): Matches Labor Day → is_holiday = Yes

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