MySql Common Table Expression with Coalesce

Previously I wrote here about MySql recursive Common Table Expressions CTE

However now I want to do a summation against available records with COALESCE functionality to find unpaid bookings and then join that against users and this is a simplified query of what I needed

Table assumptions

Given the following tables of Users->BookingTable->PaymentsTable related by foreign keys like so

create table BookingTable
 Id                  int unsigned auto_increment primary key,
 UserId              int unsigned,
 TotalOwed           decimal(10, 2),
 Arrival             datetime,
 constraint FK_Users_UserId foreign key (UserId) references Users (Id),

create table PaymentsTable
 Id                  int unsigned auto_increment primary key,
 BookingId           int unsigned,
 CollectType         int unsigned,
 CollectDate         datetime,
 CollectDaysBefore   int unsigned,
 AmountType          int unsigned,
 Amount              decimal(10, 2),
 Status              int unsigned,
 constraint FK_BookingTable_BookingId foreign key (BookingId) references BookingTable (Id),

create table Users
 Id                  int unsigned auto_increment primary key,
 Active              int unsigned,


The payment tables holds two states of a record a date which a payment is due the specific date in CollectDate or interval based date days from CollectDaysBefore that counts from the Booking->Arrival date.

Came up with the query to sum all outstanding payments and then match to the bookings in CTE which will be the unpaid bookings. Then join this against the active users to get the information to inform the affected Users

WITH unpaid_bookings as (
	select b.Id, b.UserId
	from <BookingTable> b
	where ... -- Filter clauses
	and (
		SELECT COALESCE(SUM(IF(sp.AmountType = 0, b.TotalOwed, sp.Amount)), 0) < b.TotalOwed
		from <PaymentsTable> sp
		where sp.Status = 1
		and sp.BookingId = b.Id
		and (
			(sp.CollectType = 1 and sp.CollectDate < b.Arrival)
			(sp.CollectType = 0 and DATEDIFF(@endDate, DATE_SUB(b.Arrival, INTERVAL sp.CollectDaysBefore DAY)) <= 0)
FROM <Users> u
	inner join unpaid_bookings upd
		on upd.UserId =
where u.Active = 1