WITH RECURSIVE dates(date) AS (
  VALUES('2024-03-01')
  UNION ALL
  SELECT date(date, '+1 day')
  FROM dates
  WHERE date < '2024-03-07'
), dow AS(
SELECT 
    CASE (strftime('%w', DATE))
        WHEN '0' THEN '日' 
        WHEN '1' THEN '月' 
        WHEN '2' THEN '火' 
        WHEN '3' THEN '水' 
        WHEN '4' THEN '木' 
        WHEN '5' THEN '金' 
        WHEN '6' THEN '土' END AS WEEK
    , (strftime('%w', DATE)) AS week_order
FROM 
    dates
), calc AS(
SELECT
    strftime('%w', SALES_DATE) AS week
    , CAST(COUNT(SALES_NO)/4 AS INT) AS AVG_CNT
    , CAST(SUM(SALES_AMT)/4 AS INT)AS AMT
FROM
    SALES
WHERE
    SALES_DATE < '2024-03-29'
    AND SALES_DATE >= '2024-03-01'
    AND SALES_TYPE != 2
    AND UPDATED_NO IS NULL
GROUP BY
    week
)
SELECT
    dow.week
    , IFNULL(AVG_CNT, 0) AS AVG_CNT
    , CASE 
        WHEN LENGTH(AMT) > 6 
            THEN SUBSTR(AMT, 1, LENGTH(AMT) - 6) || ',' || SUBSTR(AMT, LENGTH(AMT) - 5, 3)
         || ',' || SUBSTR(AMT, LENGTH(AMT) - 2, 3) 
        WHEN LENGTH(AMT) > 3 
            THEN SUBSTR(AMT, 1, LENGTH(AMT) - 3) || ',' || SUBSTR(AMT, LENGTH(AMT) - 2, 3)
        ELSE IFNULL(AMT, '0') 
        END || '円' AS AVG_AMT 
FROM
    dow
LEFT JOIN
    calc
ON
    dow.week_order = calc.week
ORDER BY
    week_order