Calculate number of specific days between dates in Snowflake

Question

I am looking for some guidance on finding number of occurrences of lets say Monday and Wednesday between two dates Date1 and Date2 inclusive of the two dates in Snowflake. Any Suggestions?

Answer 1

A standard practice is to build a calendar table: either as pernament table or inlined view.

CREATE TABLE calendar
AS
SELECT DATEADD(day, ROW_NUMBER() OVER(ORDER BY seq8()), '1999-12-31'::DATE) AS d,
      DAYOFWEEK(d) AS day_of_week,
      DAYNAME(d) AS day_name
      -- month/quarter/year/...
FROM TABLE(GENERATOR(ROWCOUNT => 365*100));

Then:

SELECT c.day_name, COUNT(*) AS cnt
FROM calendar c
WHERE c.d BETWEEN '<date_1>' AND '<date_2>'
  AND c.day_of_week IN (1,3)
GROUP BY c.day_name;

Notes: day of week depeneds on parameter WEEK_START.