Calculate number of specific days between dates in Snowflake

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.

Lukasz's table solution is quite neat, but I will try the JS version:

// not sure the best name to have here
create or replace function num_of_days_in_between(
  day_nums varchar(255), 
  start_date varchar(10), 
  end_date varchar(10)
) 
RETURNS string
LANGUAGE JAVASCRIPT 
AS $$
  // perform some validations first on parameters
  
  var s = new Date(START_DATE);
  var e = new Date(END_DATE);

  var split = DAY_NUMS.split(",");

  var count = 0;
  var d = s;

  // go through each day and check if the 
  // current day is in the days asked
  while (d.getTime() <= e.getTime()) {
    // split array contains strings, so we need to
    // force getDay() value to be string as well,
    // otherwise includes() will not find it
    if(split.includes(d.getDay()+"")) {
      count++;
    }

    // advance to the next day
    d = new Date(d.getTime() + 86400000);
  }

  return count;
$$;

-- Monday and Wednesday
select num_of_days_in_between('1,3', '2021-10-01', '2021-11-01'); -- 9
-- Tuesday
select num_of_days_in_between('2', '2021-10-01', '2021-11-01'); -- 4

Note that in JS, days start from Sunday with index of 0, refer to Date.getDay().