SQL Server 2012

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

DECLARE @Trips TABLE
(
	Id INT,
	Client_Id INT,
	Driver_Id INT,
	City_Id INT,
	[Status] NVARCHAR(100),
	Request_at DATETIME
)

DECLARE @Users TABLE
(
	Users_Id INT, 
	Banned NVARCHAR(100),  
	[Role] NVARCHAR(100)
)

INSERT INTO @Trips (Id, Client_Id, Driver_Id, City_Id, [Status], Request_at) VALUES
(1,1,10,1,'completed','2013-10-01'),
(2,2,11,1,'cancelled_by_driver','2013-10-01'),
(3,3,12,6,'completed','2013-10-01'),
(4,4,13,6,'cancelled_by_client','2013-10-01'),
(5,1,10,1,'completed','2013-10-02'),
(6,2,11,6,'completed','2013-10-02'),
(7,3,12,6,'completed','2013-10-02'),
(8,2,12,12,'completed','2013-10-03'),
(9,3,10,12,'completed','2013-10-03'),
(1,4,13,12,'cancelled_by_driver','2013-10-03')

INSERT INTO @Users (Users_Id, Banned, [Role]) VALUES
(1,'No','client'),
(2,'Yes','client'),
(3,'No','client'),
(4,'No','client'),
(10,'No','driver'),
(11,'No','driver'),
(12,'No','driver'),
(13,'No','driver')

SELECT
	CONVERT(NVARCHAR(10), Request_at, 120)AS [Day],
	ROUND(
		SUM(CASE WHEN [Status]='cancelled_by_client' OR [Status]='cancelled_by_driver' THEN 1 ELSE 0 END) / 
		CAST(COUNT(Id) AS float), 2
	) AS [Cancellation Rate]
FROM @Trips AS A
LEFT JOIN @Users AS B ON B.Users_Id=A.Client_Id
WHERE Request_at >= '2013-10-01' AND Request_at <= '2013-10-03' AND Banned = 'No' 
GROUP BY Request_at

Day	Cancellation Rate
2013-10-01	0.33
2013-10-02	0
2013-10-03	0.5