Holiday-Aware Calendar Tables

Concepts covered: Aggregate Window Fns, Window Partitioning, Inner Joins

The question carries a single word that changes the tooling: business. "Host payouts release 2 business days after check-in; find the late ones." "Support replies within 3 business days; report the breach rate by country." DATE_ADD has never heard of July 4. Once a rule depends on the status of a day (holiday, weekend, trading day, office closure), that status is data, and data belongs in a table someone maintains.

Why weekday arithmetic fails first

The first attempt usually skips Saturdays and Sundays with EXTRACT(DOW ...). It survives until the first holiday, then fails across markets, because weekends are local too: the UAE moved its weekend from Friday and Saturday to Saturday and Sunday in January 2022. Rules hardcoded in CASE expressions also get copied, and 2 copies edited at different times are how 2 dashboards end up disagreeing about the same SLA.

Weekday arithmetic in the query

• Knows weekends, never holidays
• A single weekend definition for every market
• Copied into each query that needs it
• N business days later needs a loop or a guess

Calendar table

• Every weekend and holiday stored as a row, observed dates included
• A row per market per date, so markets never collide
• A single owner and a single update, so every query agrees
• N business days later becomes an equality join

One row per market per date

The grain is a row per market per date, so a holiday in Lisbon and a holiday in Austin never collide. Each row carries whether the day is a business day, the holiday's name, and the column every due-date calculation reads: business_day_seq, a running count of business days that holds its value across weekends and holidays.

Observed dates belong in the rows too, and they are where calendars split. July 4, 2026 falls on a Saturday, so federal offices close on Friday, July 3, and so do companies that follow the federal schedule. The Federal Reserve stays open that Friday, so banks process payouts over Fedwire and ACH as on any other business day. The staff calendar marks July 3 as a non-business day while the bank calendar keeps it a business day, which is why payouts and support SLAs cannot read the same rows.

Business-day math becomes a join

-- Built once, when the calendar loads
SELECT market, cal_date, is_business_day, holiday_name,
       SUM(CASE WHEN is_business_day THEN 1 ELSE 0 END)
         OVER (PARTITION BY market ORDER BY cal_date
               ROWS UNBOUNDED PRECEDING) AS business_day_seq
FROM calendar_base;

-- Payout due 2 business days after check-in, per market
SELECT b.booking_id, b.check_in, due.cal_date AS payout_due
FROM bookings b
JOIN dim_business_calendar ci
  ON ci.market = b.market
 AND ci.cal_date = b.check_in
JOIN dim_business_calendar due
  ON due.market = b.market
 AND due.business_day_seq = ci.business_day_seq + 2
 AND due.is_business_day;

Walk the interviewer through a row. A check-in on July 3 sits at sequence 1350, so the payout lands on the business day numbered 1352, which is July 8. A Saturday check-in on July 6 inherits 1351 from Friday and lands at 1353, on July 9, the same day as a Friday check-in. That inheritance is a convention, and stating it before you are asked ("a weekend start counts from the last business day") is what a strong answer does. Business days between 2 dates is a subtraction of sequences, so an SLA breach becomes closed.business_day_seq - opened.business_day_seq > 3.

The trap answer walks forward a day at a time with a recursive CTE, checking each day against a holiday list. It returns correct dates and does at least N iterations per row, so 10 million bookings at a 2-day offset run 20 million probes, and a 30-day SLA window runs 300 million. The sequence column replaces the loop with a single equality join, and the interviewer hears the difference between computing business days and looking them up.

Expect the cross-border probe: a US guest books a Lisbon property owned by a host whose bank is in Portugal. The payout follows the host's banking market; the guest's support SLA follows the support team's calendar. Which market goes into the join key is a decision, and the interviewer wants to hear it made out loud.

Somebody owns this table

Holidays are set by governments and companies on their own schedules, so the calendar is reference data. It needs an owner and a load process, and someone has to set a policy for history. When a holiday is added after the fact, are last year's breach rates recomputed or frozen? Recomputing makes old reports disagree with what was published; freezing means versioning the calendar with effective dates. Either is defensible. Leaving it undecided is how finance and support end up quoting different breach rates for the same quarter.

About this lesson

This section is part of the Date Arithmetic: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.