SELECT SUBSTR('日月火水木金土', W + 1, 1) WEEK
, COALESCE(ROUND(AVG(CNT)), 0) AVG_CNT
, PRINTF('%,d円', COALESCE(ROUND(AVG(AMT)), 0)) AVG_AMT
FROM (
  SELECT SALES_DATE
  , CAST(STRFTIME('%w', SALES_DATE) AS INT) W
  , COUNT(*) CNT
  , ROUND(SUM(SALES_AMT)) AMT
  FROM SALES
  WHERE SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
  AND SALES_TYPE NOT IN(2)
  AND UPDATED_NO IS NULL
  GROUP BY SALES_DATE
UNION ALL SELECT NULL SALES_DATE, 0 W, NULL CNT, NULL AMT
UNION ALL SELECT NULL SALES_DATE, 1 W, NULL CNT, NULL AMT
UNION ALL SELECT NULL SALES_DATE, 2 W, NULL CNT, NULL AMT
UNION ALL SELECT NULL SALES_DATE, 3 W, NULL CNT, NULL AMT
UNION ALL SELECT NULL SALES_DATE, 4 W, NULL CNT, NULL AMT
UNION ALL SELECT NULL SALES_DATE, 5 W, NULL CNT, NULL AMT
UNION ALL SELECT NULL SALES_DATE, 6 W, NULL CNT, NULL AMT
) t
GROUP BY W
ORDER BY W