WITH RECURSIVE dates(date) AS (
  SELECT DATE('2024-03-01')
  UNION ALL
  SELECT date(date, '+1 day')
  FROM dates
  WHERE date < DATE('2024-03-28')
)
SELECT 
  CASE 
    WHEN strftime('%w', d.date) = '0' THEN '日'
    WHEN strftime('%w', d.date) = '1' THEN '月'
    WHEN strftime('%w', d.date) = '2' THEN '火'
    WHEN strftime('%w', d.date) = '3' THEN '水'
    WHEN strftime('%w', d.date) = '4' THEN '木'
    WHEN strftime('%w', d.date) = '5' THEN '金'
    ELSE '土'
  END AS WEEK,
  ROUND(AVG(CASE WHEN s.SALES_DATE IS NOT NULL THEN 1 ELSE 0 END), 0) AS AVG_CNT,
  CASE
    WHEN LENGTH(AVG(s.SALES_AMT)) > 6
      THEN substr(AVG(s.SALES_AMT),1,length(AVG(s.SALES_AMT))-6) || ',' || substr(AVG(s.SALES_AMT),length(AVG(s.SALES_AMT))-5,3) || ',' || substr(AVG(s.SALES_AMT),length(AVG(s.SALES_AMT))-2,3)
    WHEN  length(AVG(s.SALES_AMT)) > 3
      THEN substr(AVG(s.SALES_AMT),1,length(AVG(s.SALES_AMT))-3) || ',' || substr(AVG(s.SALES_AMT),length(AVG(s.SALES_AMT))-2,3)
    else  0
  END || '円' AS AVG_AMT
FROM
  dates d
LEFT JOIN
  SALES s ON d.date = s.SALES_DATE
GROUP BY 
  WEEK
ORDER BY 
  strftime('%w', d.date);