MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH CTE AS (

SELECT 'r123' sno, 'S1234' mmodel, STR_TO_DATE('03/01/2022', '%m/%d/%Y') as snapshot_date UNION ALL

SELECT 'r3421' sno, 'S1234' mmodel, STR_TO_DATE('03/01/2022', '%m/%d/%Y') as snapshot_date UNION ALL

SELECT 'y123' sno, 'D2123' mmodel, STR_TO_DATE('03/01/2022', '%m/%d/%Y') as snapshot_date UNION ALL

SELECT 'g21q' sno, 'D2123' mmodel, STR_TO_DATE('03/01/2022', '%m/%d/%Y') as snapshot_date UNION ALL

SELECT 'W321' sno, 'G345' mmodel, STR_TO_DATE('03/01/2022', '%m/%d/%Y') as snapshot_date UNION ALL

SELECT 'E231' sno, 'G345' mmodel, STR_TO_DATE('03/01/2022', '%m/%d/%Y') as snapshot_date UNION ALL

SELECT 'r123' sno, 'S1234' mmodel, STR_TO_DATE('03/02/2022', '%m/%d/%Y') as snapshot_date UNION ALL

SELECT 'D891' sno, 'S1234' mmodel, STR_TO_DATE('03/02/2022', '%m/%d/%Y') as snapshot_date UNION ALL

SELECT 'y123' sno, 'D2123' mmodel, STR_TO_DATE('03/02/2022', '%m/%d/%Y') as snapshot_date UNION ALL

SELECT 'W321' sno, 'G345' mmodel, STR_TO_DATE('03/02/2022', '%m/%d/%Y') as snapshot_date UNION ALL

SELECT 'K1231' sno, 'J1231' mmodel, STR_TO_DATE('03/02/2022', '%m/%d/%Y') as snapshot_date UNION ALL

SELECT 'r123' sno, 'S1234' mmodel, STR_TO_DATE('03/03/2022', '%m/%d/%Y') as snapshot_date UNION ALL

SELECT 'D891' sno, 'S1234' mmodel, STR_TO_DATE('03/03/2022', '%m/%d/%Y') as snapshot_date UNION ALL

SELECT 'H213' sno, 'V231' mmodel, STR_TO_DATE('03/03/2022', '%m/%d/%Y') as snapshot_date UNION ALL

SELECT '121k' sno, 'N213' mmodel, STR_TO_DATE('03/03/2022', '%m/%d/%Y') as snapshot_date)

SELECT T1.snapshot_date, count(*) gap

FROM CTE T1

LEFT OUTER JOIN CTE T2

on T1.sno = T2.sno

and T1.mmodel = T2.mmodel

and T1.snapshot_date = AddDate(T2.snapshot_date, INTERVAL -1 Day)

WHERE T2.sno is null

GROUP BY T1.snapshot_date