How to query backwards on all the 15th of the month since the first date in the database?

Question

I have a script that gives me the number of people who have opened an email or started a session, for each day encountered since at least last year in my MySQL5.7 database.

SELECT lang, count(distinct user_id)
        FROM ((SELECT user_id, lang
               FROM sessions
               WHERE session_time > NOW() - INTERVAL 1 YEAR
              ) UNION ALL
              (SELECT user_id, lang
               FROM unique_open_emails
               WHERE date > NOW() - INTERVAL 1 YEAR
              )
             ) u
        GROUP BY lang

It returns:

lang    count(distinct user_id)
en  1
es  3
fr  3

I would like to be able to do the same for every 15th of the month since the first date in the database.

That is, something like:

   date       lang  count(distinct user_id)
   15/08/2021 en    1
   15/08/2021 es    3
   15/08/2021 fr    3
   15/07/2021 en    1
   15/07/2021 es    3
   15/07/2021 fr    3
   ...
   15/08/2020 en    0
   15/08/2020 es    0
   15/08/2020 fr    0

Here is the Fiddle and the database schema:

CREATE TABLE sessions
    (`App Type` varchar(11), `Lang` varchar(2), `Session ID` int, `session_time` datetime, `user_id` int)
;
    
INSERT INTO sessions
    (`App Type`, `Lang`, `Session ID`, `session_time`, `user_id`)
VALUES
    #july 2021
    ('browser', 'fr', 46960339, '2021-07-29 00:00:00', 1292997),
    ('browser', 'es', 46960407, '2021-07-29 00:00:00', 1055040),
    ('pwa_android', 'fr', 46960412, '2021-07-29 00:00:00', 1120109),
    ('browser', 'fr', 46960391, '2021-07-29 00:00:00', 955480),
    ('pwa_android', 'en', 46960389, '2021-07-29 00:00:00', 800169),
    ('browser', 'es', 46960353, '2021-07-29 00:00:00', 1193745),
    # june 2021
    ('browser', 'fr', 46960339, '2021-06-29 00:00:00', 1292997),
    ('browser', 'es', 46960407, '2021-06-29 00:00:00', 1055040),
    ('pwa_android', 'fr', 46960412, '2021-06-29 00:00:00', 1120109),
    ('browser', 'fr', 46960391, '2021-06-29 00:00:00', 955480),
    ('pwa_android', 'en', 46960389, '2021-06-29 00:00:00', 800169),
    ('browser', 'es', 46960353, '2021-06-29 00:00:00', 12345),
    # june 2020
    ('browser', 'fr', 46960339, '2020-06-29 00:00:00', 1292997),
    ('browser', 'es', 46960407, '2020-06-29 00:00:00', 1055040),
    ('pwa_android', 'fr', 46960412, '2020-06-29 00:00:00', 1120109),
    ('browser', 'fr', 46960391, '2020-06-29 00:00:00', 955480),
    ('pwa_android', 'en', 46960389, '2020-06-29 00:00:00', 800169),
    ('browser', 'es', 46960353, '2020-06-29 00:00:00', 54321)
;


CREATE TABLE unique_open_emails
    (`date` datetime, `lang` varchar(2), `user_id` int)
;
    
INSERT INTO unique_open_emails
    (`date`, `lang`, `user_id`)
VALUES
    ('2016-04-12 00:00:00', 'fr', 115434),
    ('2016-04-13 00:00:00', 'fr', 11357),
    ('2016-04-13 00:00:00', 'fr', 137481),
    ('2016-04-13 00:00:00', 'fr', 10296),
    ('2016-04-13 00:00:00', 'fr', 125772),
    ('2016-04-13 00:00:00', 'fr', 955480),
    ('2016-04-13 00:00:00', 'fr', 9269),
    ('2016-04-13 00:00:00', 'fr', 90716),
    ('2016-04-13 00:00:00', 'fr', 26330),
    ('2016-04-13 00:00:00', 'fr', 89072),
    ('2016-04-13 00:00:00', 'fr', 87416),
    ('2016-04-13 00:00:00', 'fr', 88358),
    ('2016-04-13 00:00:00', 'fr', 102515),
    ('2016-04-13 00:00:00', 'fr', 89867),
    ('2016-04-13 00:00:00', 'fr', 119146),
    ('2016-04-13 00:00:00', 'fr', 133316),
    ('2016-04-13 00:00:00', 'fr', 90095),
    ('2016-04-13 00:00:00', 'fr', 16510),
    ('2016-04-13 00:00:00', 'fr', 21530),
    ('2016-04-13 00:00:00', 'fr', 81581),
    ('2016-04-13 00:00:00', 'es', 54321),
    ('2016-04-13 00:00:00', 'fr', 29363),
    ('2016-04-13 00:00:00', 'fr', 90326),
    ('2016-04-13 00:00:00', 'fr', 23961),
    ('2016-04-13 00:00:00', 'fr', 89000),
    ('2016-04-13 00:00:00', 'fr', 9484),
    ('2016-04-13 00:00:00', 'fr', 11845),
    ('2016-04-13 00:00:00', 'fr', 41231),
    ('2016-04-13 00:00:00', 'fr', 588),
    ('2016-04-13 00:00:00', 'fr', 16678),
    ('2016-04-13 00:00:00', 'fr', 19674),
    ('2016-04-13 00:00:00', 'fr', 130113),
    ('2016-04-13 00:00:00', 'fr', 84719),
    ('2016-04-13 00:00:00', 'fr', 123252),
    ('2016-04-13 00:00:00', 'fr', 4676),
    ('2016-04-13 00:00:00', 'fr', 113636),
    ('2016-04-13 00:00:00', 'fr', 17452),
    ('2016-04-13 00:00:00', 'fr', 136544),
    ('2016-04-13 00:00:00', 'fr', 15917),
    ('2016-04-13 00:00:00', 'fr', 82787),
    ('2016-04-13 00:00:00', 'fr', 81620),
    ('2016-04-13 00:00:00', 'fr', 135298),
    ('2016-04-13 00:00:00', 'fr', 15643),
    ('2016-04-13 00:00:00', 'fr', 80981),
    ('2016-04-13 00:00:00', 'fr', 51827),
    ('2016-04-13 00:00:00', 'fr', 90554),
    ('2016-04-13 00:00:00', 'fr', 10277),
    ('2016-04-13 00:00:00', 'fr', 24432),
    ('2016-04-13 00:00:00', 'fr', 6651),
    ('2016-04-13 00:00:00', 'fr', 64106),
    ('2016-04-13 00:00:00', 'fr', 119080),
    ('2016-04-13 00:00:00', 'fr', 72659),
    ('2016-04-13 00:00:00', 'fr', 130004),
    ('2016-04-13 00:00:00', 'fr', 22320),
    ('2016-04-13 00:00:00', 'fr', 136966),
    ('2016-04-13 00:00:00', 'fr', 11317),
    ('2016-04-13 00:00:00', 'fr', 79031),
    ('2016-04-13 00:00:00', 'fr', 90800),
    ('2016-04-13 00:00:00', 'fr', 16149),
    ('2016-04-13 00:00:00', 'fr', 61463),
    ('2016-04-13 00:00:00', 'fr', 5383),
    ('2016-04-13 00:00:00', 'fr', 87674),
    ('2016-04-13 00:00:00', 'fr', 10223),
    ('2016-04-13 00:00:00', 'fr', 88100),
    ('2016-04-13 00:00:00', 'fr', 91691),
    ('2016-04-13 00:00:00', 'fr', 89423),
    ('2016-04-13 00:00:00', 'fr', 126),
    ('2016-04-13 00:00:00', 'fr', 86858),
    ('2016-04-13 00:00:00', 'fr', 1419),
    ('2016-04-13 00:00:00', 'fr', 89849),
    ('2016-04-13 00:00:00', 'fr', 15721),
    ('2016-04-13 00:00:00', 'fr', 86444),
    ('2016-04-13 00:00:00', 'fr', 130822),
    ('2016-04-13 00:00:00', 'fr', 73991),
    ('2016-04-13 00:00:00', 'fr', 113969),
    ('2016-04-13 00:00:00', 'fr', 16779),
    ('2016-04-13 00:00:00', 'fr', 71267),
    ('2016-04-13 00:00:00', 'fr', 61067),
    ('2016-04-13 00:00:00', 'fr', 89081),
    ('2016-04-13 00:00:00', 'fr', 24815),
    ('2016-04-13 00:00:00', 'fr', 91928),
    ('2016-04-13 00:00:00', 'fr', 13071),
    ('2016-04-13 00:00:00', 'fr', 1942),
    ('2016-04-13 00:00:00', 'fr', 44012),
    ('2016-04-13 00:00:00', 'fr', 52049),
    ('2016-04-13 00:00:00', 'fr', 6626),
    ('2016-04-13 00:00:00', 'fr', 7034),
    ('2016-04-13 00:00:00', 'fr', 20442),
    ('2016-04-13 00:00:00', 'fr', 75422),
    ('2016-04-13 00:00:00', 'fr', 16673),
    ('2016-04-13 00:00:00', 'fr', 17325),
    ('2016-04-13 00:00:00', 'fr', 7898),
    ('2016-04-13 00:00:00', 'fr', 85226),
    ('2016-04-13 00:00:00', 'fr', 136557),
    ('2016-04-13 00:00:00', 'fr', 134423),
    ('2016-04-13 00:00:00', 'fr', 68723),
    ('2016-04-13 00:00:00', 'en', 118331),
    ('2016-04-13 00:00:00', 'fr', 91298),
    ('2016-04-13 00:00:00', 'fr', 136046),
    ('2016-04-13 00:00:00', 'fr', 136891),
    ('2016-04-13 00:00:00', 'fr', 9169),
    ('2016-04-13 00:00:00', 'fr', 88946),
    ('2016-04-13 00:00:00', 'fr', 115919),
    ('2016-04-13 00:00:00', 'fr', 44492),
    ('2016-04-13 00:00:00', 'fr', 89783),
    ('2016-04-13 00:00:00', 'fr', 137482),
    ('2016-04-13 00:00:00', 'fr', 10072),
    ('2016-04-13 00:00:00', 'fr', 38636),
    ('2016-04-13 00:00:00', 'fr', 11227),
    ('2016-04-13 00:00:00', 'fr', 108310),
    ('2016-04-13 00:00:00', 'fr', 4700),
    ('2016-04-13 00:00:00', 'fr', 17976),
    ('2016-04-13 00:00:00', 'fr', 8580),
    ('2016-04-13 00:00:00', 'fr', 91316)
;

I tried to do it with python:

    edate = date.now()
    date_list = pd.date_range(sdate,edate-timedelta(days=1),freq='d')
    for date_range in date_list:
        c.execute(
            f"""
            INSERT INTO alive_subscribers_by_month 
            SELECT {date_range}, lang, count(distinct user_id)
            FROM ((SELECT user_id, lang
                   FROM sessions
                   WHERE session_time > NOW - INTERVAL 1 YEAR
                  ) UNION ALL
                  (SELECT user_id, lang
                   FROM unique_open_emails
                   WHERE date > {date_range} - INTERVAL 1 YEAR
                  )
                 ) u
            GROUP BY lang
            WHERE NOT EXISTS (SELECT * FROM alive_subscribers_by_month) ;""")

user_1144035 · Answer

Basically, you need to construct a list of dates that you care about and then use LEFT JOIN and aggregation.

MySQL 5.7 does not have have a convenient way to list the dates. If you have a calendar table, or you have some other source for the dates, then use that. Otherwise, list the dates in the query:

SELECT d.the_date, u.lang, count(distinct u.user_id)
FROM (SELECT date('2021-08-15') as the_date UNION ALL
      SELECT date('2021-07-15') as the_date 
     ) d LEFT JOIN
     ((SELECT user_id, lang, date(session_time) as dte
       FROM sessions
      ) UNION ALL
      (SELECT user_id, lang, date as dte
       FROM unique_open_emails
      )
     ) u
     ON u.dte > d.the_date - INTERVAL 1 YEAR AND
        u.dte <= d.the_date
GROUP BY d.the_date, u.lang;

How to query backwards on all the 15th of the month since the first date in the database?

1 Answers