YTD Calculations in MySQL from (SELECT statement) including All Days of Year

Question

Given this query...

select
    sum(count) as quotes,
    date
from (
      select 
         1 as count, 
         DATE_FORMAT(CONVERT_TZ(createdAt, 'UTC', 'US/Pacific'), "%Y-%m-%d") as date 
      from quotes 
      where deletedAt IS NULL
    ) q1 
group by date 
order by date;

I get the following results (showing 2020-02 results only, but actual results would go back several years)...

NOTE: 2020-02-02 received 0 quotes and is missing

+-------+------------+
| count |    date    |
+-------+------------+
|     1 | 2020-02-01 |
|     2 | 2020-02-03 |
|     1 | 2020-02-04 |
|     1 | 2020-02-05 |
|     1 | 2020-02-06 |
|     1 | 2020-02-07 |
|     3 | 2020-02-08 |
|     3 | 2020-02-09 |
|     3 | 2020-02-10 |
|     1 | 2020-02-11 |
+-------+------------+

How do I modify the query to...

• Fill in the missing days (e.g. 2020-02-02 in this example)
• add the ytdCount column, which is a rolling count by year

so that the output is like this...

             add the ytdCount column
             \/
+-------+----------+------------+
| count | ytdCount |    date    |
+-------+----------+------------+
|     1 |        1 | 2020-02-01 |
|     0 |        1 | 2020-02-02 | <- was missing from previous example
|     2 |        3 | 2020-02-03 |
|     1 |        4 | 2020-02-04 |
|     1 |        5 | 2020-02-05 |
|     1 |        6 | 2020-02-06 |
|     1 |        7 | 2020-02-07 |
|     3 |       10 | 2020-02-08 |
|     3 |       13 | 2020-02-09 |
|     3 |       16 | 2020-02-10 |
|     1 |       17 | 2020-02-11 |
+-------+----------+------------+

References

• I found MYSQL to calculate YTD which shows how to do this if I were selecting from a simple table, but since my "table" is actually a select statement, I'm not sure how to translate the example to my use case.
• I found get all dates in the current month which shows how to generate all the dates in a month, but I need all the days for a particular year.

Answer 1

I solved the problem by creating two temporary tables and a UNION

Create temp table for quotes

 DROP TABLE IF EXISTS __quotes__;
 CREATE TABLE __quotes__ (quotes INT(11), ytdQuotes INT(11), date DATE)
      ENGINE=MEMORY
      AS
      select
        sum(count) as quotes,
        NULL as ytdQuotes,
        date
      from (select 1 as count, DATE_FORMAT(CONVERT_TZ(createdAt, 'UTC', 'US/Pacific'), "%Y-%m-%d") as date from quotes where deletedAt IS NULL) q1 group by date order by date;

Create temp table for the date range

note: you can easily get more/less days by changing the wear clause

      DROP TABLE IF EXISTS __daterange__;
      CREATE TABLE __daterange__ (quotes INT(11), ytdQuotes INT(11), date DATE)
      ENGINE=MEMORY
      AS
      select
        NULL as quotes,
        NULL as ytdQuotes
        date
      from (select date
        from (
          SELECT a.date
              FROM (
                  SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + + (1000 * d.a)) DAY AS date
                  FROM       (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
                  CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
                  CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
                  CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS d) a
        ) daterange
        where date >= "2012-11-14") daterange;

Finally, here's the query that brings it all together

select
  coalesce(sum(quotes), 0) as quotes,
  coalesce((select sum(q2.quotes) from __quotes__ q2 where YEAR(report.date) = YEAR(q2.date) AND q2.date <= report.date), 0) as ytdQuotes,
  date
from (
  select * from __quotes__
  UNION ALL
  select * from __daterange__
) report
group by date