Calendar logic looks simple — until it breaks production.

If you’re preparing for FAANG-level data engineering interviews, you’ve probably seen variations of this problem:

Inspections can only happen on Tuesday or Thursday.
If an order is placed today, schedule it on the next eligible inspection day.
No same-day scheduling.
Skip holidays.

Sounds straightforward.

It isn’t.

This problem tests:

Date arithmetic
Modular math
Set-based thinking
Clean abstraction (no messy CASE blocks)

Let’s break it down the right way.

📊 Problem Setup

We have two tables:

`orders`

order_id	order_date
1	2024-01-01
2	2024-01-02
3	2024-01-03

`holidays`

holiday_date
2024-01-09

Rules

Inspections only on Tuesday or Thursday
Never same-day scheduling
Skip holidays
Pick the earliest valid inspection date

🧠 The Key Insight: Weekly Cycles = Modulo 7

Since weekdays repeat every 7 days, we use modular arithmetic.

The clean formula for “next target weekday” is:


((target_weekday - current_weekday + 6) % 7) + 1

Why?

% 7 wraps cyclic logic
+6 shifts the range to avoid 0
+1 ensures we always move forward at least one day

No CASE statements required.

✅ Solution 1 — Column-Based Modular Math

This approach calculates next Tuesday and next Thursday separately.


SET DATEFIRST 7;

WITH computed_dates AS (
    SELECT
        o.order_id,
        o.order_date,

        DATEADD(day,
            ((3 - DATEPART(weekday, o.order_date) + 6) % 7) + 1,
            o.order_date
        ) AS next_tuesday,

        DATEADD(day,
            ((5 - DATEPART(weekday, o.order_date) + 6) % 7) + 1,
            o.order_date
        ) AS next_thursday

    FROM orders o
)

SELECT
    c.order_id,
    c.order_date,
    MIN(candidate_date) AS inspection_date
FROM computed_dates c
CROSS APPLY (VALUES (c.next_tuesday), (c.next_thursday)) v(candidate_date)
WHERE NOT EXISTS (
    SELECT 1 FROM holidays h
    WHERE h.holiday_date = CAST(v.candidate_date AS date)
)
GROUP BY c.order_id, c.order_date
ORDER BY c.order_id;

Why this works

Compute both possible inspection days
Convert columns into rows
Remove holidays
Select the earliest date

Clean. Deterministic. Interview-ready.

🚀 Solution 2 — Pure CROSS APPLY (More Elegant)

Instead of computing two columns first, we treat weekdays as data.


SET DATEFIRST 7;

SELECT
    o.order_id,
    o.order_date,
    MIN(candidate_date) AS inspection_date
FROM orders o

CROSS APPLY (
    SELECT DATEADD(day,
            ((target_weekday - DATEPART(weekday, o.order_date) + 6) % 7) + 1,
            o.order_date
        ) AS candidate_date
    FROM (VALUES (3), (5)) v(target_weekday)
) d

WHERE NOT EXISTS (
    SELECT 1
    FROM holidays h
    WHERE h.holiday_date = CAST(d.candidate_date AS date)
)

GROUP BY
    o.order_id,
    o.order_date
ORDER BY
    o.order_id;

Why this version is better

No duplicated logic
Extensible (just change VALUES list)
Fully set-based
Cleaner abstraction

If inspections later include Saturday, simply modify:


VALUES (3), (5)


VALUES (3), (5), (7)

No structural rewrite needed.

That’s scalable SQL design.

🟠 What About NEXT_DAY()?

Some databases like Oracle and Snowflake support:


NEXT_DAY(date, 'TUESDAY')

SQL Server does not.

In Oracle:


SELECT NEXT_DAY(order_date, 'TUESDAY') FROM orders;

This automatically returns the next Tuesday (never same-day).

However, once you introduce:

Holidays
SLAs
Business hours
Time zones

You often still need calendar tables.

🧠 Interview Perspective

Interviewers are not testing whether you know DATEADD.

They’re testing:

Do you understand cyclic patterns?
Can you avoid messy branching logic?
Can you design extensible SQL?
Can you think in sets instead of conditions?

If you explain:

“I use modular arithmetic to compute next weekday and CROSS APPLY to treat weekdays as data.”

You signal senior-level thinking.

🏆 Production Reality

In real systems, many teams:

Use calendar dimension tables
Precompute next inspection days
Store SLA boundaries

But for interviews, modular arithmetic demonstrates reasoning clearly.

Final Takeaway

Calendar logic is deceptively simple.

But clean calendar logic:

Avoids CASE explosions
Avoids scanning future dates
Scales
Reads well
Signals engineering maturity

If you’re preparing for FAANG interviews, mastering these patterns is essential.

If you want more real interview-style SQL problems like this, I’ve compiled a structured FAANG SQL practice workbook focused on:

Calendar logic
Window functions
SLA problems
Real system thinking

Because interviews don’t test syntax.
They test reasoning.

How to Calculate the Next Tuesday or Thursday in SQL (FAANG Interview Style)