Postgres 15

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

CREATE TABLE utm
(
	row_id int8 NOT NULL,
	utm_campaign text NULL,
	utm_source text NULL,
	utm_medium text NULL,
	utm_content text NULL,
	utm_term text NULL
);

INSERT INTO utm (row_id, utm_campaign, utm_source, utm_medium, utm_content, utm_term)
VALUES
(24611609, 'myTarget', 'Media', 'Social', NULL, NULL),
(28573041, 'shop_ smartfony', 'my_beeline', 'banner', NULL, NULL),
(28573041, 'Beeline_uppers_2022', NULL, NULL, NULL, NULL),
(24611609, 'campaign', 'source', 'medium', 'content', 'term');

CREATE TABLE report (
	id int8 NOT NULL,
	date_of_visit date NOT NULL,
	sessions numeric NULL,
    pageviews numeric NULL,
	bounces numeric NULL,
    utm_campaign text NULL,
	utm_source text NULL,
	utm_medium text NULL,
	utm_content text NULL,
	utm_term text NULL
);

INSERT INTO report (id, date_of_visit, sessions, pageviews, bounces, utm_campaign, utm_source, utm_medium, utm_content, utm_term)
VALUES
(24611609, '2022-08-01', 200, 150, 15, 'myTarget', 'Media', 'Social', 'premium', 'subcribe'),
(24611609, '2022-08-01', 25, 10, 1, 'myTarget', 'Media', 'Social', 'free', 'subcribe'),
(28573041, '2022-08-01', 900, 885, 34, 'shop_ smartfony', 'my_beeline', 'banner', NULL, NULL),
(28573041, '2022-08-01', 1000, 900, 10, 'Beeline_uppers_2022', NULL, NULL, NULL, NULL),
(21781121, '2022-08-01', 500, 50, 5, 'vodafone', 'google', NULL, NULL, NULL),
(21781121, '2022-08-01', 55, 50, 3, 'vodafone', 'google', 'youtube', NULL, NULL),
(24611609, '2022-08-01', 1, 1, 0, 'campaign', 'source', 'medium', 'content', 'term');

CREATE TABLE result (
	id int8 NOT NULL,
	date_of_visit date NOT NULL,
	sessions numeric NULL,
    pageviews numeric NULL,
	bounces numeric NULL,
    utm_campaign text NULL,
	utm_source text NULL,
	utm_medium text NULL,
	utm_content text NULL,
	utm_term text NULL
);

INSERT INTO result (id, date_of_visit, sessions, pageviews, bounces, utm_campaign, utm_source, utm_medium, utm_content, utm_term)
VALUES
(24611609, '2022-08-01', 225, 160, 16, 'myTarget', 'Media', 'Social', NULL, NULL),
(28573041, '2022-08-01', 900, 885, 34, 'shop_ smartfony', 'my_beeline', 'banner', NULL, NULL),
(28573041, '2022-08-01', 1000, 900, 10, 'Beeline_uppers_2022', NULL, NULL, NULL, NULL),
(24611609, '2022-08-01', 1, 1, 0, 'campaign', 'source', 'medium', 'content', 'term');
4 rows affected

7 rows affected

4 rows affected

select * from utm


row_id
utm_campaign
utm_source
utm_medium
utm_content
utm_term


24611609
myTarget
Media
Social
null
null

28573041
shop_ smartfony
my_beeline
banner
null
null

28573041
Beeline_uppers_2022
null
null
null
null

24611609
campaign
source
medium
content
term


select * from report


id
date_of_visit
sessions
pageviews
bounces
utm_campaign
utm_source
utm_medium
utm_content
utm_term


24611609
2022-08-01
200
150
15
myTarget
Media
Social
premium
subcribe

24611609
2022-08-01
25
10
1
myTarget
Media
Social
free
subcribe

28573041
2022-08-01
900
885
34
shop_ smartfony
my_beeline
banner
null
null

28573041
2022-08-01
1000
900
10
Beeline_uppers_2022
null
null
null
null

21781121
2022-08-01
500
50
5
vodafone
google
null
null
null

21781121
2022-08-01
55
50
3
vodafone
google
youtube
null
null

24611609
2022-08-01
1
1
0
campaign
source
medium
content
term


select * from result


id
date_of_visit
sessions
pageviews
bounces
utm_campaign
utm_source
utm_medium
utm_content
utm_term


24611609
2022-08-01
225
160
16
myTarget
Media
Social
null
null

28573041
2022-08-01
900
885
34
shop_ smartfony
my_beeline
banner
null
null

28573041
2022-08-01
1000
900
10
Beeline_uppers_2022
null
null
null
null

24611609
2022-08-01
1
1
0
campaign
source
medium
content
term


select      u.row_id
           ,r.date_of_visit
           
           ,sum(r.sessions)     as sessions
           ,sum(r.pageviews)    as pageviews
           ,sum(r.bounces)      as bounce
           
           ,u.utm_campaign
           ,u.utm_source  
           ,u.utm_medium  
           ,u.utm_content 
           ,u.utm_term    
         
from                utm     as u
            join    report  as r
            on       u.row_id = r.id
                and (u.utm_campaign = r.utm_campaign or u.utm_campaign is null)
                and (u.utm_source   = r.utm_source   or u.utm_source   is null)
                and (u.utm_medium   = r.utm_medium   or u.utm_medium   is null)
                and (u.utm_content  = r.utm_content  or u.utm_content  is null)
                and (u.utm_term     = r.utm_term     or u.utm_term     is null)
                                   
group by    u.row_id
           ,r.date_of_visit
           ,u.utm_campaign
           ,u.utm_source  
           ,u.utm_medium  
           ,u.utm_content 
           ,u.utm_term    


row_id
date_of_visit
sessions
pageviews
bounce
utm_campaign
utm_source
utm_medium
utm_content
utm_term


24611609
2022-08-01
1
1
0
campaign
source
medium
content
term

24611609
2022-08-01
225
160
16
myTarget
Media
Social
null
null

28573041
2022-08-01
1000
900
10
Beeline_uppers_2022
null
null
null
null

28573041
2022-08-01
900
885
34
shop_ smartfony
my_beeline
banner
null
null

row_id	utm_campaign	utm_source	utm_medium	utm_content	utm_term
24611609	myTarget	Media	Social	null	null
28573041	shop_ smartfony	my_beeline	banner	null	null
28573041	Beeline_uppers_2022	null	null	null	null
24611609	campaign	source	medium	content	term

id	date_of_visit	sessions	pageviews	bounces	utm_campaign	utm_source	utm_medium	utm_content	utm_term
24611609	2022-08-01	200	150	15	myTarget	Media	Social	premium	subcribe
24611609	2022-08-01	25	10	1	myTarget	Media	Social	free	subcribe
28573041	2022-08-01	900	885	34	shop_ smartfony	my_beeline	banner	null	null
28573041	2022-08-01	1000	900	10	Beeline_uppers_2022	null	null	null	null
21781121	2022-08-01	500	50	5	vodafone	google	null	null	null
21781121	2022-08-01	55	50	3	vodafone	google	youtube	null	null
24611609	2022-08-01	1	1	0	campaign	source	medium	content	term