WITH RECURSIVE YOUBI(YOU) AS ( 
    -- 曜日０～６(日～土)
    SELECT
       0 as YOU
    UNION ALL
    
    SELECT
        YOU +1  AS YOU
    FROM
        YOUBI
    WHERE
        YOU  < 6
)
,SL_HI AS ( 
    -- 日別件数金額
    SELECT
         strftime('%w', SALES.SALES_DATE)  as YOU 
        ,SALES.SALES_DATE
        ,sum(SALES.SALES_AMT) as AMT_SUM
        ,COUNT(*) as AVG_CNT
    FROM
        SALES
    WHERE
        DATE(SALES.SALES_DATE) BETWEEN '2024-03-01' AND '2024-03-28'
        and
        SALES.SALES_TYPE <> 2
        and
        SALES.UPDATED_NO is null
    GROUP BY
        SALES.SALES_DATE
)

    SELECT
         case
            when YOUBI.YOU = 0 then "日"
            when YOUBI.YOU = 1 then "月"
            when YOUBI.YOU = 2 then "火"
            when YOUBI.YOU = 3 then "水"
            when YOUBI.YOU = 4 then "木"
            when YOUBI.YOU = 5 then "金"
            when YOUBI.YOU = 6 then "土"
            end as WEEK
        ,case
            when SL_HI.AVG_CNT is null then 0
            else round(avg(SL_HI.AVG_CNT),0)
            end as AVG_CNT
        ,case
            when SL_HI.AMT_SUM is null then 0 ||"円"
            when length(cast(round(avg(SL_HI.AMT_SUM)) as int)) > 6 
                then substr(round(avg(SL_HI.AMT_SUM),-1),1,length(cast(round(avg(SL_HI.AMT_SUM)) as int)) -6) || "," ||
                     substr(round(avg(SL_HI.AMT_SUM),-1),length(cast(round(avg(SL_HI.AMT_SUM)) as int)) -5,3) || "," ||
                     substr(round(avg(SL_HI.AMT_SUM),-1),length(cast(round(avg(SL_HI.AMT_SUM)) as int)) -2,3) || "円" 
            when length(cast(round(avg(SL_HI.AMT_SUM)) as int)) > 3 
                then substr(round(avg(SL_HI.AMT_SUM),0),1,length(cast(round(avg(SL_HI.AMT_SUM)) as int)) -3) || "," ||
                     substr(round(avg(SL_HI.AMT_SUM),0),length(cast(round(avg(SL_HI.AMT_SUM)) as int)) -2,3) || "円"
            else cast(round(avg(SL_HI.AMT_SUM)) as int) || "円"

            end as AVG_AMT
    FROM
        YOUBI 
        left join 
        SL_HI on YOUBI.YOU =  cast(SL_HI.YOU as int)
    GROUP BY
        YOUBI.YOU
; 
--select * from SL_HI
--;