1WITH all_countries AS (
2  SELECT DISTINCT country_name
3  FROM @blt.calendar.public_holidays
4  WHERE subdivision_name = 'National'
5    AND YEAR(date) = 2025
6),
7may1_countries AS (
8  SELECT DISTINCT country_name
9  FROM @blt.calendar.public_holidays
10  WHERE subdivision_name = 'National'
11    AND MONTH(date) = 5
12    AND DAY(date) = 1
13    AND YEAR(date) = 2025
14),
15classified AS (
16  SELECT
17    a.country_name,
18    CASE WHEN m.country_name IS NOT NULL THEN 'May 1st is public holiday' ELSE 'May 1st is normal day' END AS may1_status
19  FROM all_countries a
20  LEFT JOIN may1_countries m ON a.country_name = m.country_name
21)
22SELECT may1_status, COUNT(*) AS num_countries
23FROM classified
24GROUP BY may1_status
25order by 2 desc
26