MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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)
;

SELECT * FROM sessions;

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)
;

SELECT * FROM unique_open_emails;


App Type
Lang
Session ID
session_time
user_id


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

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

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




date
lang
user_id


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


CREATE TABLE IF NOT EXISTS alive_subscribers_by_month (`date` DATE,
                                                lang CHAR(2),
                                                count INTEGER);
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


lang
count(distinct user_id)


en
1

es
3

fr
3

App Type	Lang	Session ID	session_time	user_id
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
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
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

date	lang	user_id
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