Postgres 10

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

CREATE TABLE colours (
    date_local date,
    colour text,
    amount int
);

INSERT INTO colours VALUES
('2020-01-01',	'white', 	10),
('2020-01-01',	'white', 	10),
('2020-01-01',	'green', 	20),
('2020-01-01',	'white', 	10),
('2020-01-01',	'red', 	        25),
('2020-01-01',	'white', 	10),
('2020-01-02',	'pink', 	15),
('2020-01-02',	'pink', 	15),
('2020-01-02',	'pink', 	15),
('2020-01-02',	'pink', 	15),
('2020-01-02',	'white', 	10),
('2020-01-02',	'white', 	10),
('2020-01-02',	'white', 	10),
('2020-01-02',	'white', 	10),
('2020-01-02',	'white', 	10),
('2020-01-03',	'pink', 	15),
('2020-01-03',	'pink', 	15),
('2020-01-03',	'pink', 	15),
('2020-01-03',	'green', 	20),
('2020-01-03',	'green', 	20),
('2020-01-03',	'green', 	20);

------------------------------------------------------------------------------
21 rows affected

select * from colours


date_local
colour
amount


2020-01-01
white
10

2020-01-01
white
10

2020-01-01
green
20

2020-01-01
white
10

2020-01-01
red
25

2020-01-01
white
10

2020-01-02
pink
15

2020-01-02
pink
15

2020-01-02
pink
15

2020-01-02
pink
15

2020-01-02
white
10

2020-01-02
white
10

2020-01-02
white
10

2020-01-02
white
10

2020-01-02
white
10

2020-01-03
pink
15

2020-01-03
pink
15

2020-01-03
pink
15

2020-01-03
green
20

2020-01-03
green
20

2020-01-03
green
20


with a as( 
    select
        *
        ,sum(colours) over(partition by date_local) as total_colour
    from ( 
        select 
            date_local
            ,colour
            ,count(colour) as colours 
            ,sum(amount) as amount 
        from colours
        group by 1,2
    ) as fr_om 
) 

select
    a.*
    ,b.date_local as is_matched
    ,coalesce(b.date_local, a.date_local + interval '1' day) as date_local_2
    ,coalesce(b.colour, a.colour) as colour_2
    ,coalesce(b.colours, 0) as colour_2
    ,coalesce(b.amount, 0) as amount_2
    ,coalesce(b.colours - a.colours, a.colours) as colour_difference
    ,coalesce(b.amount - a.amount, a.amount) as amount_difference
    ,b.total_colour as this_is_not_working
from a 
full outer join a as b 
    on a.date_local = b.date_local - interval '1' day 
    and a.colour = b.colour
order by 1


date_local
colour
colours
amount
total_colour
is_matched
date_local_2
colour_2
colour_2
amount_2
colour_difference
amount_difference
this_is_not_working


2020-01-01
red
1
25
6
null
2020-01-02 00:00:00
red
0
0
1
25
null

2020-01-01
green
1
20
6
null
2020-01-02 00:00:00
green
0
0
1
20
null

2020-01-01
white
4
40
6
2020-01-02
2020-01-02 00:00:00
white
5
50
1
10
9

2020-01-02
pink
4
60
9
2020-01-03
2020-01-03 00:00:00
pink
3
45
-1
-15
6

2020-01-02
white
5
50
9
null
2020-01-03 00:00:00
white
0
0
5
50
null

2020-01-03
pink
3
45
6
null
2020-01-04 00:00:00
pink
0
0
3
45
null

2020-01-03
green
3
60
6
null
2020-01-04 00:00:00
green
0
0
3
60
null

null
null
null
null
null
2020-01-02
2020-01-02 00:00:00
pink
4
60
null
null
9

null
null
null
null
null
2020-01-01
2020-01-01 00:00:00
white
4
40
null
null
6

null
null
null
null
null
2020-01-03
2020-01-03 00:00:00
green
3
60
null
null
6

null
null
null
null
null
2020-01-01
2020-01-01 00:00:00
green
1
20
null
null
6

null
null
null
null
null
2020-01-01
2020-01-01 00:00:00
red
1
25
null
null
6


select 
	date_local,
	colour,
	no_colour,
	sum_amount,
	total_colour,
	is_matched,
	case when is_matched = 1
		then lead(date_local) over(partition by colour order by date_local)
	end date_local_2,
	case when is_matched = 1
		then lead(colour) over(partition by colour order by date_local) 
	end colour_2,
	case when is_matched = 1 
		then lead(no_colour) over(partition by colour order by date_local) 
	end no_colour_2,
	case when is_matched = 1 
		then lead(sum_amount) over(partition by colour order by date_local) 
	end sum_amount_2,
	case when is_matched = 1 
		then lead(total_colour) over(partition by colour order by date_local) 
	end total_colour_2	
from (
	select
		date_local,
		colour,
		count(*) no_colour,
		sum(amount) sum_amount,
		case when lead(date_local) over(partition by colour order by date_local) 
			= date_local + interval '1' day
			then 1
		end is_matched,
		sum(count(*)) over(partition by date_local) total_colour
	from colours
	group by date_local, colour
) t
order by date_local, colour


date_local
colour
no_colour
sum_amount
total_colour
is_matched
date_local_2
colour_2
no_colour_2
sum_amount_2
total_colour_2


2020-01-01
green
1
20
6
null
null
null
null
null
null

2020-01-01
red
1
25
6
null
null
null
null
null
null

2020-01-01
white
4
40
6
1
2020-01-02
white
5
50
9

2020-01-02
pink
4
60
9
1
2020-01-03
pink
3
45
6

2020-01-02
white
5
50
9
null
null
null
null
null
null

2020-01-03
green
3
60
6
null
null
null
null
null
null

2020-01-03
pink
3
45
6
null
null
null
null
null
null

date_local	colour	amount
2020-01-01	white	10
2020-01-01	white	10
2020-01-01	green	20
2020-01-01	white	10
2020-01-01	red	25
2020-01-01	white	10
2020-01-02	pink	15
2020-01-02	pink	15
2020-01-02	pink	15
2020-01-02	pink	15
2020-01-02	white	10
2020-01-02	white	10
2020-01-02	white	10
2020-01-02	white	10
2020-01-02	white	10
2020-01-03	pink	15
2020-01-03	pink	15
2020-01-03	pink	15
2020-01-03	green	20
2020-01-03	green	20
2020-01-03	green	20

date_local	colour	colours	amount	total_colour	is_matched	date_local_2	colour_2	colour_2	amount_2	colour_difference	amount_difference	this_is_not_working
2020-01-01	red	1	25	6	null	2020-01-02 00:00:00	red	0	0	1	25	null
2020-01-01	green	1	20	6	null	2020-01-02 00:00:00	green	0	0	1	20	null
2020-01-01	white	4	40	6	2020-01-02	2020-01-02 00:00:00	white	5	50	1	10	9
2020-01-02	pink	4	60	9	2020-01-03	2020-01-03 00:00:00	pink	3	45	-1	-15	6
2020-01-02	white	5	50	9	null	2020-01-03 00:00:00	white	0	0	5	50	null
2020-01-03	pink	3	45	6	null	2020-01-04 00:00:00	pink	0	0	3	45	null
2020-01-03	green	3	60	6	null	2020-01-04 00:00:00	green	0	0	3	60	null
null	null	null	null	null	2020-01-02	2020-01-02 00:00:00	pink	4	60	null	null	9
null	null	null	null	null	2020-01-01	2020-01-01 00:00:00	white	4	40	null	null	6
null	null	null	null	null	2020-01-03	2020-01-03 00:00:00	green	3	60	null	null	6
null	null	null	null	null	2020-01-01	2020-01-01 00:00:00	green	1	20	null	null	6
null	null	null	null	null	2020-01-01	2020-01-01 00:00:00	red	1	25	null	null	6