MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table users (user_id int, name nvarchar(50), account_bal float);
insert into users (user_id, name, account_bal) values 
(1, 'Wells', -5),
(2, 'James', 100),
(3, 'Joy', 10),
(4, 'Mumbi', -300);

create table deposits (id int, user_id int, amount float, date date);
insert into deposits values
(1, 1, 5,  '2021-04-26'),
(2, 3, 10, '2021-04-26'),
(3, 3, 5,  '2021-04-25'),
(4, 4, 5,  '2021-04-25'),
(5, 3, 73, '2021-05-06'); /* added last day oper*/


create table transactions (id int, user_id int, amount_tendered float, date date);
insert into transactions values 
(1, 1, 5,  '2021-04-27'),
(2, 2, 10, '2021-04-26'),
(3, 3, 15, '2021-04-26'),
(4, 3, 17, '2021-04-27'), /* added */
(5, 4, 50, '2021-04-25');

create table councils (council_id int, name nvarchar(50));
insert into councils values 
(1, 'a'), 
(2, 'b'), 
(3, 'c');

create table markets (market_id int, name nvarchar(50), council_id int);
insert into markets values 
(1, 'x', 3),
(2, 'y', 1),
(3, 'z', 2);

create table market_user_link (id int, market_id int, user_id int);
insert into market_user_link values 
(1, 1, 3), 
(2, 2, 2), 
(3, 3, 1),
(4, 3 ,4);

Records: 4  Duplicates: 0  Warnings: 0



Records: 5  Duplicates: 0  Warnings: 0



Records: 5  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0


select * from users;
select * from deposits order by date;
select * from transactions order by date;
select * from councils;
select * from markets;
select * from market_user_link;


user_id
name
account_bal


1
Wells
-5

2
James
100

3
Joy
10

4
Mumbi
-300




id
user_id
amount
date


3
3
5
2021-04-25

4
4
5
2021-04-25

1
1
5
2021-04-26

2
3
10
2021-04-26

5
3
73
2021-05-06




id
user_id
amount_tendered
date


5
4
50
2021-04-25

2
2
10
2021-04-26

3
3
15
2021-04-26

1
1
5
2021-04-27

4
3
17
2021-04-27




council_id
name


1
a

2
b

3
c




market_id
name
council_id


1
x
3

2
y
1

3
z
2




id
market_id
user_id


1
1
3

2
2
2

3
3
1

4
3
4


/*  Users table is holding starting balance dated '2021-04-01' */

  with uc as (
   select distinct m.council_id, mul.user_id
   from markets m 
   join market_user_link mul on m.market_id = mul.market_id
  ),
  user_running_total as (
    select user_id, date, 
     coalesce(lead(date) over(partition by user_id order by date) - interval 1 day, date) nxt,
     sum(sum(s)) over(partition by user_id order by date) rt
    from (
       select user_id, date, -amount_tendered s
       from transactions 
       union all
       select user_id, date, amount
       from deposits
       union all
       select user_id, se.d, se.s
       from users
       cross join lateral (
            select date(NOW() + interval 1 day) d, 0 s
            union all
            select '2021-04-01' d, account_bal
        ) se
    ) t
    group by user_id, date
  ),
  user_overdraft as (
     select user_id, date, nxt, least(rt, 0) ovd
     from user_running_total 
     where date <= date(NOW()) 
  ),
  dates as (
     select date
     from user_overdraft
     union 
     select nxt
     from user_overdraft
  ),
  council__overdraft as ( 
     select uc.council_id, d.date, sum(uo.ovd) total_overdraft, lag(sum(uo.ovd), 1, sum(uo.ovd) - 1) over(partition by uc.council_id order by d.date) prev_ovd
     from uc
     cross join dates d
     join user_overdraft uo on uc.user_id = uo.user_id and d.date between uo.date and uo.nxt
     group by uc.council_id, d.date
  )
  select council_id, date, total_overdraft
  from council__overdraft
  where total_overdraft <> prev_ovd
  order by date, council_id  


council_id
date
total_overdraft


1
2021-04-01
0

2
2021-04-01
-305

3
2021-04-01
0

2
2021-04-25
-350

2
2021-04-26
-345

2
2021-04-27
-350

3
2021-04-27
-7

3
2021-05-06
0


  /*  Users table is holding ending balance dated now() */
  with uc as (
     select distinct m.council_id, mul.user_id
     from markets m 
     join market_user_link mul on m.market_id = mul.market_id
  ),
  user_running_total as (
    select user_id, date, 
       coalesce(lead(date) over(partition by user_id order by date) - interval 1 day, date) nxt,
       coalesce(sum(sum(s)) over(partition by user_id order by date desc
            rows between unbounded preceding and 1 preceding), sum(s))  rt
    from (
       select user_id, date, amount_tendered s
       from transactions 
       union all
       select user_id, date, -amount
       from deposits
       union all
       select user_id, se.d, se.s
       from users
       cross join lateral (
            select date(NOW() + interval 1 day) d, account_bal s
            union all
            select '2021-04-01' d, 0
        ) se
    ) t
    where DATE(date) between date '2021-04-01' and date(NOW() + interval 1 day)
    group by user_id, date
  ),
  user_overdraft as (
     select user_id, date, nxt, least(rt, 0) ovd
     from user_running_total 
     where date <= date(NOW()) 
  ), 
  dates as (
     select date
     from user_overdraft
     union 
     select nxt
     from user_overdraft
  ),
  council__overdraft as ( 
     select uc.council_id, d.date, sum(uo.ovd) total_overdraft, lag(sum(uo.ovd), 1, sum(uo.ovd) - 1) over(partition by uc.council_id order by d.date) prev_ovd
     from uc
     cross join dates d
     join user_overdraft uo on uc.user_id = uo.user_id and d.date between uo.date and uo.nxt
     group by uc.council_id, d.date
  )
  select council_id, date, total_overdraft
  from council__overdraft
  where total_overdraft <> prev_ovd
  order by date, council_id  


council_id
date
total_overdraft


1
2021-04-01
0

2
2021-04-01
-260

3
2021-04-01
-46

2
2021-04-25
-305

3
2021-04-25
-41

2
2021-04-26
-300

3
2021-04-26
-46

2
2021-04-27
-305

3
2021-04-27
-63

3
2021-05-06
0
id	user_id	amount	date
3	3	5	2021-04-25
4	4	5	2021-04-25
1	1	5	2021-04-26
2	3	10	2021-04-26
5	3	73	2021-05-06
council_id	date	total_overdraft
1	2021-04-01	0
2	2021-04-01	-305
3	2021-04-01	0
2	2021-04-25	-350
2	2021-04-26	-345
2	2021-04-27	-350
3	2021-04-27	-7
3	2021-05-06	0
council_id	date	total_overdraft
1	2021-04-01	0
2	2021-04-01	-260
3	2021-04-01	-46
2	2021-04-25	-305
3	2021-04-25	-41
2	2021-04-26	-300
3	2021-04-26	-46
2	2021-04-27	-305
3	2021-04-27	-63
3	2021-05-06	0