WITH WEEK_TBL(W_NUM, W_NAME) AS (
    SELECT '0', '日'
    UNION 
    SELECT '1', '月'
    UNION
    SELECT '2', '火'
    UNION
    SELECT '3', '水'
    UNION
    SELECT '4', '木'
    UNION
    SELECT '5', '金'
    UNION
    SELECT '6', '土'
), SUM_SALES(W_NUM, AVG_CNT, AVG_AMT) AS (
    SELECT 
        STRFTIME('%w', DATE(SALES_DATE)) AS W_NUM,
        ROUND(CAST(COUNT(SALES_NO) AS REAL) / 4),
        CAST(ROUND(CAST(SUM(SALES_AMT) AS REAL) / 4) AS INT)
    FROM SALES
    WHERE SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28' 
        AND SALES_TYPE <> 2 
        AND UPDATED_NO IS NULL
    GROUP BY W_NUM
)

SELECT
    W.W_NAME AS WEEK,
    IFNULL(S.AVG_CNT, 0) AS AVG_CNT,
    CASE 
        WHEN S.AVG_AMT IS NULL THEN 0
        ELSE LTRIM(SUBSTR(S.AVG_AMT,-6,-3) || ',' ||
                  SUBSTR(S.AVG_AMT,-3,-3) || ',' ||
                  SUBSTR(S.AVG_AMT,LENGTH(S.AVG_AMT)+1,-3)
                ,',') 
    END || '円' AS AVG_AMT
FROM WEEK_TBL AS W
LEFT OUTER JOIN SUM_SALES AS S
    ON W.W_NUM=S.W_NUM
ORDER BY 
    W.W_NUM ASC;