WITH RECURSIVE
    tmp AS (
        SELECT
            SALES_DATE,
            COUNT(*) AS CNT,
            SUM(SALES_AMT) AS SALES_AMT
        FROM
            SALES
        WHERE
            SALES_TYPE <> 2
            AND UPDATED_NO IS NULL
        GROUP BY
            SALES_DATE
    ),
    DateTable AS (
        SELECT DATE('2024-03-01') AS Date
        UNION ALL
        SELECT DATE(Date, '+1 day')
        FROM DateTable
        WHERE Date < '2024-03-28'
    ),
    tmp_sum AS (
    SELECT
        d.Date,
        CASE strftime('%w', d.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,
        CASE
            WHEN SUM(t.CNT) IS NULL THEN 0
            ELSE SUM(t.CNT)
        END AS CNT,
        CASE
            WHEN SUM(t.SALES_AMT) IS NULL THEN 0
            ELSE SUM(t.SALES_AMT)
        END AS SALES_AMT,
        strftime('%w', d.Date) AS tmp
    FROM
        DateTable AS d
    LEFT JOIN
        tmp AS t
    ON
        d.Date = t.SALES_DATE
    GROUP BY
        d.Date
    ), tmp_sumsum AS(
        SELECT 
            WEEK
            ,tmp
            ,ROUND(AVG(CNT),0) AS AVG_CNT
            ,CAST (ROUND(AVG(SALES_AMT), 0) AS INTEGER) AS AVG_AMT
        FROM tmp_sum
        GROUP BY WEEK,tmp
        ORDER BY tmp
    )
    
SELECT
    WEEK,
    AVG_CNT,
    CASE
        WHEN length(AVG_AMT) > 6 THEN substr(AVG_AMT, 1, length(AVG_AMT) - 6) || ',' || substr(AVG_AMT, length(AVG_AMT) - 5, 3) || ',' || substr(AVG_AMT, length(AVG_AMT) - 2, 3) || '円'
        WHEN length(AVG_AMT) > 3 THEN substr(AVG_AMT, 1, length(AVG_AMT) - 3) || ',' || substr(AVG_AMT, length(AVG_AMT) - 2, 3) || '円'
        ELSE AVG_AMT || '円'
    END AS AVG_AMT
FROM
    tmp_sumsum
ORDER BY tmp