with a as(
    select
        SALES_DATE dt,
        cast((strftime('%w', SALES_DATE)) as integer) wk,
        sales_amt
    from
        sales
    where
        SALES_DATE between '2024-03-01'
        and '2024-03-28'
        and SALES_TYPE <> 2
        and UPDATED_NO is null
),
SEQ AS (
    SELECT
        0 as no
    UNION
    ALL
    SELECT
        no + 1 as no
    FROM
        SEQ
    WHERE
        no + 1 < 7
)
select
    WEEK,
    amt as 'AVG_CNT',
    (
        WITH SEQ AS ( SELECT amt2 A, NULL B, 0 AS NO UNION ALL SELECT SUBSTR(A, 1, LENGTH(A) -3), SUBSTR(A, -3) B, NO + 1 FROM SEQ WHERE LENGTH(A) >= 1)
        SELECT GROUP_CONCAT(B) FROM ( SELECT * FROM SEQ ORDER BY NO DESC) WHERE B IS NOT NULL
    ) 
    || '円' AS 'AVG_AMT'
from
    (
        select
            seq.no no,
            substr('日月火水木金土日', seq.no + 1, 1) as WEEK,
            ifnull(
                cast(
                    round(cast(count(a.dt) as real) / 4.0, 0) as integer
                ),
                0
            ) amt,
            ifnull(
                cast(
                    round(cast(sum(SALES_AMT) as real) / 4.0, 0) as integer
                ),
                0
            ) amt2
        from
            seq
            left join a on seq.no = a.wk
        group by
            seq.no
    )
order by
    no