Try this:
TABLE WITHOUT ID weekday , length(rows) as count
FROM "<your-folder>"
GROUP BY dateformat(date , "E cccc") as weekday
If Obsidian recognises your date format as a valid date, it should work out of the box. If not change the last line to
group by GROUP BY dateformat(date(date, "yyyy-mm-dd"), "E cccc") as weekday