WITH TMP(W_NUM, WEEK) AS (
    SELECT 0, '日'
    UNION
    SELECT 1, '月'
    UNION
    SELECT 2, '火'
    UNION
    SELECT 3, '水'
    UNION
    SELECT 4, '木'
    UNION
    SELECT 5, '金'
    UNION
    SELECT 6, '土'
), TMP2(WEEK, W_NUM, AVG_CNT, AVG_AMT) AS (
    SELECT
        T.WEEK AS WEEK,
        T.W_NUM,
        CASE
            WHEN S.AVG_CNT IS NULL THEN 0
            ELSE S.AVG_CNT
        END 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 TMP AS T
    LEFT OUTER JOIN(
        SELECT
            W_NUM,
            ROUND(AVG(CNT)) AS AVG_CNT,
            CAST(ROUND(AVG(SUM_AMT)) AS INT) AS AVG_AMT
        FROM (
                SELECT 
                    CAST(STRFTIME('%w',DATE(SALES_DATE)) AS INT) AS W_NUM,
                    SUM(CASE 
                            WHEN SALES_TYPE=2 OR UPDATED_NO IS NOT NULL THEN 0
                            ELSE 1
                        END) AS CNT,
                    SUM(CASE 
                            WHEN SALES_TYPE=2 OR UPDATED_NO IS NOT NULL THEN 0
                            ELSE SALES_AMT
                        END) AS SUM_AMT
                FROM SALES
                GROUP BY SALES_DATE
            )
        GROUP BY W_NUM
        ) AS S
        ON T.W_NUM=S.W_NUM
    GROUP BY T.W_NUM
)
SELECT
    WEEK,
    COALESCE(AVG_CNT,0) AS AVG_CNT,
    COALESCE(AVG_AMT,0 || '円') AS AVG_AMT
FROM TMP2
ORDER BY W_NUM;