MySQL 8.0

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

CREATE TABLE Table_1 (ID VARCHAR(4), `Date` DATETIME, uid CHAR(3));
INSERT INTO Table_1 VALUES
('I-1'  ,   '2020-01-01 10:12:15' ,    'K-1'),
('I-2'  ,   '2020-01-02 10:12:15' ,    'K-1'),
('I-3'  ,   '2020-02-01 10:12:15' ,    'K-2'),
('I-4'  ,   '2020-02-02 10:12:15' ,    'K-3'),
('I-5'  ,   '2020-02-04 10:12:15' ,    'K-4'),
('I-6'  ,   '2019-11-01 10:12:15' ,    'K-4'),
('I-7'  ,   '2019-11-01 10:12:15' ,    'K-3'),
('I-8'  ,   '2018-12-13 10:12:15' ,    'K-5'),
('I-9'  ,   '2019-05-17 10:12:15' ,    'K-4'),
('I-19' ,   '2020-03-11 10:12:15' ,    'K-7');
SELECT * FROM Table_1;


ID
Date
uid


I-1
2020-01-01 10:12:15
K-1

I-2
2020-01-02 10:12:15
K-1

I-3
2020-02-01 10:12:15
K-2

I-4
2020-02-02 10:12:15
K-3

I-5
2020-02-04 10:12:15
K-4

I-6
2019-11-01 10:12:15
K-4

I-7
2019-11-01 10:12:15
K-3

I-8
2018-12-13 10:12:15
K-5

I-9
2019-05-17 10:12:15
K-4

I-19
2020-03-11 10:12:15
K-7


CREATE TABLE Table_2 (ID VARCHAR(4), city VARCHAR(16), code SMALLINT);
INSERT INTO Table_2 VALUES
('I-1'  ,   'New York'   ,    123    ),
('I-2'  ,   'Washington' ,    122    ),
('I-3'  ,   'Tokyo'      ,    123    ),
('I-4'  ,   'London'     ,    144    ),
('I-5'  ,   'Dubai'      ,    101    ),
('I-6'  ,   'Dubai'      ,    101    ),
('I-7'  ,   'London'     ,    144    ),
('I-8'  ,   'Tokyo'      ,    143    ),
('I-9'  ,   'Dubai'      ,    101    ),
('I-19' ,   'Dubai'      ,    150    );
SELECT * FROM Table_2;


ID
city
code


I-1
New York
123

I-2
Washington
122

I-3
Tokyo
123

I-4
London
144

I-5
Dubai
101

I-6
Dubai
101

I-7
London
144

I-8
Tokyo
143

I-9
Dubai
101

I-19
Dubai
150


SELECT t11.ID, 
       t11.`Date`, 
       t21.city, 
       t21.code, 
       t11.uid, 
       CASE WHEN SUM((t21.city = t22.city) * (t21.code = t22.code)) - 1
            THEN 'Yes'
            ELSE 'No' END `Match`,
       SUM((t21.city = t22.city) * (t21.code = t22.code) * (t11.uid = t12.uid)) - 1 same_uid,
       SUM((t21.city = t22.city) * (t21.code = t22.code) * (t11.uid != t12.uid)) different_uid,
       SUM(t11.uid = t12.uid) uid_count
FROM /* cities t1 */
     (Table_1 t11 NATURAL JOIN Table_2 t21)
CROSS JOIN /* cities t2 */
           (Table_1 t12 NATURAL JOIN Table_2 t22)
WHERE t11.`Date` >= '2020-01-01' AND t11.`Date` < '2020-03-01'
GROUP BY t11.ID, t11.`Date`, t21.city, t21.code, t11.uid
ORDER BY t11.ID


ID
Date
city
code
uid
Match
same_uid
different_uid
uid_count


I-1
2020-01-01 10:12:15
New York
123
K-1
No
0
0
2

I-2
2020-01-02 10:12:15
Washington
122
K-1
No
0
0
2

I-3
2020-02-01 10:12:15
Tokyo
123
K-2
No
0
0
1

I-4
2020-02-02 10:12:15
London
144
K-3
Yes
1
0
2

I-5
2020-02-04 10:12:15
Dubai
101
K-4
Yes
2
0
3

ID	Date	uid
I-1	2020-01-01 10:12:15	K-1
I-2	2020-01-02 10:12:15	K-1
I-3	2020-02-01 10:12:15	K-2
I-4	2020-02-02 10:12:15	K-3
I-5	2020-02-04 10:12:15	K-4
I-6	2019-11-01 10:12:15	K-4
I-7	2019-11-01 10:12:15	K-3
I-8	2018-12-13 10:12:15	K-5
I-9	2019-05-17 10:12:15	K-4
I-19	2020-03-11 10:12:15	K-7

ID	city	code
I-1	New York	123
I-2	Washington	122
I-3	Tokyo	123
I-4	London	144
I-5	Dubai	101
I-6	Dubai	101
I-7	London	144
I-8	Tokyo	143
I-9	Dubai	101
I-19	Dubai	150