WITH RECURSIVE CALENDAR(CALENDAR_DATE) AS (
    SELECT
        DATE('2024-03-01') AS CALENDAR_DATE
    UNION ALL
    SELECT
        DATE(CALENDAR_DATE, '+1 day') AS CALENDAR_DATE
    FROM CALENDAR
    WHERE
        CALENDAR_DATE < DATE('2024-03-28')
)
,SALECAL AS (
  SELECT C.CALENDAR_DATE
        ,SUBSTR('日月火水木金土', STRFTIME("%w" ,C.CALENDAR_DATE) + 1, 1) AS WEEK
        ,STRFTIME("%w" ,C.CALENDAR_DATE) AS WEEKO
        ,IFNULL(S.AMT, 0) AS AMT
        ,IFNULL(S.DAYCNT, 0) AS DAYCNT
    FROM CALENDAR C
    LEFT OUTER JOIN (SELECT SALES_DATE
                           ,SUM(SALES_AMT) AMT
                           ,COUNT(SALES_DATE) DAYCNT
                       FROM SALES
                      WHERE SALES_TYPE <> 2
                        AND UPDATED_NO IS NULL
                      GROUP BY SALES_DATE
                     ) S
      ON C.CALENDAR_DATE = SALES_DATE
)
SELECT SALECAL.WEEK
      ,ROUND(AVG(SALECAL.DAYCNT)) AS AVG_CNT
      , CASE WHEN LENGTH(CAST(ROUND(AVG(SALECAL.AMT)) AS INT)) > 6 THEN SUBSTR(CAST(ROUND(AVG(SALECAL.AMT)) AS INT),1,LENGTH(CAST(ROUND(AVG(SALECAL.AMT)) AS INT))-6) || ',' || SUBSTR(CAST(ROUND(AVG(SALECAL.AMT)) AS INT),LENGTH(CAST(ROUND(AVG(SALECAL.AMT)) AS INT))-5,3) || ',' || SUBSTR(CAST(ROUND(AVG(SALECAL.AMT)) AS INT),LENGTH(CAST(ROUND(AVG(SALECAL.AMT)) AS INT))-2,3)
             WHEN LENGTH(CAST(ROUND(AVG(SALECAL.AMT)) AS INT)) > 3 THEN SUBSTR(CAST(ROUND(AVG(SALECAL.AMT)) AS INT),1,LENGTH(CAST(ROUND(AVG(SALECAL.AMT)) AS INT))-3) || ',' || SUBSTR(CAST(ROUND(AVG(SALECAL.AMT)) AS INT),LENGTH(CAST(ROUND(AVG(SALECAL.AMT)) AS INT))-2,3)
             ELSE  CAST(ROUND(AVG(SALECAL.AMT)) AS INT)
         END || '円' AS AVG_AMT
 FROM SALECAL
 GROUP BY SALECAL.WEEK
 ORDER BY SALECAL.WEEKO