with WEEK_TBL AS ( 
    SELECT
        '2024-03-01' AS CLDATE
        , STRFTIME('%w', '2024-03-01') AS WEEK_NO
        , SUBSTR('日月火水木金土', STRFTIME('%w', '2024-03-01') + 1, 1) AS WEEK_NAME 
    UNION ALL 
    SELECT
        DATE (CLDATE, '+1 days')
        , STRFTIME('%w', DATE (CLDATE, '+1 days'))
        , SUBSTR('日月火水木金土',STRFTIME('%w', DATE (CLDATE, '+1 days')) + 1, 1) 
    FROM WEEK_TBL 
    WHERE CLDATE < '2024-03-07'
) 
select
    week_name AS WEEK,
    ifnull(avg_cnt,0) AS AVG_CNT,
    ifnull(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 amt
    end,0)||'円' AS AVG_AMT
from(
    select
        strftime('%w', sales_date) as week_num,
        round(cast(count(sales_no) AS REAL) / 4)  AS avg_cnt,
        cast(round(sum(sales_amt)/4,0) AS INT) AS amt
    from sales
    where updated_no is null and sales_type != 2
    and sales_date between '2024-03-01' AND '2024-03-28'
    group by week_num
    ) as a right outer join week_tbl as b on a.week_num = b.week_no
order by b.week_no