with tmp1 as (
        select SALES_DATE,
        COUNT(SALES_NO) CNT,
        SUM(SALES_AMT) AMT
        from SALES
        where
        SALES_DATE between '2024-03-01' and '2024-03-28'
        and SALES_TYPE!=2
        AND UPDATED_NO is NULL
        group by SALES_DATE
        ),
        tmp2 as(SELECT 
         strftime("%w", SALES_DATE) +1 as WEEKD,
         round(AVG(CNT),0) AVG_CNT,
         cast(cast( round(AVG(AMT),0) as integer) as text) AVG_AMT
        FROM tmp1
        group by 1
        ),tmp3 as (select 1 nn union select 2 nn 
        union select 3 nn union 
        select 4 nn union select 5 nn union select 6 nn union select 7 nn)
         
        SELECT
        substr('日月火水木金土',nn,1) WEEK,
        CASE WHEN 
        AVG_AMT is null then '0円'
        WHEN length(AVG_AMT)>6 then 
        substr(AVG_AMT,1,length(AVG_AMT)-6) || ',' || substr(AVG_AMT,length(AVG_AMT)-5,length(AVG_AMT)-4) || ',' || 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 AVG_AMT,
        CASE WHEN
        AVG_CNT is null then '0'
        ELSE AVG_CNT END AVG_CNT
        FROM 
        tmp3 left join tmp2 on tmp3.nn=tmp2.weekd
        order by nn;