How to compare with the same weekday last year in Snowflake (364 days)

In many businesses the day of the week matters more than the date: a shop sells most on Saturday, a B2B service almost nothing on Sunday. 16 May 2026 is a Saturday, but 16 May 2025 was a Friday, so a comparison by date sets a Saturday against a Friday. Going back 364 days instead, exactly 52 weeks, always lands on the same weekday.

A small example

Two years of daily sales in which every Saturday sells three times as much as any other day. The business is identical in both years, so every difference in the comparisons below comes from the method, not from the sales.

CREATE TEMP TABLE Sales AS
SELECT OrderDate,
       IFF(DAYOFWEEKISO(OrderDate) = 6, 300, 100) AS Amount   -- Saturdays sell 300
FROM (
  SELECT DATEADD(day, ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1, '2025-01-01'::DATE) AS OrderDate
  FROM TABLE(GENERATOR(ROWCOUNT => 730))
);

GENERATOR creates 730 rows, ROW_NUMBER numbers them without gaps, and DATEADD turns the numbers into the days from 1 January 2025 to 31 December 2026.

Day by day

SELECT d.OrderDate,
       DAYNAME(d.OrderDate) AS Weekday,
       d.Amount,
       c.Amount AS SameDateLastYear,
       w.Amount AS SameWeekdayLastYear
FROM Sales d
LEFT JOIN Sales c ON c.OrderDate = DATEADD(year, -1, d.OrderDate)
LEFT JOIN Sales w ON w.OrderDate = DATEADD(day, -364, d.OrderDate)
WHERE d.OrderDate BETWEEN '2026-05-11' AND '2026-05-17'
ORDER BY d.OrderDate;

By date, Saturday looks 200% up and Sunday 67% down, although nothing changed. By weekday, every day matches.

Period totals

The same applies to a period: move both ends back 364 days. The query compares year to date as of Saturday 16 May 2026 in both ways.

WITH Params AS (
  SELECT '2026-05-16'::DATE AS AsOf
),
Periods AS (
  SELECT 'This year' AS Period,
         DATE_TRUNC('year', AsOf) AS StartDate,
         AsOf AS EndDate
  FROM Params
  UNION ALL
  SELECT 'Last year, same date',
         DATE_TRUNC('year', DATEADD(year, -1, AsOf)),
         DATEADD(year, -1, AsOf)
  FROM Params
  UNION ALL
  SELECT 'Last year, same weekday',
         DATEADD(day, -364, DATE_TRUNC('year', AsOf)),
         DATEADD(day, -364, AsOf)
  FROM Params
)
SELECT p.Period, p.StartDate, p.EndDate, SUM(s.Amount) AS Sales
FROM Periods p
LEFT JOIN Sales s
  ON  s.OrderDate >= p.StartDate
  AND s.OrderDate <  DATEADD(day, 1, p.EndDate)
GROUP BY p.Period, p.StartDate, p.EndDate
ORDER BY p.Period DESC;

This year’s period contains 20 Saturdays, while the same dates in 2025 contain 19. That one Saturday shows up as 1.1% growth in a business that did not grow. The 364-day period has the same number of days and the same weekdays, but it is no longer a calendar year: it runs from 2 January to 17 May and leaves 1 January 2025 out.

Limits of 364 days

• Drift. A year has 365 or 366 days, so 364 days back is one day earlier on the calendar, or two after a 29 February. For a single year-on-year comparison that does not matter. Retail calendars built on 52-week years add a 53rd week every five or six years to catch up. If your company uses one, compare fiscal weeks from its calendar table instead.
• Holidays. Fixed-date holidays move: 25 December 2026 is a Friday, and 364 days earlier is Friday 26 December 2025, not Christmas Day. Around Christmas, New Year and national holidays, a weekday comparison sets a holiday against a normal day. Easter falls on a different date every year, whichever method you use.
• Monthly reports. Months do not contain whole weeks, so for monthly totals readers usually expect the calendar comparison. Use 364 days for daily and weekly views, such as week to date.