SQLite select data past actual 7 days ( week ) and if not exist null/zero

Question

I'm building a finance app and need to select some specific data that is on the actual past 7 days even if one of the dates not exists just return 0 or null

To be specific I have a transactions table and has value I need to return the total of transactions per day for the last 7 days of today not the last 7 days on the database

So What I need is something like this!

-----------
total|date
-----------
150 | 22/9
50  | 21/9
0   | 20/9
100 | 19/9
800 | 18/9
1500| 17/9
0   | 16/9

My current query which is returning the total of transactions per day and date field of that day

SELECT SUM(value), date as value FROM transactions GROUP BY STRFTIME('%d', DATE(date, 'unixepoch'))

Answer 1

You need a recursive CTE that returns the previous 7 dates and a LEFT join to the table to aggregate:

WITH week(date) AS (
  SELECT date('now', '-7 day') 
  UNION ALL 
  SELECT date(date, '+1 day') 
  FROM week 
  WHERE date < date('now', '-1 day') 
)
SELECT TOTAL(t.value) total, 
       strftime('%d/%m', w.date) date
FROM week w LEFT JOIN transactions t
ON date(t.date, 'unixepoch') = w.date
GROUP BY w.date
ORDER BY w.date DESC;