用于获取具有最大匹配ids数的日历周的SQL查询

CREATE VIEW calendar_weeks AS
SELECT calendar.date AS start_date, DATE_ADD(calendar.date, INTERVAL 6 DAY) AS end_date
FROM calendar
INNER JOIN weeks ON calendar.date BETWEEN weeks.start_date AND weeks.end_date

SELECT calendar.start_date, calendar.end_date, COUNT(events.id) AS matching_ids
FROM calendar_weeks AS calendar
LEFT JOIN events ON events.start_date <= calendar.end_date AND events.end_date >= calendar.start_date
GROUP BY calendar.start_date, calendar.end_date

SELECT start_date, end_date, matching_ids
FROM (
  SELECT calendar.start_date, calendar.end_date, COUNT(events.id) AS matching_ids
  FROM calendar_weeks AS calendar
  LEFT JOIN events ON events.start_date <= calendar.end_date AND events.end_date >= calendar.start_date
  GROUP BY calendar.start_date, calendar.end_date
) AS result
WHERE matching_ids = (SELECT MAX(matching_ids) FROM result)