SQL入門
SQL基礎問題11：連続期間の開始月と終了月を抽出

SELECT A.ID,A.B_ID,B.C_IDs,IFNULL(SUM(C.値), 0) AS 値合計
FROM TBLA AS A
LEFT JOIN TBLB AS B
       ON A.B_ID = B.ID
LEFT JOIN TBLC AS C
       ON (',' || B.C_IDs || ',') LIKE ('%,' || C.ID || ',%')
      AND (C.終了日 > STRFTIME('%Y/%m/%d', DATE('now')) OR C.終了日 IS NULL)
GROUP BY A.ID,A.B_ID,B.C_IDs

SELECT A.ID,A.B_ID,B_SUM.C_IDs,IFNULL(B_SUM.合計値, 0) AS 値合計
FROM TBLA AS A
LEFT JOIN (SELECT B.ID AS B_ID,B.C_IDs,SUM(C.値) AS 合計値
           FROM TBLB AS B
           LEFT JOIN TBLC AS C
                  ON (',' || B.C_IDs || ',') LIKE ('%,' || C.ID || ',%')
                 AND (C.終了日 > DATE('now') OR C.終了日 IS NULL)
           GROUP BY B.ID, B.C_IDs
          ) AS B_SUM
       ON A.B_ID = B_SUM.B_ID
GROUP BY A.ID, A.B_ID, B_SUM.C_IDs

=LET(sql,
"SELECT S.user_id,S.start_month,Min(E.end_month) AS end_month
 FROM (SELECT T.user_id, T.year_month AS start_month
       FROM ?TBL AS T
       WHERE NOT EXISTS (
             SELECT 1 FROM ?TBL AS P
             WHERE P.user_id = T.user_id
             AND P.year_month = DateAdd('m', -1, T.year_month)
             )
      ) AS S
 INNER JOIN
      (SELECT T.user_id, T.year_month AS end_month
       FROM ?TBL AS T
       WHERE NOT EXISTS (
             SELECT 1 FROM ?TBL AS N
             WHERE N.user_id = T.user_id
             AND N.year_month = DateAdd('m', 1, T.year_month)
             )
      ) AS E
      ON S.user_id = E.user_id AND E.end_month >= S.start_month
GROUP BY S.user_id, S.start_month
ORDER BY S.user_id, S.start_month
",
sql_1,SUBSTITUTE(sql,"?TBL","[active_months$A1:C17]"),
QUERY(sql_1,,TRUE))