Count bookings and cancellations per member (select in subquery)
I hope that query is self-explaining enough.

SELECT u.name AS `name`
     , IFNULL( z.bookings, 0 ) AS bookings
     , IFNULL( q.cancellations, 0 ) AS cancellations 
  FROM user AS u 

  LEFT JOIN 
     (
       SELECT uv.user_id AS user_id
            , COUNT(*) AS `bookings`
         FROM user_visit AS uv
        WHERE uv.type = 'BOOKED'
        GROUP BY uv.user_id 
     )
     AS z 
     ON z.user_id = u.id

  LEFT JOIN 
     (
       SELECT uv.user_id AS user_id
            , COUNT(*) AS `cancellations`
         FROM user_visit AS uv
        WHERE uv.type = 'CANCELED'
        GROUP BY uv.user_id
     ) 
     AS q 
     ON q.user_id = u.id
;